Common table express

现有一张采购订单表p，写出SQL语句以查询每次采购价格Price发生变动的日期。

ID	Price	Date
1	3.5	2019-01-01
2	3.5	2019-01-04
3	3.4	2019-01-05
4	3.2	2019-01-05
5	3.2	2019-01-09
6	3.2	2019-01-11
7	3.2	2019-01-11
8	3.4	2019-01-12
9	3.4	2019-01-14
10	3.2	2019-01-15

 

WITH cte AS ( 
　　SELECT ItemName,Price,Date FROM (SELECT *,ROW_NUMBER() OVER(PARTITION BY ItemName ORDER BY Date) AS RN FROM p) t WHERE RN = 1 
　　UNION ALL 
　　SELECT ItemName,UPC,Date FROM ( 
　　　　SELECT p.*,ROW_NUMBER() OVER(PARTITION BY p.ItemName ORDER BY p.Date) AS RN1 FROM p INNER JOIN ( 
　　　　　　SELECT * FROM (SELECT *,ROW_NUMBER() OVER(PARTITION BY ItemName ORDER BY Date DESC) AS RN FROM cte) t WHERE RN = 1 
　　　　) t1 ON p.ItemName = t1.ItemName AND p.Price != t1.Price AND p.Date > t1.Date 
    ) t2 WHERE RN1 = 1 
)
SELECT * FROM cte