declare @user table (userid int,status int)
declare @userlog table (userid int,logintime datetime)
insert into @user
select 1,1 union
select 2,3 union
select 3,2 union
select 4,1 union
select 5,2 insert into @userlog
select 1,'2007-3-2 12:00:01' union
select 2,'2008-3-2 12:00:01' union
select 2,'2007-3-2 12:00:01' union
select 1,'2007-3-2 11:33:01' union
select 3,'2007-3-2 12:00:01' union
select 4,'2007-3-2 12:00:01'select 天数,count(userid) as 总数,
sum(case when status=1 then 1 else 0 end) as status1,
sum(case when status=2 then 1 else 0 end) as status2,
sum(case when status=3 then 1 else 0 end) as status3
from (
select a.userid,count(distinct convert(char(10),logintime,21)) as 天数,a.status
from @user a
inner join @userlog b on a.userid=b.userid
group by a.userid,a.status
) c
group by 天数/*
天数 总数 status1 status2 status3
----------- ----------- ----------- ----------- -----------
1 3 2 1 0
2 1 0 0 1(2 行受影响)*/
declare @User table([userid] int,[status] int)
Insert @User
select 1,1 union all
select 2,3 union all
select 3,2 union all
select 4,1 union all
select 5,2
--Select * from @Userdeclare @Userlog table([userid] int,[logintime] Datetime)
Insert @Userlog
select 1,'2007-3-2' union all
select 2,'2008-3-2' union all
select 1,'2007-3-2' union all
select 3,'2007-3-2' union all
select 4,'2007-3-2'
--Select * from @Userlog
select u.[userid] as '用户',
count(distinct g.[logintime]) ' 登录天数',
count(1) as '总数',
sum(case when u.status =1 then 1 else 0 end) as 'status1' ,
sum(case when u.status =2 then 1 else 0 end) as 'status2' ,
sum(case when u.status =3 then 1 else 0 end) as 'status3'
from @User u
left join (select distinct [userid],convert(nvarchar(10),[logintime],120) as [logintime] from @Userlog)
g on g.[userid] =u.[userid]
group by u.[userid]
/*
用户 登录天数 总数 status1 status2 status3
----------- ----------- ----------- ----------- ----------- -----------
1 1 1 1 0 0
2 1 1 0 0 1
3 1 1 0 1 0
4 1 1 1 0 0
5 0 1 0 1 0
*/
不好意思,表达能力太差,呵呵
sum(case when u.status =1 then 1 else 0 end) as 'status1' ,
sum(case when u.status =2 then 1 else 0 end) as 'status2' ,
sum(case when u.status =3 then 1 else 0 end) as 'status3'
create table [user](userid int,status int)
create table userlog(userid int,logintime datetime)
insert into [user]
select 1,1 union
select 2,3 union
select 3,2 union
select 4,1 union
select 5,2 insert into userlog
select 1,'2007-3-2 12:00:01' union
select 2,'2008-3-2 12:00:01' union
select 2,'2007-3-2 12:00:01' union
select 1,'2007-3-2 11:33:01' union
select 3,'2007-3-2 12:00:01' union
select 4,'2007-3-2 12:00:01'declare @a varchar(1000)
set @a=''
select @a=@a+',status'+rtrim(status)+'=sum(1-sign(abs(status-'+rtrim(status)+')))' from [user] group by status
set @a='select 天数,count(A.userid) 总数'+@a+'from(
select userid,count(distinct(convert(varchar(10),logintime,120)))天数 from Userlog group by userid
)A left join [user] B on a.userid=b.userid group by 天数 order by 天数 desc '
exec (@A)
--------------------------------------
2 1 0 0 1
1 3 2 1 0
再去连下Userlog 表