日期:2014-05-18  浏览次数:20494 次

请教一个记录为空的问题
比如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) a
order by 2 desc

------解决方案--------------------
SQL code
SELECT  ISNULL(b.score, 0) score ,
        a.number userid
FROM    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.userid
WHERE   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 
) m
left join
(
select sum(score) score,userid from tab where userid in(1,2,3,4,5,6) group by userid
) n
on m.userid = n.userid
order by score desc , m.userid