求一个统计查询的语句！急。。。

Question

以下是用户登录访问表（loginT）：
name              ip                                                     logintime
---------------------------------------------------------------------------------------
张三         11.22.33.44                                        2009-01-01 0:00:00
李四         22.33.44.55                                        2009-01-02 0:00:00
张三         11.22.33.44                                        2009-01-04 0:00:00
王五         33.44.55.66                                        2009-01-04 0:00:00
赵六         23.34.56.78                                        2009-02-01 0:00:00
李四         210.57.32.34                                      2009-02-18 0:00:00想要统计出如下效果:月份              人数           独立IP
----------------------------------------
2009-01           3              3
2009-02           2              2请问语句应该怎么写?

Answer 1

declare @t table (name varchar(20),ip varchar(20),logintime datetime)
--插入数据
insert into @t
select '张三','11.22.33.44','2009-01-01' union
select '李四','22.33.44.55','2009-01-02' union
select '张三','11.22.33.44','2009-01-04' union
select '王五','33.44.55.66','2009-01-04' union
select '赵六','23.34.56.78','2009-02-01' union
select '李四','210.57.32.34','2009-02-18'
select convert(varchar(7),logintime,120),count(distinct ip),count(distinct name) from @t
group by convert(varchar(7),logintime,120)
 

Answer 2

select left(logintime,7),count(distinct ip),count(distinct name) from @t
group by left(logintime,7)
order by left(logintime,7)