求一补全统计结果sql

[baobao28]

有t1表为类型表
typeid typename
1 a
2 b
3 c

有t2表为类型人员数据表
typeid personid score
1 1 10
2 1 20
3 1 30
3 1 20
3 1 10
1 2 10
2 2 30
1 3 20

有t3表为人员表
personid personname
1 张三
2 李四
3 王五

希望得到如下统计结果
personname typename score
张三 a 10
张三 b 20
张三 c 60
李四 a 10
李四 b 30
李四 c 0
王五 a 20
王五 b 0
王五 c 0

希望得到的结果为所有人所有类型数据结果加和的列表，列表中是展现出人员和类型对应的数据结果表中的数据列加和

[yinan9]

select t3.personname,t1.typename,sum(t2.score) from t1,t2,t3 where t1.typeid=t2.typeid and t2.personid=t3.personid group by t3.personname,t1.typename;

[yinan9]

select t3.personname,t1.typename,sum(t2.score) from t1,t2,t3 where t1.typeid=t2.typeid and t2.personid=t3.personid group by t3.personname,t1.typename;

[baobao28]

楼上给的sql语句没有把人员和类型补全啊，不是我上面给出的想要的查询结果啊，缺失的哪部分数据得用0补上啊

[ccqqwheat]

select max(t3.personname) personname,t2.typename typename,sum(t1.score) from t1 left join persion t3 on t3.personid=t1.personid left join type t2 on t2.typeid=t1.typeid group by t2.typename

[yinan9]

没仔细看，应该是这个

select t4.personname, t4.typename, nvl(sum(score), 0)
  from (select t3.*, t1.* from t3, t1) t4
  left join t2
    on t2.typeid = t4.typeid
   and t2.personid = t4.personid
 group by t4.personname, t4.typename
 order by 1

[mwf678]

SELECT MAX(T.PERSONNAME), MAX(T.TYPENAME), SUM(NVL(T2.SCORE, 0)) FROM (SELECT * FROM T1 T1, T3 T3) T LEFT JOIN T2 T2 ON T.PERSONID = T2.PERSONID AND T.TYPEID = T2.TYPEID GROUP BY T.PERSONID, T.TYPEID ORDER BY T.PERSONID, T.TYPEID 先把t1，t3表都列出来再左连接计算t2表

[yinan9]

SQL> SQL> select t4.personname, t4.typename, nvl(sum(score), 0) 2 from (select t3.*, t1.* from t3, t1) t4 3 left join t2 4 on t2.typeid = t4.typeid 5 and t2.personid = t4.personid 6 group by t4.personname, t4.typename 7 order by 1; PERSONNAME TYPENAME NVL(SUM(SCORE),0) -------------------- ---------- ----------------- 李四 a 10 李四 b 30 李四 c 0 王五 a 20 王五 b 0 王五 c 0 张三 a 10 张三 b 10 张三 c 70

[baobao28]

谢谢了，就是我想要的结果了

[baobao28]

谢谢了。。。。。。

[小海葵1]

SELECT T3.personname, T1.typename, SUM(T.score) FROM (SELECT NVL(T2.typeid, T.typeid) AS typeid, NVL(T2.personid, T.personid) AS personid, NVL(T2.score, 0) AS score FROM T2 FULL JOIN (SELECT T1.typeid, T2.personid FROM T1 FULL JOIN T2 ON 1 = 1 MINUS SELECT T2.typeid, T2.personid FROM T2) T ON T2.typeid = T.typeid AND T2.personid = T.personid) T LEFT JOIN T1 ON T.typeid = T1.typeid LEFT JOIN T3 ON T.personid = T3.personid GROUP BY T3.personname, T1.typename ORDER BY T3.personname, T1.typename