查询语句求助

[mabailin]

有这样两个表

COMPANY

CID Cname
1 公司A
2 公司B
3 公司C
4 公司D
5 公司E


count
id cid NUM
101101 1 10
101101 1 2
101102 1 15
1012 1 8
1015 1 16
1015 1 7
1015 1 8
10160101 1 9
10160102 1 5
10160102 1 19
101602 1 5
101101 2 5
101102 2 15
1012 2 8
1015 2 7
10160101 2 9
10160102 2 0
101602 2 3
.....



有这样一个对应关系（id的前四位就能决定对应关系）
101101 a
101102 a
1012 a
1015 b
10160101 b
10160102 b
101602 b
.....

但是这个对应关系现在不能进入数据库，需要根据上面两张表查询出如下的结果
Cname a b
公司A 35 69
公司B 38 19


这个查询怎么写效率比较高

[luckyzhangye]

晕乎晕乎，这个是什么意思？
有这样一个对应关系（id的前四位就能决定对应关系）
101101 a
101102 a
1012 a
1015 b
10160101 b
10160102 b
101602 b
.....

[bosslichuan]

貌似复杂

[coolkisses]

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