sql 统计分组解决思路
日期：2014-05-17 浏览次数：20599 次
sql 统计分组
想请教一下各位，怎么显示下面的格式。望指点？
declare @startdate datetime,@enddate datetime
set @startdate='2012-11-01'
set @enddate='2012-11-21'
select convert(varchar(10),dateadd(day,number,@startdate),120) from master..spt_values where datediff(day,dateadd(day,number,@startdate), @enddate)>=0 and number>=0 and type='p'
按周显示
/*----------
2012-11-04 至 2012-11-10
2012-11-11 至 2012-11-17
。。。。
按月显示
/*----------
declare @startdate datetime,@enddate datetime
set @startdate='2011-11'
set @enddate='2012-11'
/*----------
2011-11
2011-12
2012-01
2012-02
。。。。
------最佳解决方案--------------------
------其他解决方案--------------------
想请教一下各位，怎么显示下面的格式。望指点？
declare @startdate datetime,@enddate datetime
set @startdate='2012-11-01'
set @enddate='2012-11-21'
select convert(varchar(10),dateadd(day,number,@startdate),120) from master..spt_values where datediff(day,dateadd(day,number,@startdate), @enddate)>=0 and number>=0 and type='p'
按周显示
/*----------
2012-11-04 至 2012-11-10
2012-11-11 至 2012-11-17
。。。。
按月显示
/*----------
declare @startdate datetime,@enddate datetime
set @startdate='2011-11'
set @enddate='2012-11'
/*----------
2011-11
2011-12
2012-01
2012-02
。。。。
------最佳解决方案--------------------
declare @startdate datetime,@enddate datetime
set @startdate='2012-11-01'
set @enddate='2012-11-21'
SELECT ltrim(MIN(x))+'至'+LTRIM(MAX(x))
FROM
(
SELECT convert(varchar(10),dateadd(day,number,@startdate),120) x from master..spt_values where datediff(day,dateadd(day,number,@startdate), @enddate)>=0 and number>=0 and type='p'
)aa
GROUP BY DATEPART(week,x)
--result
/*
2012-11-01至2012-11-03
2012-11-04至2012-11-10
2012-11-11至2012-11-17
2012-11-18至2012-11-21
（所影响的行数为 4 行）
*/
------其他解决方案--------------------
with t (dt,wk)
as
(
select
dateadd(dd,number,'2012-01-01'),
DATEPART(WK,dateadd(dd,number,'2012-01-01'))
from
master..spt_values
where
number between 0 and 365
and type='p'
)
select
wk,
min(dt) as StartDate,
max(dt) as EndDate
from
t
group by
wk
/*
wk StartDate EndDate
----------- ----------------------- -----------------------
1 2012-01-01 00:00:00.000 2012-01-07 00:00:00.000
2 2012-01-08 00:00:00.000 2012-01-14 00:00:00.000
3 2012-01-15 00:00:00.000 2012-01-21 00:00:00.000
4 2012-01-22 00:00:00.000 2012-01-28 00:00:00.000
5 2012-01-29 00:00:00.000 2012-02-04 00:00:00.000
6 2012-02-05 00:00:00.000 2012-02-11 00:00:00.000
7 2012-02-12 00:00:00.000 2012-02-18 00:00:00.000
8 2012-02-19 00:00:00.000 2012-02-25 00:00:00.000
9 2012-02-26 00:00:00.000 2012-03-03 00:00:00.000
10 2012-03-04 00:00:00.
免责声明： 本文仅代表作者个人观点，与爱易网无关。其原创性以及文中陈述文字和内容未经本站证实，对本文以及其中全部或者部分内容、文字的真实性、完整性、及时性本站不作任何保证或承诺，请读者仅作参考，并请自行核实相关内容。
