我有一个产品表,我想用价格列做一个之字形减法
我已经尝试过以下查询,但对我不起作用
SELECT price
,price - (SELECT COALESCE(lag(price) OVER (ORDER BY ProductID),price) AS PayableInQuarter
FROM @Products WHERE ProductID=1) from @Products
创建表的代码
DECLARE @Products TABLE (ProductID INT, ProductName VARCHAR(20), Price int)
INSERT INTO @Products VALUES (1, 'A', 1000)
INSERT INTO @Products VALUES (2, 'B', 5000)
INSERT INTO @Products VALUES (3, 'C', 9000)
INSERT INTO @Products VALUES (4, 'E', 12000)
SELECT * FROM @Products
预期产量如下
| ProductId | ProductName | Price | Difference |
|-----------|-------------|-------|------------|
| 1 | A | 1000 | 1000 |
| 2 | B | 5000 | 4000 |
| 3 | C | 9000 | 5000 |
| 4 | E | 12000 | 7000 |
最佳答案
我们可以尝试在ROW_NUMBER
的帮助下实现滚动差异:
WITH cte AS (
SELECT *,
ROW_NUMBER() OVER (ORDER BY ProductID) rn,
COUNT(*) OVER () cnt
FROM @Products
)
SELECT
ProductID,
ProductName,
Price,
(CASE WHEN rn % 2 = 0 THEN 1.0 ELSE -1.0 END) *
(SELECT SUM(CASE WHEN (t2.cnt - t2.rn) % 2 = 0
THEN t2.Price ELSE -1.0*t2.Price END)
FROM cte t2
WHERE t2.ProductID <= t1.ProductID) AS Difference
FROM cte t1
ORDER BY
ProductID;
Demo
关于sql - 如何对结果和当前值进行对角线减法,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/56679521/