表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 行受影响)
------解决思路----------------------
SELECT pno,MIN(crdate) crdate,SUM(num) num FROM dbo.testS
GROUP BY pno, DATEPART(WEEK,crdate-1)
ORDER BY pno