group by 后面能否加子查询？

显示全部楼层 · 2013-10-10 21:39:18

如下:
select case when (字段1 in (select 字段2 from 表)) then 1 else 2 end, .....from 表1
group by
case when (字段1 in (select 字段2 from 表)) then 1 else 2 end

千问 · 2013-10-10 21:39:18

It's not!

千问 · 2013-10-10 21:39:18

select g1 from (
select case when (字段1 in (select 字段2 from 表)) then 1 else 2 end, .....) g1 from 表1
)
group by g1

千问 · 2013-10-10 21:39:18

不能.

千问 · 2013-10-10 21:39:18

可以。
如下例所示：
QL> create table t1 as select rownum id,'T1' col1 from dual connect by rownum create table t2 as select rownum id from dual connect by rownum select case when id in (select id from t2) then 1 else 2 end v1,count(*) from t1
2 group bycase when id in (select id from t2) then 1 else 2 end;

V1 COUNT(*)
---------- ----------
1
5
2
95
SQL>

千问 · 2013-10-10 21:39:18

试了一下，是可以的，谢谢！