我有一个这样的输出:
Volume c1 c2
--------------------------------------------------
0 1000 0
1-20 100 10
20+ 50 40
我通过以下方式获得此输出:
SELECT
case when volume=0 then '0' when volume <21 then '1-20' when volume>=21 then '21+' as Volume,
sum(ab) as c1,
sum(xy) as c2
FROM
table t
GROUP BY
case when volume=0 then '0' when volume <21 then '1-20' when volume>=21 then '21+'
但是,我需要像下面一样获取输出。我正在尝试从 c1 列的总和中减去 c2 列的总和,结果必须放在 c2 列下的第一行,就像这样:
Volume c1 c2
--------------------------------------------------
0 1000 **1100**
1-20 100 10
20+ 50 40
我该怎么做?
提前谢谢。
我会简单地做:
SELECT (case when volume = 0 then '0' when volume < 21 then '1-20' when volume>=21 then '21+' end) as Volume,
sum(ab) as c1,
(case when volume = 0
then sum(sum(ab)) over () - sum(sum(xy))
else sum(xy)
end) as c2
FROM table t
GROUP BY (case when volume=0 then '0' when volume <21 then '1-20' when volume>=21 then '21+' end);
当您可以简单地使用窗口函数时,不需要子查询和 CTE。
WITH x
AS
(
SELECT
case when volume=0 then '0' when volume <21 then '1-20' when volume>=21 then '21+' as Volume,
sum(ab) as c1,
sum(xy) as c2
FROM
table t
GROUP BY
CASE when volume=0 then '0' when volume <21 then '1-20' when volume>=21 then '21+'
)
SELECT x.Volume, x.c1, (CASE WHEN x.Volume = 0
THEN (SELECT SUM(x2.c1 - x2.c2)
FROM x AS x2)
ELSE x.c2 END)
FROM x
您可以使用下面的查询来获取所需的输出 使用 CTE 表表达式可以实现。
与 Cte as( 选择 当音量=0时,当音量<21时为"0",当音量>=21时为"1-20",然后"21+"作为音量, 总和(AB( 作为 C1, 总和(XY( 作为 C2, sum(ab(-sum(xy( as c 从 表 T 分组依据 当音量=0 时的情况,然后当音量 <21 时为"0",然后当音量>=21 时为"1-20",然后"21+"结束 )
选择卷,C1,当卷='0'时的情况,然后(从CTE中选择sum(c3((否则C2结束C2从CTE结束C2