■■■■■■高分求每月每天的求和统计SQL■■■■■■

[keithann]

■表结构：
name CallCount CallTime
------ ----------- -----------
a 13 2005-02-10 15:08:11.000
a 5 2005-02-12 15:08:13.000
a 6 2005-03-01 15:09:08.000
a 21 2005-03-18 01:09:09.000
a 7 2005-03-18 15:09:11.000

■要求：将name=a按每月每天进行CallCount求和统计，表中不存在的日期sumcount的值为0，
格式如下：
SumCount day
----------- ----------
0 2005-02-01
0 2005-02-02
……
……
13 2005-02-10
5 2005-02-12
……
……
0 2005-02-28
6 2005-03-01
……
……
28 2005-03-18
……
……
0 2005-03-30
0 2005-03-31
■这样的SQL语句该怎么写？每月的天数又不固定，2月还有28、29天。急死人了！

[zjcxc]

--如果每个月都要是足月从1号到月底,则改为:

--■附测试脚本：
CREATE TABLE [TABLE1] (
[name] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[CallCount] [int] NULL ,
[CallTime] [datetime] NULL CONSTRAINT [DF_TABLE1_CallTime] DEFAULT (getdate())
) ON [PRIMARY]
GO

Insert into TABLE1 (name,CallCount,CallTime) values ('a',13,'2005-2-10 15:08:11')
Insert into TABLE1 (name,CallCount,CallTime) values ('a',5,'2005-2-12 15:08:13')
Insert into TABLE1 (name,CallCount,CallTime) values ('a',6,'2005-3-1 15:09:08')
Insert into TABLE1 (name,CallCount,CallTime) values ('a',21,'2005-3-18 1:09:09')
Insert into TABLE1 (name,CallCount,CallTime) values ('a',7,'2005-3-18 15:09:11')
go

--■取每天记录：
declare @i int,@dt datetime
select @dt=convert(char(8),min(CallTime),120)+'1'
,@i=datediff(day,@dt,convert(char(8),dateadd(month,1,max(CallTime)),120)+'1')
from table1
where name='a'

if @@rowcount=0 return
set rowcount @i
select id=identity(int,0,1) into #t from syscolumns a,syscolumns b
set rowcount 0

select SumCount=isnull(a.SumCount,0),day=convert(varchar(10),@dt+b.id,120)
from(
select Sum(CallCount) SumCount,convert(varchar(10),CallTime,120) day
from table1
where name='a'
group by convert(varchar(10),CallTime,120)
)a right join #t b on datediff(day,@dt,a.day)=b.id
order by b.id
drop table #t
go

drop table TABLE1

[子陌红尘]

set rowcount 1000
go

select identity(int,1,1) as id into #t from sysobjects a,sysobjects b

set rowcount 0
go

select
[day] = a.ndate,
SumCount = isnull(sum(CallCount),0)
from
(select dateadd(day,id,cast('2004-12-31' as datetime)) ndate from #t) a
left join
table1 b
on
datediff(day,a.ndate,b.CallTime) = 0 and b.name = 'a'
where
a.ndate between '2005-02-01' and '2005-03-31'
group by
a.ndate
order by
a.ndate

[xluzhong]

--■附测试脚本：
CREATE TABLE [TABLE1] (
[name] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[CallCount] [int] NULL ,
[CallTime] [datetime] NULL CONSTRAINT [DF_TABLE1_CallTime] DEFAULT (getdate())
) ON [PRIMARY]
GO

Insert into TABLE1 (name,CallCount,CallTime) values ('a',13,'2005-2-10 15:08:11')
Insert into TABLE1 (name,CallCount,CallTime) values ('a',5,'2005-2-12 15:08:13')
Insert into TABLE1 (name,CallCount,CallTime) values ('a',6,'2005-3-1 15:09:08')
Insert into TABLE1 (name,CallCount,CallTime) values ('a',21,'2005-3-18 1:09:09')
Insert into TABLE1 (name,CallCount,CallTime) values ('a',7,'2005-3-18 15:09:11')
go

--■取每天记录：
declare @i int,@dt datetime
select @dt=min(CallTime),@i=datediff(day,@dt,max(CallTime))+1
from table1
where name='a'

if @@rowcount=0 return
set rowcount @i
select id=identity(int,0,1) into #t from syscolumns a,syscolumns b
set rowcount 0

select SumCount=isnull(a.SumCount,0),day=convert(varchar(10),@dt+b.id,120)
from(
select Sum(CallCount) SumCount,convert(varchar(10),CallTime,120) day
from table1
where name='a'
group by convert(varchar(10),CallTime,120)
)a right join #t b on datediff(day,@dt,a.day)=b.id
order by b.id
drop table #t
go

drop table TABLE1

[jiang130]

set @begindate='2005-03-01'
set @enddate='2005-03-31'
create table #1(ddate datetime)
while @begindate<=@enddate
begin
insert #1 values @begindate
set @begindate=@begindate+1
end

select name,sum(isnull(callcount,0)),ddate from #1 a left join 表 on day(day)=ddate group by name,ddate

[jiang130]

set @begindate='2005-03-01'
set @enddate='2005-03-31'
create table #1(ddate datetime)
while @begindate<=@enddate
begin
insert #1 values @begindate
set @begindate=@begindate+1
end

select name,sum(isnull(callcount,0)),ddate from #1 a left join 表 on day(calltime)=ddate group by name,ddate

[jiang130]

set @begindate='2005-03-01'
set @enddate='2005-03-31'
create table #1(ddate datetime)
while @begindate<=@enddate
begin
insert #1 values @begindate
set @begindate=@begindate+1
end

select name,sum(isnull(callcount,0)),ddate from #1 a left join 表 on day(calltime)=day(ddate) and month(calltime)=month(ddate) and year(calltime)=year(ddate) group by name,ddate

[dazhu2]

study

[cywarson]

mark..

[jiang130]

問題還沒解決嗎?

[yown]

建表一：获取某一时间内的所有日期

再与表关联　分组即可

[yown]

如表 tbl_date

d
-----------
2004-01-01
2004-01-02
...
2005-04-01

再关联

select a.d day,sum(b.Callcount) Sumcount from tbl_date a

left join 你的表 b on datediff(d,a.d,b.Calltime)=0
group by a.d

[wjlsmail]

Study

[ken2002]

收藏了

[wei123456]

高人真多啊.........

[clarkchiang]

学习学习中...................收益非浅

[keithann]

怎么都是生成临时表的，运行起来感觉很慢。有其它方法吗？

[zjcxc]

不生成临时表怎么补齐天数?

用我的方法生成临时表很快的,都不用1秒

[zjcxc]

如果嫌临时表不好,你可以查出数据,在前台显示的时候补齐缺少的日期.

[hipop]

学习！

[liuyxit]

MARK