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 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.000 2012-03-10 00:00:00.000
11 2012-03-11 00:00:00.000 2012-03-17 00:00:00.000
12 2012-03-18 00:00:00.000 2012-03-24 00:00:00.000
13 2012-03-25 00:00:00.000 2012-03-31 00:00:00.000
14 2012-04-01 00:00:00.000 2012-04-07 00:00:00.000
15 2012-04-08 00:00:00.000 2012-04-14 00:00:00.000
16 2012-04-15 00:00:00.000 2012-04-21 00:00:00.000
17 2012-04-22 00:00:00.000 2012-04-28 00:00:00.000
18 2012-04-29 00:00:00.000 2012-05-05 00:00:00.000
19 2012-05-06 00:00:00.000 2012-05-12 00:00:00.000
20 2012-05-13 00:00:00.000 2012-05-19 00:00:00.000
21 2012-05-20 00:00:00.000 2012-05-26 00:00:00.000
22 2012-05-27 00:00:00.000 2012-06-02 00:00:00.000
23 2012-06-03 00:00:00.000 2012-06-09 00:00:00.000
24 2012-06-10 00:00:00.000 2012-06-16 00:00:00.000
25 2012-06-17 00:00:00.000 2012-06-23 00:00:00.000
26 2012-06-24 00:00:00.000 2012-06-30 00:00:00.000
27 2012-07-01 00:00:00.000 2012-07-07 00:00:00.000
28 2012-07-08 00:00:00.000 2012-07-14 00:00:00.000
29 2012-07-15 00:00:00.000 2012-07-21 00:00:00.000
30 2012-07-22 00:00:00.000 2012-07-28 00:00:00.000
31 2012-07-29 00:00:00.000 2012-08-04 00:00:00.000
32 2012-08-05 00:00:00.000 2012-08-11 00:00:00.000
33 2012-08-12 00:00:00.000 2012-08-18 00:00:00.000
34 2012-08-19 00:00:00.000 2012-08-25 00:00:00.000
35 2012-08-26 00:00:00.000 2012-09-01 00:00:00.000
36 2012-09-02 00:00:00.000 2012-09-08 00:00:00.000
37 2012-09-09 00:00:00.000 2012-09-15 00:00:00.000
38 2012-09-16 00:00:00.000 2012-09-22 00:00:00.000
39 2012-09-23 00:00:00.000 2012-09-29 00:00:00.000
40 2012-09-30 00:00:00.000 2012-10-06 00:00:00.000
41 2012-10-07 00:00:00.000 2012-10-13 00:00:00.000
42 2012-10-14 00:00:00.000 2012-10-20 00:00:00.000
43 2012-10-21 00:00:00.000 2012-10-27 00:00:00.000
44 2012-10-28 00:00:00.000 2012-11-03 00:00:00.000
45 2012-11-04 00:00:00.000 2012-11-10 00:00:00.000
46 2012-11-11 00:00:00.000 2012-11-17 00:00:00.000
47 2012-11-18 00:00:00.000 2012-11-24 00:00:00.000
48 2012-11-25 00:00:00.000 2012-12-01 00:00:00.000
49 2012-12-02 00:00:00.000 2012-12-08 00:00:00.000
50 2012-12-09 00:00:00.000 2012-12-15 00:00:00.000
51 2012-12-16 00:00:00.000 2012-12-22 00:00:00.000
52 2012-12-23 00:00:00.000 2012-12-29 00:00:00.000
53 2012-12-30 00:00:00.000 2012-12-31 00:00:00.000
(53 行受影响)
*/
