declare @startDate datetime,@endDate datetime select @startDate='2005-01-01', @endDate='2005-08-01'--查询 select [总数量]=sum([数量]), [周数]='第'+ convert(varchar,datepart(week,日期字段)-datepart(week,@startDate)+1)+ '周' from 表 where 日期字段 between @startDate and @endDate group by datepart(week,日期字段)
declare @tb table ( Client_1RecType varchar(10), Client_1RecTime varchar(10) ) insert @tb select '来访','2005-08-11' union select '来访','2005-08-12' union select '来访','2005-08-15' union select '来访','2005-08-15' union select '来电','2005-08-16' union select '来访','2005-08-17' union select '来访','2005-08-17' union select '来电','2005-08-17' union select '来电','2005-08-17' union select '来电','2005-08-17' union select '来访','2005-08-18' union select '来访','2005-08-18' union select '来访','2005-08-18' union select '来电','2005-08-18' union select '来访','2005-08-18' union select '来访','2005-08-18' union select '来电','2005-08-18' union select '来电','2005-08-19' union select '来电','2005-08-20' union select '来电','2005-08-20' declare @startDate datetime,@endDate datetime select @startDate='2005-08-11', @endDate='2005-08-20'--查询 select [总数量], [周数]= '第'+convert(varchar,[周数]-datepart(week,@startDate)+1)+'周' from ( select [总数量]=count(1), [周数]=datepart(week,Client_1RecTime) from @tb where Client_1RecTime between @startDate and @endDate group by datepart(week,Client_1RecTime) )t--/*总数量 周数 ----------- ---------------------------------- 2 第1周 8 第2周(2 row(s) affected) */
select Count(统计项目) as 统计数量,mf_function(日期型字段) as 统计周数 from 统计表 group by mf_function(日期型字段) order by mf_function(日期型字段)
select 统计数量 = Count(统计项目), 统计周数 = '第'+rtrim(Datepart(wk,日期型字段))+'周' from 统计表 group by Datepart(wk,日期型字段) order by Datepart(wk,日期型字段)
try:declare @startDate datetime,@endDate datetime select @startDate='2005-08-11', @endDate='2005-08-20'declare @weeks table([周数] int) declare @tmp datetime set @tmp=@startDate while datepart(week,@tmp)<=datepart(week,@endDate) begin insert @weeks select datepart(week,@tmp) set @tmp=dateadd(day,7,@tmp) end--查询 select [总数量]=isnull(T.[总数量],0), [周数]= '第'+convert(varchar,W.[周数]-datepart(week,@startDate)+1)+'周' from @weeks W left join ( select [总数量]=count(1), [周数]=datepart(week,Client_1RecTime) from @tb where Client_1RecTime between @startDate and @endDate group by datepart(week,Client_1RecTime) )T on W.[周数]=T.[周数]
declare @tb table ( Client_1RecType varchar(10), Client_1RecTime varchar(10) ) insert @tb select '来访','2005-08-11' union all select '来访','2005-08-12' union all select '来访','2005-08-15' union all select '来访','2005-08-15' union all select '来电','2005-08-16' union all select '来访','2005-08-17' union all select '来访','2005-08-17' union all select '来电','2005-08-17' union all select '来电','2005-08-17' union all select '来电','2005-08-17' union all select '来访','2005-08-18' union all select '来访','2005-08-18' union all select '来访','2005-08-18' union all select '来电','2005-08-18' union all select '来访','2005-08-18' union all select '来访','2005-08-18' union all select '来电','2005-08-18' union all select '来电','2005-08-19' union all select '来电','2005-08-28' union all select '来电','2005-08-28' union all select '来电','2005-08-29' union all select '来电','2005-08-29' union all select '来电','2005-08-29' union all select '来电','2005-08-29' declare @startDate datetime,@endDate datetime select @startDate='2005-08-11', @endDate='2005-08-29'declare @weeks table([周数] int) declare @tmp datetime set @tmp=@startDate while datepart(week,@tmp)<=datepart(week,@endDate) begin insert @weeks select datepart(week,@tmp) set @tmp=dateadd(day,7,@tmp) end--查询 select [总数量]=isnull(T.[总数量],0), [周数]= '第'+convert(varchar,W.[周数]-datepart(week,@startDate)+1)+'周' from @weeks W left join ( select [总数量]=count(1), [周数]=datepart(week,Client_1RecTime) from @tb where Client_1RecTime between @startDate and @endDate group by datepart(week,Client_1RecTime) )T on W.[周数]=T.[周数] --结果 /* 总数量 周数 ----------- ---------------------------------- 2.00 第1周 16.00 第2周 .00 第3周 6.00 第4周(所影响的行数为 4 行) */
没注意到这贴,但有个很奇怪的问题,就是“统计周数不是从第1周起始,而是从第2周起始”
Client_1RecType Client_1RecTime
来访 2005-08-11
来访 2005-08-12
来访 2005-08-15
来访 2005-08-15
来电 2005-08-16
来访 2005-08-17
来访 2005-08-17
来电 2005-08-17
来电 2005-08-17
来电 2005-08-17
来访 2005-08-18
来访 2005-08-18
来访 2005-08-18
来电 2005-08-18
来访 2005-08-18
来访 2005-08-18
来电 2005-08-18
来电 2005-08-19
来电 2005-08-20
来电 2005-08-20
测试SQL语句:
Select Count(Client_1RecType) AS 统计数量,DatePart(dw,Client_1RecTime) AS 统计周数 from TbClient Group By DatePart(dw,Client_1RecTime)测试结果:
统计数量 统计周数
2 2
1 3
5 4
8 5
2 6
2 7从数据看应该是只跨2周,但执行结果为6周
select @startDate='2005-01-01',
@endDate='2005-08-01'--查询
select
[总数量]=sum([数量]),
[周数]='第'+
convert(varchar,datepart(week,日期字段)-datepart(week,@startDate)+1)+
'周'
from 表
where 日期字段 between @startDate and @endDate
group by datepart(week,日期字段)
(
Client_1RecType varchar(10),
Client_1RecTime varchar(10)
)
insert @tb
select '来访','2005-08-11' union
select '来访','2005-08-12' union
select '来访','2005-08-15' union
select '来访','2005-08-15' union
select '来电','2005-08-16' union
select '来访','2005-08-17' union
select '来访','2005-08-17' union
select '来电','2005-08-17' union
select '来电','2005-08-17' union
select '来电','2005-08-17' union
select '来访','2005-08-18' union
select '来访','2005-08-18' union
select '来访','2005-08-18' union
select '来电','2005-08-18' union
select '来访','2005-08-18' union
select '来访','2005-08-18' union
select '来电','2005-08-18' union
select '来电','2005-08-19' union
select '来电','2005-08-20' union
select '来电','2005-08-20' declare @startDate datetime,@endDate datetime
select @startDate='2005-08-11',
@endDate='2005-08-20'--查询
select [总数量],
[周数]= '第'+convert(varchar,[周数]-datepart(week,@startDate)+1)+'周'
from
(
select
[总数量]=count(1),
[周数]=datepart(week,Client_1RecTime)
from @tb
where Client_1RecTime between @startDate and @endDate
group by datepart(week,Client_1RecTime)
)t--/*总数量 周数
----------- ----------------------------------
2 第1周
8 第2周(2 row(s) affected)
*/
统计数量 = Count(统计项目),
统计周数 = '第'+rtrim(Datepart(wk,日期型字段))+'周'
from
统计表
group by
Datepart(wk,日期型字段)
order by
Datepart(wk,日期型字段)
还有个问题:
如何让周数不间断,数据示例:
总数量 周数
----------- ----------------------------------
2 第1周
8 第2周
0 第3周 (假设日期字段中没有该周的数据,希望能将该周的总数量置为0)
----------------------------------------------
这样就能保证整个数据的连续性,因为这些数据的组织是为了形成最终的走势图
select @startDate='2005-08-11',
@endDate='2005-08-20'declare @weeks table([周数] int)
declare @tmp datetime
set @tmp=@startDate
while datepart(week,@tmp)<=datepart(week,@endDate)
begin
insert @weeks select datepart(week,@tmp)
set @tmp=dateadd(day,7,@tmp)
end--查询
select [总数量]=isnull(T.[总数量],0),
[周数]= '第'+convert(varchar,W.[周数]-datepart(week,@startDate)+1)+'周'
from @weeks W
left join
(
select
[总数量]=count(1),
[周数]=datepart(week,Client_1RecTime)
from @tb
where Client_1RecTime between @startDate and @endDate
group by datepart(week,Client_1RecTime)
)T on W.[周数]=T.[周数]
(
Client_1RecType varchar(10),
Client_1RecTime varchar(10)
)
insert @tb
select '来访','2005-08-11' union all
select '来访','2005-08-12' union all
select '来访','2005-08-15' union all
select '来访','2005-08-15' union all
select '来电','2005-08-16' union all
select '来访','2005-08-17' union all
select '来访','2005-08-17' union all
select '来电','2005-08-17' union all
select '来电','2005-08-17' union all
select '来电','2005-08-17' union all
select '来访','2005-08-18' union all
select '来访','2005-08-18' union all
select '来访','2005-08-18' union all
select '来电','2005-08-18' union all
select '来访','2005-08-18' union all
select '来访','2005-08-18' union all
select '来电','2005-08-18' union all
select '来电','2005-08-19' union all
select '来电','2005-08-28' union all
select '来电','2005-08-28' union all
select '来电','2005-08-29' union all
select '来电','2005-08-29' union all
select '来电','2005-08-29' union all
select '来电','2005-08-29' declare @startDate datetime,@endDate datetime
select @startDate='2005-08-11',
@endDate='2005-08-29'declare @weeks table([周数] int)
declare @tmp datetime
set @tmp=@startDate
while datepart(week,@tmp)<=datepart(week,@endDate)
begin
insert @weeks select datepart(week,@tmp)
set @tmp=dateadd(day,7,@tmp)
end--查询
select [总数量]=isnull(T.[总数量],0),
[周数]= '第'+convert(varchar,W.[周数]-datepart(week,@startDate)+1)+'周'
from @weeks W
left join
(
select
[总数量]=count(1),
[周数]=datepart(week,Client_1RecTime)
from @tb
where Client_1RecTime between @startDate and @endDate
group by datepart(week,Client_1RecTime)
)T on W.[周数]=T.[周数]
--结果
/*
总数量 周数
----------- ----------------------------------
2.00 第1周
16.00 第2周
.00 第3周
6.00 第4周(所影响的行数为 4 行)
*/
急的时候脑子总容易乱,呵呵
我先试试