当前位置: 代码迷 >> Sql Server >> ,关于表内合计
  详细解决方案

,关于表内合计

热度:61   发布时间:2016-04-24 09:04:40.0
求助,关于表内合计
表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
  相关解决方案