按周统计数据
有一数据
rq shl chb je
2007-07-1093821895.0300000040331.50000000
2007-07-113936127969.50000000245982.00000000
2007-07-122459593741.82000000199354.50000000
2007-07-1312385192543.38000000380977.00000000
2007-07-14954755.000000008965.00000000
2007-07-151467982921.71600000167731.00000000
2007-07-1610806308124.41500000636300.50000000
2007-07-17503484234.81500000167930.00000000
2007-07-1813243231705.83300000486411.00000000
2007-07-1924995121614.97000000251994.50000000
2007-07-2017809159689.00000000454706.50000000
2007-07-21251443275.00000000141176.00000000
2007-07-22739938729.7000000084467.00000000
2007-07-2310476372.5600000011172.00000000
2007-07-24109360559.00000000109335.00000000
2007-07-25299766107.55000000126557.50000000
2007-07-26157923023.0700000045876.00000000
2007-07-2735303196670.88200000412040.50000000
2007-07-2964310149.4900000018261.00000000
2007-07-30908755355.14000000116558.50000000
2007-07-311639049070.79000000111100.00000000
求SQL语句，根据输入开始、结束日期得出结果
输入2007-7-10，2007-7-27 得到如下结果
2007-7-10 ～ 2007-7-15 56628523826.4461043341
2007-7-16 ～ 2007-7-22 81800987373.7332222985.5
2007-7-23 ～ 2007-7-27 42019352733.062704981
按周一到周日的方式给出结果
[解决办法]
select datepart(week,rq),sum(...) from 表 group by datepart(week,rq)
[解决办法]
select (case when datepart(week,min(rq))=datepart(week, '2007-7-10 ') then '2007-7-10 ' else convert(varchar(10),DATEADD(wk,DATEDIFF(wk,0,min(rq)),0),120) end)+ ' ～ '+
(case when datepart(week,min(rq))=datepart(week, '2007-7-27 ') then '2007-7-27 ' else convert(varchar(10),DATEADD(wk,DATEDIFF(wk,0,min(rq)),6),120) end)
,sum(shl),sum(chb),sum(je)
from #表 where rq between '2007-7-10 ' and '2007-7-27 '
group by datepart(Week,dateadd(d,-1,rq))
-------------最好将日期参数格式一致
2007-7-10 ～ 2007-07-1556628523826.44601043341.0000
2007-07-16 ～ 2007-07-2281800987373.73302222985.5000
2007-07-23 ～ 2007-7-2742019352733.0620704981.0000
[解决办法]
或者先按给定时间段得出所有周，再join 子陌红尘的结果,效率会提升.
[解决办法]
参数格式一致-----如果给的参数就是2007-07-10形式的就不用象下面要进行转换了
then '2007-7-10 ' 改为 convert(varchar(10), '2007-7-10 ',120),另个一个类似
语句---大家帮忙，我也想个简单的
[解决办法]
declare @Test table(rq datetime, shl int, chb money, je money)
insert @Test
select '2007-07-10 ',938,21895.03000000,40331.50000000 union all
select '2007-07-11 ',3936,127969.50000000,245982.00000000 union all
select '2007-07-12 ',24595,93741.82000000,199354.50000000 union all
select '2007-07-13 ',12385,192543.38000000,380977.00000000 union all
select '2007-07-14 ',95,4755.00000000,8965.00000000 union all
select '2007-07-15 ',14679,82921.71600000,167731.00000000 union all
select '2007-07-16 ',10806,308124.41500000,636300.50000000 union all
select '2007-07-17 ',5034,84234.81500000,167930.00000000 union all
select '2007-07-18 ',13243,231705.83300000,486411.00000000 union all
select '2007-07-19 ',24995,121614.97000000,251994.50000000 union all
select '2007-07-20 ',17809,159689.00000000,454706.50000000 union all
select '2007-07-21 ',2514,43275.00000000,141176.00000000 union all
select '2007-07-22 ',7399,38729.70000000,84467.00000000 union all
select '2007-07-23 ',1047,6372.56000000,11172.00000000 union all
select '2007-07-24 ',1093,60559.00000000,109335.00000000 union all
select '2007-07-25 ',2997,66107.55000000,126557.50000000 union all
select '2007-07-26 ',1579,23023.07000000,45876.00000000 union all
select '2007-07-27 ',35303,196670.88200000,412040.50000000 union all
select '2007-07-29 ',643,10149.49000000,18261.00000000 union all
select '2007-07-30 ',9087,55355.14000000,116558.50000000 union all
select '2007-07-31 ',16390,49070.79000000,111100.00000000
/*
求SQL语句，根据输入开始、结束日期得出结果
输入2007-7-10，2007-7-27 得到如下结果
2007-7-10 ～ 2007-7-15 56628 523826.446 1043341
2007-7-16 ～ 2007-7-22 81800 987373.733 2222985.5
2007-7-23 ～ 2007-7-27 42019 352733.062 704981
按周一到周日的方式给出结果
*/
--Limpire：按周汇总不难，难在日期的处理——不是一般的难。
declare @t1 varchar(10),@t2 varchar(10)
select @t1= '2007-07-10 ',@t2= '2007-07-27 '
select
日期范围=
convert(varchar(10),(case Weeks when 0 then @t1 else dateadd(day,7*Weeks-datediff(day,0,@t1)%7,@t1) end),120)+ ' ～ '+
convert(varchar(10),(case when dateadd(day,7*(Weeks+1)-datediff(day,0,@t1)%7-1,@t1)> @t2 then @t2 else dateadd(day,7*(Weeks+1)-datediff(day,0,@t1)%7-1,@t1) end),120),
[sum(shl)]=shl,
[sum(chb)]=chb,
[sum(je)]=je
from
(
select
weeks=(datediff(day,0,rq)/7)-datediff(day,0,@t1)/7,
shl=sum(shl),
chb=sum(chb),
je=sum(je)
from @Test
where rq between @t1 and @t2
group by datediff(day,0,rq)/7-datediff(day,0,@t1)/7
) a
/*
日期范围 sum(shl) sum(chb) sum(je)
------------------------ ----------- --------------------- ---------------------
2007-07-10 ～ 2007-07-15 56628 523826.4460 1043341.0000
2007-07-16 ～ 2007-07-22 81800 987373.7330 2222985.5000
2007-07-23 ～ 2007-07-27 42019 352733.0620 704981.0000
（所影响的行数为 3 行）
*/
[解决办法]
select (case when 周=datediff(Week,0,dateadd(d,-1, '2007-7-10 ')) then '2007-7-10 ' else convert(varchar(10),dateadd(wk,周,0),120) end)+ ' ～ '+
(case when 周=datediff(Week,0,dateadd(d,-1, '2007-7-27 ')) then '2007-7-27 ' else convert(varchar(10),dateadd(wk,周,6),120) end)
,和1,和2,和3
from(
select datediff(Week,0,dateadd(d,-1,rq)) 周,sum(shl)和1,sum(chb)和2,sum(je)和3
from #表 where rq between '2007-7-10 ' and '2007-7-27 '
group by datediff(Week,0,dateadd(d,-1,rq))
)a--效率应该提升了
和1 和2 和3
------------------------ ----------- --------------------- ---------------------
2007-7-10 ～ 2007-07-15 56628 523826.4460 1043341.0000
2007-07-16 ～ 2007-07-22 81800 987373.7330 2222985.5000
2007-07-23 ～ 2007-7-27 42019 352733.0620 704981.0000
（所影响的行数为 3 行）
