題目﹕
reate table tst (c1 integer, c2 integer, c3 integer);
insert into tst values (1, null, 150);
insert into tst values (1, 1, 80);
insert into tst values (1, 2, 90);
insert into tst values (1, 1, 130);
insert into tst valuse (1, 3, 120);
SQL> select * from tst;
C1 C2 C3
---------- ---------- ----------
1 150
1 1 80
1 2 90
1 1 130
1 3 120
要求结果为
C1 C2 C3_SUM
---------- ---------- ----------
1 192
1 1 210
1 2 90
1 3 120
以C1/C2分组对C3求和，如果C2为空,C3的和应该包括同组中其他Ｃ２非空行的10%.
如果单纯分组
则为
C1 C2 C3_SUM
1 NULL 150
特殊要求为让其包含 10% * (80+90+130+120) = 42
最后结果为 150 + 42 = 192
答案﹕
-- 1. 需求直译
select c1, c2,
case
when c2 is not null then
sum(c3)
else
sum(c3)+(select sum(c3) from tst where c1=t.c1 and c2 is not null)*0.1
end as c3_sum
from tst t
group by c1, c2-- 2. join
select a.c1, a.c2,
case
when a.c2 is null then
a.c3_sum + c3_sum_01
else
a.c3_sum
end as c3_sum
from (select c1, c2, sum(c3) as c3_sum
from tst
group by c1, c2) a,
(select c1, sum(c3)*0.1 as c3_sum_01
from tst
where c2 is not null
group by c1) b
where a.c1=b.c1
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/701414/viewspace-905266/ ，如需转载，请注明出处，否则将追究法律责任。
上一篇：
三十岁之前不必在乎的二十九件事
下一篇：
SQL中Case语句用法讨论
请登录后发表评论
登录
全部评论
