Group by 后 细分数据

[ONE-PIECE]

select cartypesn,
sum(case w2_onschedule_sort when 10 then 1 else 0 end) 提前入库,
sum(case w2_onschedule_sort when 20 then 1 else 0 end) 计划内入库,
sum(case when w2_onschedule_sort in (30,40) then 1 else 0 end) 延迟入库
from w2summary_day_h_d m group by cartypesn;


有没有办法将 提前入库、 计划内入库、延迟入库
在进行细分 得到 A有多少 B有多少 ？


大虾 帮我看看 急！！！！！！！

[linwaterbin]

单独汇总吗？group by rollup(A,B);

[ONE-PIECE]

列 X Y Z A 10 1 A 10 1 A 20 0 A 20 0 A 30 1 B 10 0 B 20 1 C 30 0 C 10 1 先按X分组 得到 10、20、30 分类有多少，在细分Z的数量 得到下面结果，不知道我的表述明白没？ A 2 2 1 B 1 1 0 C 1 0 1

引用 1 楼 linwaterbin 的回复:

单独汇总吗？group by rollup(A,B);

[ONE-PIECE]

列 X Y Z A 10 1 A 10 1 A 20 0 A 20 0 A 30 1 B 10 0 B 20 1 C 30 0 C 10 1 先按X分组 得到 10、20、30 分类有多少，在细分Z的数量 得到下面结果，不知道我的表述明白没？ 10 20 30 A 2 2 1 B 1 1 0 C 1 0 1

[小海葵1]

WITH TEST AS ( SELECT 'A' AS X,'10' AS Y,'1' AS Z FROM DUAL UNION ALL SELECT 'A' AS X,'10' AS Y,'1' AS Z FROM DUAL UNION ALL SELECT 'A' AS X,'20' AS Y,'0' AS Z FROM DUAL UNION ALL SELECT 'A' AS X,'20' AS Y,'0' AS Z FROM DUAL UNION ALL SELECT 'A' AS X,'30' AS Y,'1' AS Z FROM DUAL UNION ALL SELECT 'B' AS X,'10' AS Y,'0' AS Z FROM DUAL UNION ALL SELECT 'B' AS X,'20' AS Y,'1' AS Z FROM DUAL UNION ALL SELECT 'C' AS X,'30' AS Y,'0' AS Z FROM DUAL UNION ALL SELECT 'C' AS X,'10' AS Y,'1' AS Z FROM DUAL ) SELECT X, MAX(DECODE(Y,'10',Z,0)) S1, MAX(DECODE(Y,'20',Z,0)) S2, MAX(DECODE(Y,'30',Z,0)) S3 FROM ( SELECT X,Y,COUNT(Z) Z FROM TEST GROUP BY X,Y) GROUP BY X

[ONE-PIECE]

谢谢大家的回答。。我自己搞定了！！！！！！ select cartypesn, sum(case when w2_onschedule_sort=10 and W2_BILL_SORT=1 then 1 else 0 end) tqrkA, sum(case when w2_onschedule_sort=10 and W2_BILL_SORT=0 then 1 else 0 end) tqrkB, sum(case when w2_onschedule_sort=20 and W2_BILL_SORT=1 then 1 else 0 end) jhnrkA, sum(case when w2_onschedule_sort=20 and W2_BILL_SORT=0 then 1 else 0 end) jhnrkb, sum(case when w2_onschedule_sort in (30,40) and W2_BILL_SORT=1 then 1 else 0 end) ycrkA, sum(case when w2_onschedule_sort in (30,40) and W2_BILL_SORT=0 then 1 else 0 end) ycrkB, max(w2_Time) as w2_Time from w2summary_day_h_d m where to_char(w2_Time,'yyyy-mm-dd')='2013-01-07' group by cartypesn ;

引用 4 楼 restbely 的回复:

WITH TEST AS ( SELECT 'A' AS X,'10' AS Y,'1' AS Z FROM DUAL UNION ALL SELECT 'A' AS X,'10' AS Y,'1' AS Z FROM DUAL UNION ALL SELECT 'A' AS X,'20' AS Y,'0' AS Z FROM DUAL UNION ALL SELECT 'A' AS……

[善若止水]

我看楼主的描述是没有看懂，但看你写的sql语句是看明白了。学习了

[灰桃K]

同楼上，描述没看懂，SQL看懂了。学习