晕乎晕乎,这个是什么意思? 有这样一个对应关系(id的前四位就能决定对应关系) 101101 a 101102 a 1012 a 1015 b 10160101 b 10160102 b 101602 b .....
WITH t_cname AS ( SELECT 1 CID ,'公司A' Cname FROM dual UNION ALL SELECT 2, '公司B' FROM dual UNION ALL SELECT 3, '公司C' FROM dual UNION ALL SELECT 4, '公司D' FROM dual UNION ALL SELECT 5, '公司E' FROM dual )
, t_count AS ( SELECT 101101 id ,1 cid ,10 NUM from dual UNION ALL SELECT 101101 ,1 ,2 from dual UNION ALL SELECT 101102 ,1 ,15 from dual UNION ALL SELECT 1012 ,1 ,8 from dual UNION ALL SELECT 1015 ,1 ,16 from dual UNION ALL SELECT 1015 ,1 ,7 from dual UNION ALL SELECT 1015 ,1 ,8 from dual UNION ALL SELECT 10160101 ,1 ,9 from dual UNION ALL SELECT 10160102 ,1 ,5 from dual UNION ALL SELECT 10160102 ,1 ,19 from dual UNION ALL SELECT 101602 ,1 ,5 from dual UNION ALL SELECT 101101 ,2 ,5 from dual UNION ALL SELECT 101102 ,2 ,15 from dual UNION ALL SELECT 1012 ,2 ,8 from dual UNION ALL SELECT 1015 ,2 ,7 from dual UNION ALL SELECT 10160101 ,2 ,9 from dual UNION ALL SELECT 10160102 ,2 ,0 from dual UNION ALL SELECT 101602 ,2 ,3 from dual ), t_ab AS ( SELECT 101101 id ,'a' ab from dual UNION ALL SELECT 101102 ,'a' from dual UNION ALL SELECT 1012 ,'a' from dual UNION ALL SELECT 1015 ,'b' from dual UNION ALL SELECT 10160101 ,'b' from dual UNION ALL SELECT 10160102 ,'b' from dual UNION ALL SELECT 101602 ,'b' from dual )
SELECT cname ,sum(decode(ab,'a',num,0)) a ,sum(decode(ab,'b',num,0)) b FROM t_count a FULL JOIN t_ab b ON a.id = b.id FULL JOIN t_cname c ON a.cid = c.cid GROUP BY cname ORDER BY cname
有这样一个对应关系(id的前四位就能决定对应关系)
101101 a
101102 a
1012 a
1015 b
10160101 b
10160102 b
101602 b
.....
WITH t_cname AS
(
SELECT 1 CID ,'公司A' Cname FROM dual
UNION ALL SELECT 2, '公司B' FROM dual
UNION ALL SELECT 3, '公司C' FROM dual
UNION ALL SELECT 4, '公司D' FROM dual
UNION ALL SELECT 5, '公司E' FROM dual
)
, t_count AS
(
SELECT 101101 id ,1 cid ,10 NUM from dual
UNION ALL SELECT 101101 ,1 ,2 from dual
UNION ALL SELECT 101102 ,1 ,15 from dual
UNION ALL SELECT 1012 ,1 ,8 from dual
UNION ALL SELECT 1015 ,1 ,16 from dual
UNION ALL SELECT 1015 ,1 ,7 from dual
UNION ALL SELECT 1015 ,1 ,8 from dual
UNION ALL SELECT 10160101 ,1 ,9 from dual
UNION ALL SELECT 10160102 ,1 ,5 from dual
UNION ALL SELECT 10160102 ,1 ,19 from dual
UNION ALL SELECT 101602 ,1 ,5 from dual
UNION ALL SELECT 101101 ,2 ,5 from dual
UNION ALL SELECT 101102 ,2 ,15 from dual
UNION ALL SELECT 1012 ,2 ,8 from dual
UNION ALL SELECT 1015 ,2 ,7 from dual
UNION ALL SELECT 10160101 ,2 ,9 from dual
UNION ALL SELECT 10160102 ,2 ,0 from dual
UNION ALL SELECT 101602 ,2 ,3 from dual
), t_ab AS
(
SELECT 101101 id ,'a' ab from dual
UNION ALL SELECT 101102 ,'a' from dual
UNION ALL SELECT 1012 ,'a' from dual
UNION ALL SELECT 1015 ,'b' from dual
UNION ALL SELECT 10160101 ,'b' from dual
UNION ALL SELECT 10160102 ,'b' from dual
UNION ALL SELECT 101602 ,'b' from dual
)
SELECT cname
,sum(decode(ab,'a',num,0)) a
,sum(decode(ab,'b',num,0)) b
FROM t_count a
FULL JOIN t_ab b ON a.id = b.id
FULL JOIN t_cname c ON a.cid = c.cid
GROUP BY cname
ORDER BY cname