一行中最小和最大行的SQL值 [英] SQL values for min and max rows in one row

问题描述

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

表结构很简单：

  ProductID，StartDate，Price 
  

所需结果是

  ProductId，StartDate，InitialPrice，LatestDate，LatestPrice 
  
 
 
解决方案
 
  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演示 
 
 
 
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.

Table structure is simple:
ProductID, StartDate, Price
Desired result is 
ProductId, StartDate, InitialPrice, LatestDate, LatestPrice

解决方案
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


                        
这篇关于一行中最小和最大行的SQL值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！