分组求和问题

[fzeng554]

PRDID BQTY PRD_NO PNAME SPC DATE

PC00000058 5.0000 A22301 TS客户 5+20 35
PC00000058 5.0000 A22301 TS客户 5+20 99
PC00000079 12.0000 A22401 KH-2432 5+15 0
PC00000082 720.0000 A32997 KH-2861 NOM 0
PC00000082 360.0000 A32997 KH-2861 NOM 2
PC00000267 555.0000 A22326 KH-0701020 5+20 224
PC00000310 555.0000 A20719 KH-07012502 5+20 135

SELECT A.PRDID,SUM(A.BQTY) AS BQTY,B.PRD_NO,B.PNAME,B.SPC,ISNULL(datediff(DD, CKDATE,GETDATE()),0) AS DATE FROM PRDTCOLORONEIN A
LEFT OUTER JOIN PRDTCOLOR B ON B.PRDID = A.PRDID WHERE OUTSTATE = 0
GROUP BY A.PRDID,B.PRD_NO,B.PNAME,B.SPC,datediff(DD, CKDATE,GETDATE())

上面的答案是我用这个语句查出来的

PRDID是编码,BQTY是数量,PRD_NO是色码,PNAME是品名,SPC是规格,DATE是当天日期减去出厂日期(CKDATE)得出来的天数,我现在想要这样做,根据0-30天之内,30-60天之内,60-90天之内,90-180之内,180以后分别去求SUM(BQTY),就是让上面那些答案变成

PRDID BQTY PRD_NO PNAME SPC DATE 30 60 90

PC00000058 5.0000 A22301 TS客户 5+20 35
PC00000058 5.0000 A22301 TS客户 5+20 99
PC00000079 12.0000 A22401 KH-2432 5+15 0
PC00000082 720.0000 A32997 KH-2861 NOM 0
PC00000082 360.0000 A32997 KH-2861 NOM 2
PC00000267 555.0000 A22326 KH-0701020 5+20 224
PC00000310 555.0000 A20719 KH-07012502 5+20 135

请教这要怎么做

[Godsaidlwq]

那总要把源表的格式给出！LZ
DATE是当天日期减去出厂日期(CKDATE)得出来的天数
没看到在上面的问题中有这方面的信息

[hyc_music1981]

没见结果集有什么变化呀

[gahade]

试试

SELECT A.PRDID,
SUM(A.BQTY) AS BQTY,
B.PRD_NO,
B.PNAME,
B.SPC,
ISNULL(datediff(DD, CKDATE,GETDATE()),0) AS DATE,
sum(case when ISNULL(datediff(DD, CKDATE,GETDATE()),0) between 0 and 30 then BQTY else 0 end) as [0-30],
sum(case when ISNULL(datediff(DD, CKDATE,GETDATE()),0) between 30 and 60 then BQTY else 0 end) as [30-60],
sum(case when ISNULL(datediff(DD, CKDATE,GETDATE()),0) between 60 and 90 then BQTY else 0 end) as [60-90],
sum(case when ISNULL(datediff(DD, CKDATE,GETDATE()),0) between 90 and 180 then BQTY else 0 end) as [90-180],
sum(case when ISNULL(datediff(DD, CKDATE,GETDATE()),0)>180 then BQTY else 0 end) as [180以后]
FROM PRDTCOLORONEIN A
LEFT OUTER JOIN PRDTCOLOR B ON B.PRDID = A.PRDID
WHERE OUTSTATE = 0
GROUP BY A.PRDID,B.PRD_NO,B.PNAME,B.SPC,datediff(DD, CKDATE,GETDATE())

[fzeng554]

gahade(与君共勉) 多谢兄台