求sql 计算全年每个月的会员总数

[maggie_ma900]

数据表结构：会员号，起始日期 结束日期
1111 2008-12-1 2009-1-31
2222 2009-2-1 2010-2-21
3333 2009-3-1 2010-2-21
4444 2009-4-1 2009-4-31


求全年每个月的会员总数
例如如上计算结果 年份 会员总数
2009-1 4
2009-2 3

谢谢

[YUANHUILONG]

8樓對

[maggie_ma900]

8楼做个临时表 select top 8000 identity(int,0,1) as id into # from syscolumns a,syscolumns b
是为什么？ 只用一个表就可以了

[maggie_ma900]

我再把这个问题说一下吧：
表结构 ：会员号 开始日期 结束日期
1111 2007-1-5 2009-1-20
2222 2008-5-1 2009-2-16
3333 2009-1-1 2009-5-4
求 2009 年所有月份的会员总数

结果要求 2009-1 3
2009-2 2

[dawugui]

按哪个日期算?

起始日期 结束日期

[dawugui]

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) 

[dawugui]

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 , # 

[肥龙上天]

楼主要的是不是这个
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)

[maggie_ma900]

是结束日期
例如：1111,2222,3333,4444 的结束日期在2009年1月都在会 所以 2009-1 会员总数是 4
2009年2月 应该有的会员总是为：2222,3333,4444 所以会员总数是3

[dawugui]

[Quote=引用 5 楼 maggie_ma900 的回复:]
是结束日期
例如：1111,2222,3333,4444 的结束日期在2009年1月都在会 所以 2009-1 会员总数是 4
2009年2月 应该有的会员总是为：2222,3333,4444 所以会员总数是3
[/Quote]
2222 2009-2-1 2010-2-21
3333 2009-3-1 2010-2-21
4444 2009-4-1 2009-4-31

你2222,3333,4444都是2月份后才开始的,1月份也算进去?牵强了吧?
另2009-4-31数据错误,4月没有31号.

[maggie_ma900]

哦 我写错了 应该是
2222 2009-1-1 2010-2-21
3333 2009-1-1 2010-2-21
4444 2009-1-1 2009-4-21

[dawugui]

[Quote=引用 7 楼 maggie_ma900 的回复:]
哦 我写错了 应该是
2222 2009-1-1 2010-2-21
3333 2009-1-1 2010-2-21
4444 2009-1-1 2009-4-21
[/Quote]

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 , # 

[zzxap]

select convert(varchar(7),起始日期,120) 年份 , count(*) 会员总数 from tb group by convert(varchar(7),起始日期,120)

[maggie_ma900]

楼上写的有点不明白#n 是什么表 ，dateadd(day , n.id , 起始日期)代表什么意思？

[FNO1468]

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

[maggie_ma900]

不用临时表 怎么写？

[shanshuang0818]

支持 爱新觉罗.毓华

[JeffreyZhang1314]

过路的看看！