试试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())
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())