谢谢大家 都回答得很好,但现在需求又变了 还要查出在使用中VIP 用户数和在使用中的普通用户数
表名 userinfo 用户类型为1 是VIP 用户类型为2 是普通用户 在使用中 即已经注册的用户 在线等呀id name 注册时间 到期时间 用户类型
1 张三 2008-03-04 2009-06-01 1
2 李四 2007-02-16 2010-04-04 2
要求
年月 本月注册用户数 本月到期用户数 本月在使用中的VIP用户数 本月在使用中的普通用户数
200701 67 11 3000 8000
200702 133 21 3012 8100
200703 122 23 3065 8210
200706 142 12 3223 8266
表名 userinfo 用户类型为1 是VIP 用户类型为2 是普通用户 在使用中 即已经注册的用户 在线等呀id name 注册时间 到期时间 用户类型
1 张三 2008-03-04 2009-06-01 1
2 李四 2007-02-16 2010-04-04 2
要求
年月 本月注册用户数 本月到期用户数 本月在使用中的VIP用户数 本月在使用中的普通用户数
200701 67 11 3000 8000
200702 133 21 3012 8100
200703 122 23 3065 8210
200706 142 12 3223 8266
SELECT CONVERT(VARCHAR(6),DATEADD(MONTH,NUMBER,'2007-1-1')) AS 年月 ,
(SELECT COUNT(1) FROM userinfo WHERE DATEDIFF(MONTH,DATEADD(MONTH,NUMBER,'2007-1-1'),注册时间)=0 and 用户类型=1 ) AS 本月注册用户数,
(SELECT COUNT(1) FROM userinfo WHERE DATEDIFF(MONTH,DATEADD(MONTH,NUMBER,'2007-1-1'),到期时间)=0 and 用户类型=1 ) AS 本月到期用户数,(SELECT COUNT(1) FROM userinfo WHERE DATEDIFF(MONTH,注册时间,DATEADD(MONTH,NUMBER,'2007-1-1'))>=0 and DATEDIFF(MONTH,到期时间,DATEADD(MONTH,NUMBER,'2007-1-1'))<0 and 用户类型=2 ) AS 本月在使用中的VIP用户数,
(SELECT COUNT(1) FROM userinfo WHERE DATEDIFF(MONTH,注册时间,DATEADD(MONTH,NUMBER,'2007-1-1'))>=0 and DATEDIFF(MONTH,到期时间,DATEADD(MONTH,NUMBER,'2007-1-1'))<0 and 用户类型=1 ) AS 本月在使用中的普通用户数
FROM MASTER..SPT_VALUES N
WHERE N.TYPE='P' AND N.NUMBER BETWEEN 0 AND 35
(SELECT COUNT(1) FROM userinfo WHERE DATEDIFF(MONTH,DATEADD(MONTH,NUMBER,'2007-1-1'),注册时间)=0 and 用户类型=1 ) AS 本月注册用户数,
(SELECT COUNT(1) FROM userinfo WHERE DATEDIFF(MONTH,DATEADD(MONTH,NUMBER,'2007-1-1'),到期时间)=0 and 用户类型=1 ) AS 本月到期用户数,(SELECT COUNT(1) FROM userinfo WHERE DATEDIFF(MONTH,注册时间,DATEADD(MONTH,NUMBER,'2007-1-1'))>=0 and DATEDIFF(MONTH,到期时间,DATEADD(MONTH,NUMBER,'2007-1-1'))<0 and 用户类型=2 ) AS 本月在使用中的VIP用户数,
(SELECT COUNT(1) FROM userinfo WHERE DATEDIFF(MONTH,注册时间,DATEADD(MONTH,NUMBER,'2007-1-1'))>=0 and DATEDIFF(MONTH,到期时间,DATEADD(MONTH,NUMBER,'2007-1-1'))<0 and 用户类型=1 ) AS 本月在使用中的普通用户数
FROM MASTER..SPT_VALUES N
WHERE N.TYPE='P' AND N.NUMBER BETWEEN 0 AND 35
and DATEDIFF(MONTH,(select min(注册时间) from userinfo),DATEADD(MONTH,NUMBER,'2007-1-1')) >=0
and DATEDIFF(MONTH,(select max(到期时间) from userinfo),DATEADD(MONTH,NUMBER,'2007-1-1')) <=0
(SELECT COUNT(1) FROM userinfo WHERE DATEDIFF(MONTH,DATEADD(MONTH,NUMBER,'2007-1-1'),注册时间)=0 and 用户类型=1 ) AS 本月注册用户数,
(SELECT COUNT(1) FROM userinfo WHERE DATEDIFF(MONTH,DATEADD(MONTH,NUMBER,'2007-1-1'),到期时间)=0 and 用户类型=1 ) AS 本月到期用户数,(SELECT COUNT(1) FROM userinfo WHERE DATEDIFF(MONTH,注册时间,DATEADD(MONTH,NUMBER,'2007-1-1'))>=0 and DATEDIFF(MONTH,到期时间,DATEADD(MONTH,NUMBER,'2007-1-1'))<0 and 用户类型=2 ) AS 本月在使用中的VIP用户数,
(SELECT COUNT(1) FROM userinfo WHERE DATEDIFF(MONTH,注册时间,DATEADD(MONTH,NUMBER,'2007-1-1'))>=0 and DATEDIFF(MONTH,到期时间,DATEADD(MONTH,NUMBER,'2007-1-1'))<0 and 用户类型=1 ) AS 本月在使用中的普通用户数
FROM MASTER..SPT_VALUES N
WHERE N.TYPE='P' AND N.NUMBER BETWEEN 0 AND 35
and DATEDIFF(MONTH,(select min(注册时间) from userinfo where datediff(month,'2007-1-1',到期时间)>=0 and datediff(month,'2009-12-1',注册时间)<=0),DATEADD(MONTH,NUMBER,'2007-1-1')) >=0
and DATEDIFF(MONTH,(select max(到期时间) from userinfo where datediff(month,'2007-1-1',到期时间)>=0 and datediff(month,'2009-12-1',注册时间)<=0),DATEADD(MONTH,NUMBER,'2007-1-1')) <=0
SELECT CONVERT(VARCHAR(6),DATEADD(MONTH,NUMBER,'2007-1-1'),112) AS 年月 ,
(SELECT COUNT(1) FROM userinfo WHERE DATEDIFF(MONTH,DATEADD(MONTH,NUMBER,'2007-1-1'),注册时间)=0 and 用户类型=1 ) AS 本月注册用户数,
(SELECT COUNT(1) FROM userinfo WHERE DATEDIFF(MONTH,DATEADD(MONTH,NUMBER,'2007-1-1'),到期时间)=0 and 用户类型=1 ) AS 本月到期用户数,(SELECT COUNT(1) FROM userinfo WHERE DATEDIFF(MONTH,注册时间,DATEADD(MONTH,NUMBER,'2007-1-1'))>=0 and DATEDIFF(MONTH,到期时间,DATEADD(MONTH,NUMBER,'2007-1-1'))<0 and 用户类型=2 ) AS 本月在使用中的VIP用户数,
(SELECT COUNT(1) FROM userinfo WHERE DATEDIFF(MONTH,注册时间,DATEADD(MONTH,NUMBER,'2007-1-1'))>=0 and DATEDIFF(MONTH,到期时间,DATEADD(MONTH,NUMBER,'2007-1-1'))<0 and 用户类型=1 ) AS 本月在使用中的普通用户数
FROM MASTER..SPT_VALUES N
WHERE N.TYPE='P' AND N.NUMBER BETWEEN 0 AND DATEDIFF(MONTH,'2007-1-1',getdate())
and DATEDIFF(MONTH,(select min(注册时间) from userinfo where datediff(month,'2007-1-1',到期时间)>=0 and datediff(month,getdate(),注册时间)<=0),DATEADD(MONTH,NUMBER,'2007-1-1')) >=0
and DATEDIFF(MONTH,(select max(到期时间) from userinfo where datediff(month,'2007-1-1',到期时间)>=0 and datediff(month,getdate(),注册时间)<=0),DATEADD(MONTH,NUMBER,'2007-1-1')) <=0
-- 建模拟数据
create table tb (id int, 姓名 varchar(10), 注册时间 datetime, 到期时间 datetime, 用户类型 int)
go
insert into tb select 4,'张三','1905-06-19','1905-06-10',1
union all select 8,'李四','2007-08-03','2009-09-01',1
union all select 9,'王五','2007-08-04','2009-10-01',2
union all select 10,'赵一','2007-09-10','2009-10-02',2
union all select 11,'钱二','2008-12-01','2009-11-12',1
union all select 12,'孙三','2007-12-01','2009-11-12',1
union all select 13,'周儿','2007-12-03','2009-11-12',1
union all select 19,'吴2','2007-12-03','2008-11-12',1
go-- 测试
select
convert(varchar(6),a.日期,112) as 日期,
sum(case 类型 when '注册' then 1 else 0 end) as 本月注册用户数,
sum(case 类型 when '到期' then 1 else 0 end) as 本月到期用户数,
(select count(1) from tb b where convert(varchar(6),a.日期,112) >= convert(varchar(6),b.注册时间,112) and convert(varchar(6),a.日期,112) <= convert(varchar(6),b.到期时间,112) and b.用户类型 = 1) as 本月在使用中的VIP用户数,
(select count(1) from tb b where convert(varchar(6),a.日期,112) >= convert(varchar(6),b.注册时间,112) and convert(varchar(6),a.日期,112) <= convert(varchar(6),b.到期时间,112) and b.用户类型 = 2) as 本月在使用中的普通用户数
from (
select id,用户类型,convert(varchar(6),注册时间,112) as 日期,'注册' as 类型 from tb
union
select id,用户类型,convert(varchar(6),到期时间,112) as 日期,'到期' as 类型 from tb
) a
group by convert(varchar(6),a.日期,112)-- 结果
/*
日期 本月注册用户数 本月到期用户数 本月在使用中的VIP用户数 本月在使用中的普通用户数
------ ----------- ----------- ------------- ------------
190506 1 1 1 0
200708 2 0 1 1
200709 1 0 1 2
200712 3 0 4 2
200811 0 1 4 2
200812 1 0 4 2
200909 0 1 4 2
200910 0 2 3 2
200911 0 3 3 0(9 行受影响)
*/