按周分组
select datepart(year,列名) 年,datepart(week,列名) 周,count(*) from 表 group by datepart(year,列名),datepart(week,列名)
select datepart(year,列名) 年,datepart(week,列名) 周,count(*) from 表 group by datepart(year,列名),datepart(week,列名)
declare @r int
declare @dt datetime
set @dt = '2003-5-1'
select @dt day, datepart(dw, @dt) week, 1 row into #t where 1 = 2
set @m = datepart(mm, @dt)
set @r = 1
while @m = datepart(mm, @dt)
begin
insert #t values (@dt, datepart(dw, @dt), @r)
set @dt = dateadd(day, 1, @dt)
if datepart(dw, @dt) = 1
set @r = @r + 1
end
select * from #tselect
(select datepart(day, [day]) from #t where row = A.row and [week] = 1),
(select datepart(day, [day]) from #t where row = A.row and [week] = 2),
(select datepart(day, [day]) from #t where row = A.row and [week] = 3),
(select datepart(day, [day]) from #t where row = A.row and [week] = 4),
(select datepart(day, [day]) from #t where row = A.row and [week] = 5),
(select datepart(day, [day]) from #t where row = A.row and [week] = 6),
(select datepart(day, [day]) from #t where row = A.row and [week] = 7)
from #t A group by row
----------- ----------- ----------- ----------- ----------- ----------- -----------
NULL NULL NULL NULL 1 2 3
4 5 6 7 8 9 10
11 12 13 14 15 16 17
18 19 20 21 22 23 24
25 26 27 28 29 30 31(5 row(s) affected)
Select Count(*),Min(DateField),Max(DateField)
from T
Group by datediff(week,DateField,getdate())
order by datediff(week,DateField,getdate())2.
declare @ datetime
set @ = '2003-6-8'
select dateadd(day,-day(@),@) + F
from
(
select 1 as F
union all
select 2
union all
select 3
union all
select 4
union all
select 4
union all
select 6
union all
select 7
union all
select 8
union all
select 9
union all
select 10
union all
select 11
union all
select 12
union all
select 13
union all
select 14
union all
select 15
union all
select 16
union all
select 17
union all
select 18
union all
select 19
union all
select 20
union all
select 21
union all
select 22
union all
select 23
union all
select 24
union all
select 25
union all
select 26
union all
select 27
union all
select 28
union all
select 29
union all
select 30
union all
select 31
) T
where datediff(month,@,dateadd(day,-day(@),@ ) + F) =0
declare @ datetime
set @ = '2003-6-8'
select dateadd(day,-day(@),@) + F as [Date]
from
(
select 1 as F
union all
select 2
union all
select 3
union all
select 4
union all
select 5
union all
select 6
union all
select 7
union all
select 8
union all
select 9
union all
select 10
union all
select 11
union all
select 12
union all
select 13
union all
select 14
union all
select 15
union all
select 16
union all
select 17
union all
select 18
union all
select 19
union all
select 20
union all
select 21
union all
select 22
union all
select 23
union all
select 24
union all
select 25
union all
select 26
union all
select 27
union all
select 28
union all
select 29
union all
select 30
union all
select 31
) T
where datediff(month,@,dateadd(day,-day(@),@ ) + F) =0
order by [date]
Select Count(*),Min(DateField),Max(DateField)
from T
where datediff(month,DateField,getdate()) =0
Group by datediff(week,DateField,getdate())
order by datediff(week,DateField,getdate())
--getdate() 可以替换为 "某月份"的任意一天