比如SQL语句
select sum(score),userid from tab where userid in(1,2,3,4,5,6) group by userid
order by sum(score) desc
现在只有2和6用户有积分记录，其他几位没有，所以查出来的结果就类似
200 2
90 6
我想其他没有积分记录的显示0，应该怎么写呢？
200 2
90 6
0 1
0 3
0 4
0 5
谢谢！
------解决方案--------------------
- SQL code
select a.userid, score=(select sum(score) from tab where userid=a.userid) from (select 1 as userid union select 2 union select 3 union select 4 union select 5 union select 6) aorder by 2 desc
------解决方案--------------------
- SQL code
SELECT ISNULL(b.score, 0) score , a.number useridFROM master..spt_values a LEFT JOIN ( SELECT SUM(score) score , userid FROM tab WHERE userid IN ( 1, 2, 3, 4, 5, 6 ) GROUP BY userid ) b ON a.number = b.useridWHERE a.type = 'P' AND a.number IN ( 1, 2, 3, 4, 5, 6 )
------解决方案--------------------
- SQL code
select isnull(n.score,0) score , m.userid from(select 1 userid union select 2 userid union select 3 userid union select 4 userid union select 5 userid union select 6 userid ) mleft join(select sum(score) score,userid from tab where userid in(1,2,3,4,5,6) group by userid) non m.userid = n.useridorder by score desc , m.userid