分组求和问题
PRDID BQTY PRD_NO PNAME SPC DATE
PC000000585.0000A22301TS客户 5+20 35
PC000000585.0000A22301TS客户 5+20 99
PC0000007912.0000A22401KH-2432 5+15 0
PC00000082720.0000A32997KH-2861 NOM 0
PC00000082360.0000A32997KH-2861 NOM 2
PC00000267555.0000A22326KH-0701020 5+20 224
PC00000310555.0000A20719KH-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
PC000000585.0000A22301TS客户 5+20 35
PC000000585.0000A22301TS客户 5+20 99
PC0000007912.0000A22401KH-2432 5+15 0
PC00000082720.0000A32997KH-2861 NOM 0
PC00000082360.0000A32997KH-2861 NOM 2
PC00000267555.0000A22326KH-0701020 5+20 224
PC00000310555.0000A20719KH-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,
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())
