代码之家 › 专栏 › 技术社区 › Byron Sommardahl

用于获取具有最前1个最近价格更改的产品的SQL查询

coalesce tsql sql-server-2005 sql-server sql

Byron Sommardahl · 技术社区 · 14 年前

我正在使用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);

价格变动表:

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

有什么更好的方法可以做到这一点?

2 回复 | 直到 14 年前

garik 14 年前

它的执行速度比[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

OMG Ponies 14 年前

用途:

   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