查询 集锦

查询 汇总
有一张 用户登录记录表(结构及数据如)
表名：UserLogin
uid(int) loginDate(datetime)
100101 2011-05-09 14:25:23
100102 2011-05-09 14:25:25
100103 2011-05-10 14:25:35
100101 2011-05-10 14:30:00
100101 2011-05-10 15:20:00
100101 2011-05-10 20:20:00

100102 2011-05-11 15:30:00
...
现在怎么得出(假如记录就这么多)
时间 登录次数
2011-01 0
2011-02 0
2011-03 0
2011-04 0
2011-05 5（同一天一个账号不管登录多少次都只算一次）
...
2011-12 0
大神帮帮忙啊

[解决办法]

SQL code

select left(dt,7) as 时间,sum(cnt) as 登录次数from(  select convert(varchar(10),loginDate,120) as dt,count(distinct uid) as cnt  from tb  group by convert(varchar(10),loginDate,120) as dt) tgroup by left(dt,7)