在一个表中记录的有profit和今天的日期date
请问怎么查询本月中各个周的总的profit,也就是每个月中四个周,每个周的总profit
week sum
1 2500
2 3200
3 2000
4 1800不知道明白否?
请问怎么查询本月中各个周的总的profit,也就是每个月中四个周,每个周的总profit
week sum
1 2500
2 3200
3 2000
4 1800不知道明白否?
调试欢乐多
insert @t select '2008-03-01',5
insert @t select '2008-03-02',45
insert @t select '2008-03-03',57
insert @t select '2008-03-04',6
insert @t select '2008-03-05',57
insert @t select '2008-03-06',59
insert @t select '2008-03-07',58
insert @t select '2008-03-08',5
insert @t select '2008-03-09',45
insert @t select '2008-03-10',34
insert @t select '2008-03-11',67
insert @t select '2008-03-12',23
insert @t select '2008-03-13',78
insert @t select '2008-03-14',45
insert @t select '2008-03-15',2
insert @t select '2008-03-16',45
insert @t select '2008-03-17',56
insert @t select '2008-03-18',12
insert @t select '2008-03-19',45
insert @t select '2008-03-20',78
insert @t select '2008-03-21',88
insert @t select '2008-03-22',54
insert @t select '2008-03-23',5
insert @t select '2008-03-24',2
insert @t select '2008-03-25',33
insert @t select '2008-03-26',44
insert @t select '2008-03-27',56
insert @t select '2008-03-28',77
insert @t select '2008-03-29',22
insert @t select '2008-03-30',44
insert @t select '2008-03-31',10select wk as 周数,sum(profit) as profit
from
(
select datepart(week,dt) as wk,*
from @t
) t
group by wk/*
周数 profit
----------- -----------
9 5
10 287
11 294
12 378
13 239
14 54(6 行受影响)
*/
insert @t select '2008-03-01',5
insert @t select '2008-03-02',45
insert @t select '2008-03-03',57
insert @t select '2008-03-04',6
insert @t select '2008-03-05',57
insert @t select '2008-03-06',59
insert @t select '2008-03-07',58
insert @t select '2008-03-08',5
insert @t select '2008-03-09',45
insert @t select '2008-03-10',34
insert @t select '2008-03-11',67
insert @t select '2008-03-12',23
insert @t select '2008-03-13',78
insert @t select '2008-03-14',45
insert @t select '2008-03-15',2
insert @t select '2008-03-16',45
insert @t select '2008-03-17',56
insert @t select '2008-03-18',12
insert @t select '2008-03-19',45
insert @t select '2008-03-20',78
insert @t select '2008-03-21',88
insert @t select '2008-03-22',54
insert @t select '2008-03-23',5
insert @t select '2008-03-24',2
insert @t select '2008-03-25',33
insert @t select '2008-03-26',44
insert @t select '2008-03-27',56
insert @t select '2008-03-28',77
insert @t select '2008-03-29',22
insert @t select '2008-03-30',44
insert @t select '2008-03-31',10select wk as 周数,sum(profit) as profit
from
(
select day(dt)%4+1 as wk,*
from @t
) t
group by wk/*
周数 profit
----------- -----------
1 236
2 384
3 337
4 300(4 行受影响)
*/
from
(
select (select count(*) from @t where dt<a.dt)/7+1 as wk,*
from @t a
) t
group by wk
select wk as 周数,sum(profit) as profit
from
(
select ntile(4) over(order by dt) as wk,*
from @t a
) t
group by wk周数 profit
-------------------- -----------
1 292
2 339
3 340
4 286(4 行受影响)
declare @t table(dt datetime,profit int)
insert @t select '2008-03-01',5
insert @t select '2008-03-02',45
insert @t select '2008-03-03',57
insert @t select '2008-03-07',58
insert @t select '2008-03-08',5
insert @t select '2008-03-09',45
insert @t select '2008-03-10',34
insert @t select '2008-03-11',67
insert @t select '2008-03-15',2
insert @t select '2008-03-16',45
insert @t select '2008-03-17',56
insert @t select '2008-03-18',12
insert @t select '2008-03-19',45
insert @t select '2008-03-20',78
insert @t select '2008-03-24',2
insert @t select '2008-03-25',33
insert @t select '2008-03-26',44
insert @t select '2008-03-27',56
insert @t select '2008-03-30',44
insert @t select '2008-03-31',10
insert @t select '2008-04-04',6
insert @t select '2008-04-05',57
insert @t select '2008-04-06',59
insert @t select '2008-04-12',23
insert @t select '2008-04-13',78
insert @t select '2008-04-14',45
insert @t select '2008-04-21',88
insert @t select '2008-04-22',54
insert @t select '2008-04-23',5
insert @t select '2008-04-28',77
insert @t select '2008-04-29',22select datepart(mm,dt)as 月份,datepart(wk,dt)as 周次,sum(profit)as 总和 from @t group by datepart(mm,dt),datepart(wk,dt)