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

Question

我有一张价格变动表，我需要获取初始价格和最新价格。换句话说，我想在每种产品的一行中显示min（StartDate）和max（StartDate）的价格值。

表的结构很简单：

ProductID, StartDate, Price

所需的结果是

ProductId, StartDate, InitialPrice, LatestDate, LatestPrice

Answer 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演示