这个能运行得出每个用户名每天登陆的次数:declare @temp table(username varchar(20),logintime datetime)insert into @temp
select 'sss','2008-1-1'
union all
select 'sss','2008-1-2'
union all
select 'vc','2008-1-5'
union all
select 'sss','2008-1-1'
union all
select 'sss','2008-3-1'
union all
select 'vc','2008-1-6'
union all
select 'vc','2008-1-10'select username,login,count(login) as logincount
from (select convert(char(10),logintime,112) as login,username from @temp) as s group by username,login order by username,login,logincount desc结果是:
username login logincount
-------------------- ---------- -----------
sss 20080101 2
sss 20080102 1
sss 20080301 1
vc 20080105 1
vc 20080106 1
vc 20080110 1(所影响的行数为 6 行)
select 'sss','2008-1-1'
union all
select 'sss','2008-1-2'
union all
select 'vc','2008-1-5'
union all
select 'sss','2008-1-1'
union all
select 'sss','2008-3-1'
union all
select 'vc','2008-1-6'
union all
select 'vc','2008-1-10'select username,login,count(login) as logincount
from (select convert(char(10),logintime,112) as login,username from @temp) as s group by username,login order by username,login,logincount desc结果是:
username login logincount
-------------------- ---------- -----------
sss 20080101 2
sss 20080102 1
sss 20080301 1
vc 20080105 1
vc 20080106 1
vc 20080110 1(所影响的行数为 6 行)
from ttt
group by convert(datetime,left(logintime,10))
,username
from ttt
group by convert(char(10),logintime,112),username