SQL简单查询问题！

[zhjian6]

我有一个表
tableA
userid uyear umonth uday



四个字段都是int，用户来记录用户登录用的！

select userid,count(userid) as count2 from temped where umonth=2 group by userid order by userid

这条语句，可以输入2月份，各用户登录的次数！

现在，想2，3,4，5,6　月份一起输入　该如何写？
得到的结果希望是

userid count2(二月) count3(三月) count4(四月) count5(五月) count6(六月)

[pbsql]

select userid,
sum(case umonth when 2 then 1 else 0 end) count2,
sum(case umonth when 3 then 1 else 0 end) count3,
sum(case umonth when 4 then 1 else 0 end) count4,
sum(case umonth when 5 then 1 else 0 end) count5,
sum(case umonth when 6 then 1 else 0 end) count6
from temped t
group by userid order by userid

[frankwong]

如果你的月份数是确定的话，用“风云”的方法可以，如果月份数不确定，即是列的数量不确定，建议使用一下方法：

declare @sql varchar(8000)
set @sql='select userid'
select @sql = @sql + ',sum(case umonth when ''' + a.umonth + '''then 1 else 0 end) as ' + a.umonth + ' '
from (select distinct umonth from [tableA]) a

set @sql =@sql + 'from [tableA] group by userid'
exec (@sql)

[zanglinfeng]

declare @sql varchar(8000)
set @sql='select userid'
select @sql = @sql + ',sum(case umonth when ''' + a.umonth + '''then 1 else 0 end) as ' + a.umonth + ' '
from (select distinct umonth from [tableA]) a

set @sql =@sql + 'from [tableA] group by userid'
exec (@sql)


不错,赞同!

[zjcxc]

--只有6个月,直接写就行啦,即使有12个月,也可以直接写嘛,反正比较固定

select userid
,[2月份]=sum(case umonth when 2 then 1 else 0 end)
,[3月份]=sum(case umonth when 3 then 1 else 0 end)
,[4月份]=sum(case umonth when 4 then 1 else 0 end)
,[5月份]=sum(case umonth when 5 then 1 else 0 end)
,[6月份]=sum(case umonth when 6 then 1 else 0 end)
from tableA
where uyear=2003 --统计那一年
group by userid
order by userid

[zhjian6]

谢谢各位，我用了
select userid,
sum(case umonth when 2 then 1 else 0 end) count2,
sum(case umonth when 3 then 1 else 0 end) count3,
sum(case umonth when 4 then 1 else 0 end) count4,
sum(case umonth when 5 then 1 else 0 end) count5,
sum(case umonth when 6 then 1 else 0 end) count6
from temped
group by userid order by userid
是可以的，但是想加点东西就出错
我另外有个表，是用户信息表，有ID，对应的姓名。
我现在想把输出的ID变成姓名，其它不变改怎么写？

[pbsql]

select userid,
(select 姓名 from 用户信息表 where ID=temped.userid) name,
sum(case umonth when 2 then 1 else 0 end) count2,
sum(case umonth when 3 then 1 else 0 end) count3,
sum(case umonth when 4 then 1 else 0 end) count4,
sum(case umonth when 5 then 1 else 0 end) count5,
sum(case umonth when 6 then 1 else 0 end) count6
from temped
group by userid order by userid

[zhjian6]

谢谢，都是高手！

[zjcxc]

--建议用连接,效率高一些:

select a.userid,
b.name,
sum(case umonth when 2 then 1 else 0 end) count2,
sum(case umonth when 3 then 1 else 0 end) count3,
sum(case umonth when 4 then 1 else 0 end) count4,
sum(case umonth when 5 then 1 else 0 end) count5,
sum(case umonth when 6 then 1 else 0 end) count6
from temped a join 用户信息表 b on a.userid=id
group by userid,姓名
order by userid