一个行的最小和最大行的SQL值

I have a table with price changes and I need to get initial price and latest price. In other words I want to display price values for min(StartDate) and max(StartDate) in one row for each product.

我有一个价格变动的表格，我需要得到初始价格和最新价格。换句话说，我想为每个产品在一行中显示min(StartDate)和max(StartDate)的价格值。

Table structure is simple:

表结构很简单:

ProductID, StartDate, Price

Desired result is

预期的结果是

ProductId, StartDate, InitialPrice, LatestDate, LatestPrice

2 个解决方案

#1

WITH latestPrice AS
(
   SELECT ProductID, StartDate, Price,
          ROW_NUMBER() OVER (PArtition BY ProductID ORDER BY StartDate DESC) rn
   FROM TableName
)
, initalPrice AS
(
  SELECT ProductID, StartDate, Price,
         ROW_NUMBER() OVER (PArtition BY ProductID ORDER BY StartDate ASC) rn
  FROM TableName  
)
SELECT  a.ProductID,
        b.StartDate, 
        b.Price InitalPrice, 
        c.StartDate LatestDate, 
        c.Price LatestPrice
FROM    (SELECT DISTINCT ProductID FROM tableName) a
        INNER JOIN initalPrice b
          ON a.ProductID = b.ProductID AND b.rn = 1
        INNER JOIN latestprice c
          ON a.ProductID = c.ProductID AND c.rn = 1

• SQLFiddle Demo
• SQLFiddle演示

#2

(SELECT x.ProductId, x.MinStartDate, x.MinPrice, y.MaxStartDate, y.MaxPrice FROM (SELECT a.ProductId, a.MinStartDate, b.Price AS MinPrice FROM (SELECT ProductId, MIN(StartDate) AS MinStartDate FROM Products GROUP BY ProductId) a INNER JOIN Products b ON a.ProductId = b.ProductId AND a.MinStartDate = b.StartDate) x INNER JOIN (SELECT a.ProductId, a.MaxStartDate, b.Price AS MaxPrice FROM (SELECT ProductId, MAX(StartDate) AS MaxStartDate FROM Products GROUP BY ProductId) a INNER JOIN Products b ON a.ProductId = b.ProductId AND a.MaxStartDate = b.StartDate) y ON x.ProductId = y.ProductId

DISCLAIMER: This is from memory so I apologize if this is not entirely accurate. And it assumes that the StartDate is a datetime or is not repeated for each productId.

免责声明:这是来自记忆，所以我道歉，如果这不是完全正确。它假定StartDate是一个datetime，或者不是每个productId重复使用的。

Hope this helps.

希望这个有帮助。