A sample for you:
DECLARE @Products TABLE (ProductID INT, ProductName VARCHAR(200), Price int)
INSERT INTO @Products(ProductID, ProductName,Price)
VALUES ( 1, 'A', 1000),(2, 'B',500),(3, 'C', 200),(4, 'E', 300)
;with mycte1 as (
select ProductID, ProductName, row_number() Over(Order by ProductID) rn,Price
from @Products)
, mycte2 AS
(
SELECT ProductID, ProductName, rn, Price
FROM mycte1
WHERE rn = 1
UNION ALL
SELECT n.ProductID, n.ProductName, n.rn, (m.Price - n.Price) as price
FROM mycte2 m INNER JOIN mycte1 n
ON n.rn = m.rn + 1
)
SELECT ProductID, ProductName,Price FROM mycte2
ORDER BY ProductID
--OPTION (MAXRECURSION 0)
;
No comments:
Post a Comment