create table tb(productid varchar(10),quantity int,Createdate datetime) insert into tb select 'g1',1000,'2008-1-10' insert into tb select 'g1',500,'2008-1-12' insert into tb select 'g1',1600,'2008-1-16' insert into tb select 'g1',800,'2008-1-19' insert into tb select 'g1',300,'2008-1-20' insert into tb select 'g1',900,'2008-1-24' insert into tb select 'g1',600,'2008-1-26' insert into tb select 'g1',300,'2008-1-28' insert into tb select 'g1',600,'2008-1-30' go select productid,sum(quantity),dateadd(dd,9-datepart(dw,createdate),createdate) from tb group by productid,dateadd(dd,9-datepart(dw,createdate),createdate) go drop table tb /* productid ---------- ----------- ----------------------- g1 1500 2008-01-14 00:00:00.000 g1 2400 2008-01-21 00:00:00.000 g1 1800 2008-01-28 00:00:00.000 g1 900 2008-02-04 00:00:00.000(4 行受影响) */
create table tb(productid varchar(10), quantity int, Createdate datetime) insert into tb values('g1' , 1000 , '2008-1-10') insert into tb values('g1' , 500 , '2008-1-12') insert into tb values('g1' , 1600 , '2008-1-16') insert into tb values('g1' , 800 , '2008-1-19') insert into tb values('g1' , 300 , '2008-1-20') insert into tb values('g1' , 900 , '2008-1-24') insert into tb values('g1' , 600 , '2008-1-26') insert into tb values('g1' , 300 , '2008-1-28') insert into tb values('g1' , 600 , '2008-1-30') goselect productid , sum(quantity) quantity , datepart(week , Createdate) '第几周' from tb group by productid,datepart(week , Createdate)drop table tb/* productid quantity 第几周 ---------- ----------- ----------- g1 1500 2 g1 2400 3 g1 1800 4 g1 900 5(所影响的行数为 4 行)*/
create table tb(productid varchar(10), quantity int, Createdate datetime) insert into tb values('g1' , 1000 , '2008-1-10') insert into tb values('g1' , 500 , '2008-1-12') insert into tb values('g1' , 1600 , '2008-1-16') insert into tb values('g1' , 800 , '2008-1-19') insert into tb values('g1' , 300 , '2008-1-20') insert into tb values('g1' , 900 , '2008-1-24') insert into tb values('g1' , 600 , '2008-1-26') insert into tb values('g1' , 300 , '2008-1-28') insert into tb values('g1' , 600 , '2008-1-30') go --查本周 select productid , sum(quantity) quantity ,DATEADD(wk, DATEDIFF(wk,0,Createdate),0) Createdate from tb group by productid,DATEADD(wk, DATEDIFF(wk,0,Createdate),0) /* productid quantity Createdate ---------- ----------- ------------------------------------------------------ g1 1500 2008-01-07 00:00:00.000 g1 2400 2008-01-14 00:00:00.000 g1 1800 2008-01-21 00:00:00.000 g1 900 2008-01-28 00:00:00.000(所影响的行数为 4 行) */ --查上周 select productid , sum(quantity) quantity ,DATEADD(wk, DATEDIFF(wk,0,Createdate),0)+7 Createdate from tb group by productid,DATEADD(wk, DATEDIFF(wk,0,Createdate),0) +7 /* productid quantity Createdate ---------- ----------- ------------------------------------------------------ g1 1500 2008-01-14 00:00:00.000 g1 2400 2008-01-21 00:00:00.000 g1 1800 2008-01-28 00:00:00.000 g1 900 2008-02-04 00:00:00.000(所影响的行数为 4 行) */ drop table tb
--> 测试数据: @s declare @s table (productid varchar(2),quantity int,Createdate datetime) insert into @s select 'g1',1000,'2008-1-10' union all select 'g1',500,'2008-1-12' union all select 'g1',1600,'2008-1-16' union all select 'g1',800,'2008-1-19' union all select 'g1',300,'2008-1-20' union all select 'g1',900,'2008-1-24' union all select 'g1',600,'2008-1-26' union all select 'g1',300,'2008-1-28' union all select 'g1',600,'2008-1-30' set datefirst 1 select productid,quantity=sum(quantity), Createdate=case datepart(dw,Createdate) when 1 then Createdate else dateadd(dd,8-datepart(dw,Createdate),Createdate) end into # from @s a group by productid,case datepart(dw,Createdate) when 1 then Createdate else dateadd(dd,8-datepart(dw,Createdate),Createdate) end select productid,quantity=(select sum(quantity) from # where createdate<=a.createdate),createdate from # a --结果: productid quantity createdate --------- ----------- ------------------------------------------------------ g1 1500 2008-01-14 00:00:00.000 g1 4200 2008-01-21 00:00:00.000 g1 6000 2008-01-28 00:00:00.000 g1 6600 2008-02-04 00:00:00.000
if object_id('tb') is not null drop table tb go create table tb(productid varchar(10),quantity int,Createdate datetime) insert into tb select 'g1',1000,'2008-1-10' insert into tb select 'g1',500,'2008-1-12' insert into tb select 'g1',1600,'2008-1-16' insert into tb select 'g1',800,'2008-1-19' insert into tb select 'g1',300,'2008-1-20' insert into tb select 'g1',900,'2008-1-24' insert into tb select 'g1',600,'2008-1-26' insert into tb select 'g1',300,'2008-1-28' insert into tb select 'g1',600,'2008-1-30' go select productid,sum(quantity),max(dateadd(wk,datediff(wk,'19000101',Createdate)+1,'19000101')) from tb group by productid,datepart(wk,Createdate) go drop table tb productid ---------- ----------- ------------------------------------------------------ g1 1500 2008-01-14 00:00:00.000 g1 2400 2008-01-21 00:00:00.000 g1 1800 2008-01-28 00:00:00.000 g1 900 2008-02-04 00:00:00.000(所影响的行数为 4 行)
if object_id('tb') is not null drop table tb go create table tb(productid varchar(10),quantity int,Createdate datetime) insert into tb select 'g1',1000,'2008-1-10' insert into tb select 'g1',500,'2008-1-12' insert into tb select 'g1',1600,'2008-1-16' insert into tb select 'g1',800,'2008-1-19' insert into tb select 'g1',300,'2008-1-20' insert into tb select 'g1',900,'2008-1-24' insert into tb select 'g1',600,'2008-1-26' insert into tb select 'g1',300,'2008-1-28' insert into tb select 'g1',600,'2008-1-30' go select a.productid,sum(sum1),max(dateadd(wk,dt1+1,'20080101')) from (select productid,sum1=sum(quantity),dt=datepart(wk,Createdate) from tb group by productid,datepart(wk,Createdate)) a left join (select dt1=datepart(wk,Createdate) from tb group by datepart(wk,Createdate)) b on b.dt1<=a.dt group by a.dt,productid
insert into tb select 'g1',1000,'2008-1-10'
insert into tb select 'g1',500,'2008-1-12'
insert into tb select 'g1',1600,'2008-1-16'
insert into tb select 'g1',800,'2008-1-19'
insert into tb select 'g1',300,'2008-1-20'
insert into tb select 'g1',900,'2008-1-24'
insert into tb select 'g1',600,'2008-1-26'
insert into tb select 'g1',300,'2008-1-28'
insert into tb select 'g1',600,'2008-1-30'
go
select productid,sum(quantity),dateadd(dd,9-datepart(dw,createdate),createdate) from tb
group by productid,dateadd(dd,9-datepart(dw,createdate),createdate)
go
drop table tb
/*
productid
---------- ----------- -----------------------
g1 1500 2008-01-14 00:00:00.000
g1 2400 2008-01-21 00:00:00.000
g1 1800 2008-01-28 00:00:00.000
g1 900 2008-02-04 00:00:00.000(4 行受影响)
*/
insert into tb values('g1' , 1000 , '2008-1-10')
insert into tb values('g1' , 500 , '2008-1-12')
insert into tb values('g1' , 1600 , '2008-1-16')
insert into tb values('g1' , 800 , '2008-1-19')
insert into tb values('g1' , 300 , '2008-1-20')
insert into tb values('g1' , 900 , '2008-1-24')
insert into tb values('g1' , 600 , '2008-1-26')
insert into tb values('g1' , 300 , '2008-1-28')
insert into tb values('g1' , 600 , '2008-1-30')
goselect productid , sum(quantity) quantity , datepart(week , Createdate) '第几周'
from tb
group by productid,datepart(week , Createdate)drop table tb/*
productid quantity 第几周
---------- ----------- -----------
g1 1500 2
g1 2400 3
g1 1800 4
g1 900 5(所影响的行数为 4 行)*/
insert into tb values('g1' , 1000 , '2008-1-10')
insert into tb values('g1' , 500 , '2008-1-12')
insert into tb values('g1' , 1600 , '2008-1-16')
insert into tb values('g1' , 800 , '2008-1-19')
insert into tb values('g1' , 300 , '2008-1-20')
insert into tb values('g1' , 900 , '2008-1-24')
insert into tb values('g1' , 600 , '2008-1-26')
insert into tb values('g1' , 300 , '2008-1-28')
insert into tb values('g1' , 600 , '2008-1-30')
go
--查本周
select productid , sum(quantity) quantity ,DATEADD(wk, DATEDIFF(wk,0,Createdate),0) Createdate
from tb
group by productid,DATEADD(wk, DATEDIFF(wk,0,Createdate),0)
/*
productid quantity Createdate
---------- ----------- ------------------------------------------------------
g1 1500 2008-01-07 00:00:00.000
g1 2400 2008-01-14 00:00:00.000
g1 1800 2008-01-21 00:00:00.000
g1 900 2008-01-28 00:00:00.000(所影响的行数为 4 行)
*/
--查上周
select productid , sum(quantity) quantity ,DATEADD(wk, DATEDIFF(wk,0,Createdate),0)+7 Createdate
from tb
group by productid,DATEADD(wk, DATEDIFF(wk,0,Createdate),0) +7
/*
productid quantity Createdate
---------- ----------- ------------------------------------------------------
g1 1500 2008-01-14 00:00:00.000
g1 2400 2008-01-21 00:00:00.000
g1 1800 2008-01-28 00:00:00.000
g1 900 2008-02-04 00:00:00.000(所影响的行数为 4 行)
*/
drop table tb
declare @s table (productid varchar(2),quantity int,Createdate datetime)
insert into @s
select 'g1',1000,'2008-1-10' union all
select 'g1',500,'2008-1-12' union all
select 'g1',1600,'2008-1-16' union all
select 'g1',800,'2008-1-19' union all
select 'g1',300,'2008-1-20' union all
select 'g1',900,'2008-1-24' union all
select 'g1',600,'2008-1-26' union all
select 'g1',300,'2008-1-28' union all
select 'g1',600,'2008-1-30'
set datefirst 1
select productid,quantity=sum(quantity),
Createdate=case datepart(dw,Createdate) when 1 then Createdate else dateadd(dd,8-datepart(dw,Createdate),Createdate) end into # from @s a
group by productid,case datepart(dw,Createdate) when 1 then Createdate else dateadd(dd,8-datepart(dw,Createdate),Createdate) end
select productid,quantity=(select sum(quantity) from # where createdate<=a.createdate),createdate from # a
--结果:
productid quantity createdate
--------- ----------- ------------------------------------------------------
g1 1500 2008-01-14 00:00:00.000
g1 4200 2008-01-21 00:00:00.000
g1 6000 2008-01-28 00:00:00.000
g1 6600 2008-02-04 00:00:00.000
得出Createdate所对应周的周一: dateadd(wk,datediff(wk,'19000101',Createdate)+1,'19000101')
判断是否属于同一周: datepart(wk,Createdate)
drop table tb
go
create table tb(productid varchar(10),quantity int,Createdate datetime)
insert into tb select 'g1',1000,'2008-1-10'
insert into tb select 'g1',500,'2008-1-12'
insert into tb select 'g1',1600,'2008-1-16'
insert into tb select 'g1',800,'2008-1-19'
insert into tb select 'g1',300,'2008-1-20'
insert into tb select 'g1',900,'2008-1-24'
insert into tb select 'g1',600,'2008-1-26'
insert into tb select 'g1',300,'2008-1-28'
insert into tb select 'g1',600,'2008-1-30'
go
select productid,sum(quantity),max(dateadd(wk,datediff(wk,'19000101',Createdate)+1,'19000101')) from tb
group by productid,datepart(wk,Createdate)
go
drop table tb
productid
---------- ----------- ------------------------------------------------------
g1 1500 2008-01-14 00:00:00.000
g1 2400 2008-01-21 00:00:00.000
g1 1800 2008-01-28 00:00:00.000
g1 900 2008-02-04 00:00:00.000(所影响的行数为 4 行)
drop table tb
go
create table tb(productid varchar(10),quantity int,Createdate datetime)
insert into tb select 'g1',1000,'2008-1-10'
insert into tb select 'g1',500,'2008-1-12'
insert into tb select 'g1',1600,'2008-1-16'
insert into tb select 'g1',800,'2008-1-19'
insert into tb select 'g1',300,'2008-1-20'
insert into tb select 'g1',900,'2008-1-24'
insert into tb select 'g1',600,'2008-1-26'
insert into tb select 'g1',300,'2008-1-28'
insert into tb select 'g1',600,'2008-1-30'
go
select a.productid,sum(sum1),max(dateadd(wk,dt1+1,'20080101'))
from (select productid,sum1=sum(quantity),dt=datepart(wk,Createdate) from tb group by productid,datepart(wk,Createdate)) a left join
(select dt1=datepart(wk,Createdate) from tb group by datepart(wk,Createdate)) b on b.dt1<=a.dt
group by a.dt,productid
---------- ----------- ------------------------------------------------------
g1 1500 2008-01-22 00:00:00.000
g1 4800 2008-01-29 00:00:00.000
g1 5400 2008-02-05 00:00:00.000
g1 3600 2008-02-12 00:00:00.000(所影响的行数为 4 行)