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='2006-1-7'declare @weeks table([周数] int)
declare @tmp datetime,@i int
select @tmp=@startDate,@i=1
insert @weeks select datepart(week,@startDate)
while dateadd(day,7,@tmp)<=@endDate
begin
insert @weeks select datepart(week,@startDate)+@i
select @tmp=dateadd(day,7,@tmp),@i=@i+1
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 第1周
16 第2周
0 第3周
6 第4周
0 第5周
0 第6周
0 第7周
0 第8周
0 第9周
0 第10周
0 第11周
0 第12周
0 第13周
0 第14周
0 第15周
0 第16周
0 第17周
0 第18周
0 第19周
0 第20周
0 第21周
0 第22周(22 row(s) affected)
*/
(
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='2006-1-7'declare @weeks table([周数] int)
declare @tmp datetime,@i int
select @tmp=@startDate,@i=1
insert @weeks select datepart(week,@startDate)
while dateadd(day,7,@tmp)<=@endDate
begin
insert @weeks select datepart(week,@startDate)+@i
select @tmp=dateadd(day,7,@tmp),@i=@i+1
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 第1周
16 第2周
0 第3周
6 第4周
0 第5周
0 第6周
0 第7周
0 第8周
0 第9周
0 第10周
0 第11周
0 第12周
0 第13周
0 第14周
0 第15周
0 第16周
0 第17周
0 第18周
0 第19周
0 第20周
0 第21周
0 第22周(22 row(s) affected)
*/
解决方案 »
- SQL Server ON PRIMARY TEXTIMAGE_ON PRIMARY
- 菜鸟提问 vs2008中自带的SQL server 配置和使用啊?
- 这样 sq l怎么写呢?
- 急,这个查询如何实现?
- 关于SQL Server2000登录密码的困惑,在线等待
- 错误1503:服务没有及时响应启动,急呀!自己弄的个win服务,启动报这错了.
- 请问sql server 2000 企业版与个人版的区别
- 简单的sql问题
- 应该算是动态的问题吧。我也不能确定,看看再说喽
- 产品销售月报表(显示样式:产品名,1日销售额,...,31日销售额,总额)
- 实现局域网内两台机器中的相同SQL数据库中的一个表同步(使用代码)
- 请问大侠:我的SQLSERVER2000中的IMAGE、TEXT怎么者只能设为16位,不能改?!
declare @tb table
(
Client_1RecType varchar(10),
Client_1RecTime varchar(10)
)
insert @tb
select '来访','2005-01-01' union all
select '来访','2005-01-02' union all
select '来访','2005-01-08' union all
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' --select datepart(week,Client_1RecTime),* from @tbdeclare @startDate datetime,@endDate datetime
select @startDate='2005-01-01',
@endDate='2005-12-25'select
[周数]= '第'+ ltrim(str(min(datepart(week,a.Client_1RecTime)))) +'周',
[总数量]=(select count(Client_1RecType) from @tb b where datepart(week,b.Client_1RecTime) <= datepart(week,a.Client_1RecTime))
from @tb a
where a.Client_1RecTime between @startDate and @endDate
group by datepart(week,a.Client_1RecTime)/*结果
周数 总数量
-------------- -----------
第1周 1
第2周 3
第33周 5
第34周 21
第36周 27
*/--我奇怪的是2005-01-01是第二周?2004-01-01是第一周?晕