27,579
社区成员
发帖
与我相关
我的任务
分享
create table B(id2 varchar(10) ,starttime varchar(20),endtime varchar(20))
insert B select '1111','2008-01-01','2009-01-01' union all
select '2222','2008-01-21','2009-01-01' union all
select '3333','2008-02-01','2009-01-01' union all
select '4444','2008-02-20','2009-01-01' union all
select '5555','2008-02-13','2009-01-01'
select left(starttime,7) as '月份',count(distinct id2) as '人数'from B group by left(starttime,7)
月份 人数
2008-01 2
2008-02 3
drop table B
create table tb(会员号 varchar(10),起始日期 datetime, 结束日期 datetime)
insert into tb values('1111' , '2008-12-01' , '2009-01-31')
insert into tb values('2222' , '2009-01-01' , '2010-02-21')
insert into tb values('3333' , '2009-01-01' , '2010-02-21')
insert into tb values('4444' , '2009-01-01' , '2009-04-21')
go
--临时表
select top 8000 identity(int,0,1) as id into # from syscolumns a,syscolumns b
select convert(varchar(7),dt,120) 月份 , count(distinct 会员号) 会员总数 from
(
select distinct 会员号 , dateadd(day , n.id , 起始日期) dt from tb , # n where dateadd(day , n.id , 起始日期) <= 结束日期
) t
group by convert(varchar(7),dt,120)
/*
月份 会员总数
------- -----------
2008-12 1
2009-01 4
2009-02 3
2009-03 3
2009-04 3
2009-05 2
2009-06 2
2009-07 2
2009-08 2
2009-09 2
2009-10 2
2009-11 2
2009-12 2
2010-01 2
2010-02 2
(所影响的行数为 15 行)
*/
drop table tb , #
楼主要的是不是这个
if object_id('tb') is not null drop table tb
go
create table tb(会员号 varchar(10),起始日期 datetime, 结束日期 datetime)
insert into tb values('1111' , '2008-12-01' , '2009-01-31')
insert into tb values('2222' , '2009-02-01' , '2010-02-21')
insert into tb values('3333' , '2009-03-01' , '2010-02-21')
insert into tb values('4444' , '2009-04-01' , '2009-04-30')
go
select 起始日期,dateadd(mm,1,a.起始日期)-1 as 结束日期
,会员数目 = (select count(1) from tb where 起始日期 <= a.起始日期 and 结束日期 >= dateadd(mm,1,a.起始日期)-1)
from tb a
group by 起始日期
起始日期 结束日期 会员数目
----------------------- ----------------------- -----------
2008-12-01 00:00:00.000 2008-12-31 00:00:00.000 1
2009-02-01 00:00:00.000 2009-02-28 00:00:00.000 1
2009-03-01 00:00:00.000 2009-03-31 00:00:00.000 2
2009-04-01 00:00:00.000 2009-04-30 00:00:00.000 3
(4 row(s) affected)
create table tb(会员号 varchar(10),起始日期 datetime, 结束日期 datetime)
insert into tb values('1111' , '2008-12-01' , '2009-01-31')
insert into tb values('2222' , '2009-02-01' , '2010-02-21')
insert into tb values('3333' , '2009-03-01' , '2010-02-21')
insert into tb values('4444' , '2009-04-01' , '2009-04-30')
go
--1
select convert(varchar(7),起始日期,120) 年份 , count(*) 会员总数 from tb group by convert(varchar(7),起始日期,120)
/*
年份 会员总数
------- -----------
2008-12 1
2009-02 1
2009-03 1
2009-04 1
(所影响的行数为 4 行)
*/
--2
select convert(varchar(7),结束日期,120) 年份 , count(*) 会员总数 from tb group by convert(varchar(7),结束日期,120)
/*
年份 会员总数
------- -----------
2009-01 1
2009-04 1
2010-02 2
(所影响的行数为 3 行)
*/
--3
--临时表
select top 8000 identity(int,0,1) as id into # from syscolumns a,syscolumns b
select convert(varchar(7),dt,120) 月份 , count(distinct 会员号) 会员总数 from
(
select distinct 会员号 , dateadd(day , n.id , 起始日期) dt from tb , # n where dateadd(day , n.id , 起始日期) <= 结束日期
) t
group by convert(varchar(7),dt,120)
/*
月份 会员总数
------- -----------
2008-12 1
2009-01 1
2009-02 1
2009-03 2
2009-04 3
2009-05 2
2009-06 2
2009-07 2
2009-08 2
2009-09 2
2009-10 2
2009-11 2
2009-12 2
2010-01 2
2010-02 2
(所影响的行数为 15 行)
*/
drop table tb , #
select convert(varchar(7),起始日期,120) 年份 , count(*) 会员总数 from tb group by convert(varchar(7),起始日期,120)
select convert(varchar(7),结束日期,120) 年份 , count(*) 会员总数 from tb group by convert(varchar(7),结束日期,120)