问题是你数据集里都没uid=3和uid=5的纪录。参考 select t.*,round(s.scroe,1) as scroe , round(if(s.total is null,0,s.total),1) as summary from (select 1 as uid union select 2 as uid union select 3 as uid union select 4 as uid union select 5 as uid) t left join (select uid,scroe,(select sum(scroe) from a where uid=aa.uid group by uid) as total from a as aa) s on s.uid = t.uid
用户表tu得分表tSQL:SELECT tu.uid,t.score,a.total from test t right join test_user tu on tu.uid=t.uid left join (SELECT uid,sum(score) as total from test group by uid) a on a.uid=t.uid order by tu.uid结果
CREATE TABLE a
(`id` int, `uid` int, `scroe` float)
;
INSERT INTO a
(`id`, `uid`, `scroe`)
VALUES
(1, 1, 1.5),
(2, 1, 1.8),
(3, 2, -5.5),
(4, 1, 0.8),
(5, 2, 8.2),
(6, 1, -2),
(7, 4, 3),
(8, 2, 2.3)
;
select t.*,round(s.scroe,1) as scroe , round(if(s.total is null,0,s.total),1) as summary from
(select 1 as uid
union
select 2 as uid
union
select 3 as uid
union
select 4 as uid
union
select 5 as uid) t left join
(select uid,scroe,(select sum(scroe) from a where uid=aa.uid group by uid) as total from a as aa) s
on s.uid = t.uid
uid是和user表里管理的字段,根据user表里的记录来读取:
在这里:http://bbs.csdn.net/topics/390673185
用户表tu得分表tSQL:SELECT tu.uid,t.score,a.total from test t right join test_user tu on tu.uid=t.uid left join (SELECT uid,sum(score) as total from test group by uid) a on a.uid=t.uid order by tu.uid结果