，关于表内合计

求助，关于表内合计
表A
产品              时间                   数量
A            20150525              1000
A            20150526              1000
A            20150527              1000
A            20150528              1000
A            20150529              1000
A            20150530              1000
A            20150531              1000
B            20150531              1000
B            20150601              1000
B            20150602              1000
C            20150525              1000
C            20150526              1000
C            20150601              1000
C            20150602              1000

希望查询出来的结果是每周的合计，并把每周一位时间列
例如上面数据查询出来的结果希望是
产品              时间                   数量
A            20150525              7000
B            20150525              1000
B            20150601              2000
C            20150525              2000
C            20150601              2000

------解决思路----------------------

SET DATEFIRST 1;--设置周一为第一天

SELECT 产品

	,CONVERT(VARCHAR(8),DATEADD(DAY,1-DATEPART(WEEKDAY,时间),时间),112)[时间]

	,SUM(数量)[数量]

FROM A

GROUP BY 产品,CONVERT(VARCHAR(8),DATEADD(DAY,1-DATEPART(WEEKDAY,时间),时间),112)

ORDER BY 1,2

------解决思路----------------------

create table test(pno varchar(10) , crdate datetime , num int)

go

insert into test(pno,crdate,num) values

('A','20150525',1000),

('A','20150526',1000),

('A','20150527',1000),

('A','20150528',1000),

('A','20150529',1000),

('A','20150530',1000),

('A','20150531',1000),

('B','20150531',1000),

('B','20150601',1000),

('B','20150602',1000),

('C','20150525',1000),

('C','20150526',1000),

('C','20150601',1000),

('C','20150602',1000)

go

set datefirst 1 

select pno , min(crdate)  crdate, sum(num) num from test 

group by pno , crdate - DATEPART(weekday,crdate)

order by 1,2

go

drop table test 

go




(14 行受影响)

pno        crdate                  num

---------- ----------------------- -----------

A          2015-05-25 00:00:00.000 7000

B          2015-05-31 00:00:00.000 1000

B          2015-06-01 00:00:00.000 2000

C          2015-05-25 00:00:00.000 2000

C          2015-06-01 00:00:00.000 2000


(5 行受影响)

------解决思路----------------------

引用:

create table test(pno varchar(10) , crdate datetime , num int)

go

insert into test(pno,crdate,num) values

('A','20150525',1000),

('A','20150526',1000),

('A','20150527',1000),

('A','20150528',1000),

('A','20150529',1000),

('A','20150530',1000),

('A','20150531',1000),

('B','20150531',1000),

('B','20150601',1000),

('B','20150602',1000),

('C','20150525',1000),

('C','20150526',1000),

('C','20150601',1000),

('C','20150602',1000)

go

set datefirst 1 

select pno , min(crdate)  crdate, sum(num) num from test 

group by pno , crdate - DATEPART(weekday,crdate)

order by 1,2

go

drop table test 

go




(14 行受影响)

pno        crdate                  num

---------- ----------------------- -----------

A          2015-05-25 00:00:00.000 7000

B          2015-05-31 00:00:00.000 1000

B          2015-06-01 00:00:00.000 2000

C          2015-05-25 00:00:00.000 2000

C          2015-06-01 00:00:00.000 2000


(5 行受影响)

SELECT pno,MIN(crdate) crdate,SUM(num) num FROM dbo.testS

GROUP BY pno, DATEPART(WEEK,crdate-1)

ORDER BY pno