你所在的贴子位置为"Oracle开发", 给个排序: select id,count(*) abc,sum(decode(col1,0,1,0)) c0,sum(decode(col1,1,1,0)) c1,sum(decode(col,2,1,0)) c2 from your_table group by id order by abc desc;
select id,count(*),sum(decode(col1,0,1,0)) c0,sum(decode(col1,1,1,0)) c1,sum(decode(col,2,1,0)) c2 from your_table group by id;decode 相当于swict +case
select * from (select id,count(*) ct,sum(decode(col1,0,1,0)) c0,sum(decode(col1,1,1,0)) c1,sum(decode(col,2,1,0)) c2 from table_name group by id) order by ct
select id,count(*) as 总条数 ,sum(case col1 when 0 then 1 else 0 end) as col1为0的条数 ,sum(case col1 when 1 then 1 else 0 end) as col1为1的条数 ,sum(case col1 when 2 then 1 else 0 end) as col1为2的条数 from 表 group by id
select id,count(*),sum(decode(col1,0,1,0)) c0,sum(decode(col1,1,1,0)) c1,sum(decode(col,2,1,0)) c2 from your_table group by id order by 1
有informix版,把你的问题转到那里去吧。
selct id ,count(*) 总条数 ,sum(case when col1=0 then 1 else 0 end) COL1值为0的记录数 ,sum(case when col1=1 then 1 else 0 end) COL1值为1的记录数 ,sum(case when col1=2 then 1 else 0 end) COL1值为2的记录数 from 表 group by id
给个排序:
select id,count(*) abc,sum(decode(col1,0,1,0)) c0,sum(decode(col1,1,1,0)) c1,sum(decode(col,2,1,0)) c2 from your_table group by id order by abc desc;
,sum(case col1 when 0 then 1 else 0 end) as col1为0的条数
,sum(case col1 when 1 then 1 else 0 end) as col1为1的条数
,sum(case col1 when 2 then 1 else 0 end) as col1为2的条数
from 表
group by id
,count(*) 总条数
,sum(case when col1=0 then 1 else 0 end) COL1值为0的记录数
,sum(case when col1=1 then 1 else 0 end) COL1值为1的记录数
,sum(case when col1=2 then 1 else 0 end) COL1值为2的记录数
from 表 group by id