gpt4 book ai didi

sql - 如何对具有重置条件的列进行 SUM()?

转载 作者:行者123 更新时间:2023-11-29 12:59:16 24 4
gpt4 key购买 nike

我使用的是 Postgresql 9.2,我需要从 buttom 向上求和初始值为 100 的数量,但是如果我遇到名称为 X 的行,我需要从该行中的 qty 值重新开始求和。

例如:

itemorder   name    qty
1 A -20
2 A2 350
3 X 40
4 A 50
5 A -10
6 A2 10

应该生成:

itemorder   name    qty       modifyed_sum
1 A -20 20 / 40 + (-20)
2 A2 350 40 / not A
3 X 40 40 / encounter X, restart sum with X qty.
4 A 50 140 / 90 + 50
5 A -10 90 / 100 + (-10)
6 A2 10 100 / not A

我使用这个查询来求和:

SELECT 100 + Sum(CASE WHEN name = 'a' THEN qty ELSE 0 END)OVER(ORDER BY itemorder DESC) as modifyed_sum,
qty,
name,
itemorder
FROM Table_Name
ORDER BY itemorder ASC

如何修改它以处理 X 大小写? (可以有多个X)

最佳答案

你需要一个 RECURSIVE 查询

SQL FIDDLE DEMO

WITH RECURSIVE t("itemorder", "name", "qty", "modifyed_sum", "level") AS (
SELECT "itemorder",
"name",
"qty",
100 + CASE
WHEN "name" = 'A' THEN "qty"
ELSE 0
END as "modifyed_sum",
"itemorder" as "level"
FROM myTable
WHERE "itemorder" = (SELECT max("itemorder") FROM myTable)
UNION ALL
SELECT myTable."itemorder",
myTable."name",
myTable."qty",
CASE
WHEN myTable."name" = 'A' THEN t."modifyed_sum" + myTable."qty"
WHEN myTable."name" = 'X' THEN myTable."qty"
ELSE t."modifyed_sum"
END as "modifyed_sum",
myTable."itemorder" as "level"
FROM myTable
JOIN t
ON myTable."itemorder" + 1 = t."itemorder"
AND myTable."itemorder" = t."level" - 1
WHERE myTable."itemorder" > 0
)
SELECT *
FROM t
ORDER BY "itemorder"

输出

| itemorder | name | qty | modifyed_sum | level |
|-----------|------|-----|--------------|-------|
| 1 | A | -20 | 20 | 1 |
| 2 | A2 | 350 | 40 | 2 |
| 3 | X | 40 | 40 | 3 |
| 4 | A | 50 | 140 | 4 |
| 5 | A | -10 | 90 | 5 |
| 6 | A2 | 10 | 100 | 6 |

关于sql - 如何对具有重置条件的列进行 SUM()?,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/34702140/

24 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com