列 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
列 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
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
谢谢大家的回答。。我自己搞定了!!!!!! 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 ;
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
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
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
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 ;