用于获取最近价格变化最多的前 1 个产品的 SQL 查询

Question

我使用的是 SQL Server 2005。

假设我有一个产品表和另一个价格表，以便我可以跟踪价格随时间的变化。我需要一个查询来获取不同的产品（简单部分）加上每个产品的最新价格及其更改日期。

产品表：

CREATE TABLE [dbo].[Products](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Name] [varchar](50) NOT NULL,
    [Price] [money] NOT NULL,
 CONSTRAINT [PK_Products] PRIMARY KEY CLUSTERED 
( [ID] ASC )
) ON [PRIMARY]
GO

INSERT INTO Products (Name, Price) VALUES ('Hat', 10);
INSERT INTO Products (Name, Price) VALUES ('Shirt', 15);
INSERT INTO Products (Name, Price) VALUES ('Pants', 20);
INSERT INTO Products (Name, Price) VALUES ('Coat', 25);
INSERT INTO Products (Name, Price) VALUES ('Shoes', 30);

PriceChanges 表：

CREATE TABLE [dbo].[PriceChanges](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [ProductId] [int] NOT NULL,
    [Price] [money] NOT NULL,
    [PriceChanged] [datetime] NOT NULL,
 CONSTRAINT [PK_PriceChanges] PRIMARY KEY CLUSTERED 
( [ID] ASC )
) ON [PRIMARY]
GO

INSERT INTO PriceChanges (ProductId, Price, PriceChanged) VALUES (1, 9.65, '1/1/2010');
INSERT INTO PriceChanges (ProductId, Price, PriceChanged) VALUES (1, 10.10, '1/2/2010');
INSERT INTO PriceChanges (ProductId, Price, PriceChanged) VALUES (1, 11.50, '1/3/2010');
INSERT INTO PriceChanges (ProductId, Price, PriceChanged) VALUES (2, 15.50, '1/4/2010');
INSERT INTO PriceChanges (ProductId, Price, PriceChanged) VALUES (2, 15.65, '1/5/2010');
INSERT INTO PriceChanges (ProductId, Price, PriceChanged) VALUES (3, 19.95, '1/6/2010');
INSERT INTO PriceChanges (ProductId, Price, PriceChanged) VALUES (4, 24.95, '1/7/2010');

这是一个返回太多的查询：

SELECT     
    p.ID ProductId, 
    p.Name, 
    COALESCE(c.Price, p.Price) Price, 
    c.PriceChanged
FROM dbo.Products AS p LEFT JOIN 
        dbo.PriceChanges AS c ON c.ProductId = p.ID

返回：

1   Hat     9.65    2010-01-01 00:00:00.000
1   Hat     10.10   2010-01-02 00:00:00.000
1   Hat     11.50   2010-01-03 00:00:00.000
2   Shirt   15.50   2010-01-04 00:00:00.000
2   Shirt   15.65   2010-01-05 00:00:00.000
3   Pants   19.95   2010-01-06 00:00:00.000
4   Coat    24.95   2010-01-07 00:00:00.000
5   Shoes   30.00   NULL

我需要它返回的是：

1   Hat     11.50   2010-01-03 00:00:00.000
2   Shirt   15.65   2010-01-05 00:00:00.000
3   Pants   19.95   2010-01-06 00:00:00.000
4   Coat    24.95   2010-01-07 00:00:00.000
5   Shoes   30.00   NULL

这个查询有效，但它有两个嵌套选择，这会让世界各地的 DBA 哭泣：

SELECT     
    p.ID ProductId, 
    p.Name, 
    COALESCE((SELECT TOP 1 Price FROM dbo.PriceChanges WHERE ProductId = p.ID ORDER BY PriceChanged DESC), p.Price) Price, 
    (SELECT TOP 1 PriceChanged FROM dbo.PriceChanges WHERE ProductId = p.ID ORDER BY PriceChanged DESC) PriceChanged
FROM dbo.Products AS p 

有什么更好的方法吗？这？

Answer 1

它的执行速度比 [OMG Ponies] 快 2 倍，比你的快 20 倍（根据此数据）

select
    p.ID ProductId
    ,p.Name
    ,COALESCE(b.Price, p.Price) Price
    ,b.PriceChanged
from dbo.Products AS p 
LEFT JOIN 
(
    select
      a.ProductId
      ,a.PriceChanged
      ,pc2.Price     
    from
    (
        select 
           pc1.ProductId
           ,MAX(pc1.PriceChanged) as PriceChanged
        from dbo.PriceChanges pc1 
        group by pc1.ProductId 
    ) a         
    inner join dbo.PriceChanges pc2 
    on (a.PriceChanged = pc2.PriceChanged and a.ProductId = pc2.ProductId)
) b
ON b.ProductId = p.ID  

Answer 2

使用：

   SELECT p.id AS productid,
          p.name,
          COALESCE(x.price, p.price) AS price,
          x.pricechanged
     FROM dbo.PRODUCTS p
LEFT JOIN (SELECT pc.productid,
                  pc.price,
                  pc.pricechanged,
                  ROW_NUMBER() OVER(PARTITION BY pc.productid 
                                        ORDER BY pc.pricechanged DESC) AS rownum
             FROM dbo.PRICECHANGES pc) x ON x.productid = p.id
                                        AND x.rownum = 1