我发现很奇怪,当我基于 CASE WHEN 为组执行计数(*(时,简单的查询不起作用。
我必须创建一个temp_表才能完成工作。
我使用 DbVisualizer Free 10.0.8 [Build #2841]
你能帮忙解释一下发生了什么吗?
谢谢
哈里
--this one NOT works
select (case when n_case IN ('1','2','3','4') then 'group1'
else 'group2' end) as groupid, count(*)
from mytable
group by groupid
order by groupid;
--this one NOT works
select groupid, count(*)
from
(select (case when n_case IN ('1','2','3','4') then 'group1'
else 'group2' end) as groupid, count(*)
from mytable)
group by groupid
order by groupid;
--This one works
with temp_ as (select (case when n_case IN ('1','2','3','4') then 'group1'
else 'group2' end) as groupid, *
from mytable
)
select groupid, count(*)
from temp_
group by groupid
order by groupid;
SQL 语句解析序列以这种方式工作。只是在对行进行分组时没有名称为"groupid"的列(目前只有"n_case"(。 这就是为什么它可以与子查询正常工作的原因(当列"groupid"时 已经形成(。
稍微修复了您的第二个查询(我已经从子查询中删除了count(*(:
select groupid, count(*)
from
(select (case when n_case IN ('1','2','3','4') then 'group1'
else 'group2' end) as groupid
from mytable) f
group by groupid
order by groupid;
或者试试这个:
select case when n_case IN ('1','2','3','4') then 'group1'
else 'group2' end groupid, count(*)
from mytable
group by case when n_case IN ('1','2','3','4') then 'group1'
else 'group2' end
order by 1;
SQL小提琴玩