27,579
社区成员
发帖
与我相关
我的任务
分享
select cDepCode,cInvCode,sum(isnull(iQuantity,0)) iQuantity,sum(isnull(iPrice,0 )) iPrice
from a01
group by cDepCode,cInvCode
--如果不出現在前面,而又不在聚合函數(sum,max等)中就要出現在group by 中,要麼就不要出現select 列表中
DECLARE @TB TABLE( cDepCode VARCHAR(4),cDepName NVARCHAR(5),cInvCode VARCHAR(5),cInvname NVARCHAR(10),iQuantity DECIMAL(10,1),iPrice DECIMAL(11,4))
INSERT @TB
SELECT '1001', N'脱酸', '01001', N'分子膜片', 203823.0, 2078994.6000 UNION ALL
SELECT '1001', N'脱酸', '01002', N'离子膜片碱', 100.0, NULL UNION ALL
SELECT '1001', N'脱酸', '01001', N'分子膜片', 77.0, NULL UNION ALL
SELECT '1002', N'硬化', '01001', N'分子膜片', 10.0, NULL UNION ALL
SELECT '1002', N'硬化', '01001', N'分子膜片', 15.0, NULL
SELECT cDepCode,cDepName,cInvCode,cInvname,SUM(iQuantity) AS iQuantity,ISNULL(SUM(iPrice),0) AS iPrice
FROM @TB
GROUP BY cDepCode,cDepName,cInvCode,cInvname
/*
cDepCode cDepName cInvCode cInvname iQuantity iPrice
-------- -------- -------- ---------- ---------------------------------------- ----------------------------------------
1001 脱酸 01001 分子膜片 203900.0 2078994.6000
1001 脱酸 01002 离子膜片碱 100.0 .0000
1002 硬化 01001 分子膜片 25.0 .0000
*/
select cDepCode,cDepName,cInvCode,cInvname,sum(isnull(iQuantity,0)) iQuantity,sum(isnull(iPrice,0 )) iPrice
from a01
group by cDepCode,cDepName,cInvCode,cInvname
select cDepCode,cDepName,cInvCode,cInvname,sum(iQuantity) as iQuantity,sum(iPrice) as iPrice from a01
group by cDepCode,cDepName,cInvCode,cInvname
select
cDepCode,cDepName,cInvCode,cInvname,sum(isnull(iQuantity,0)) iQuantity,sum(isnull(iPrice,0 )) iPrice
from a01
where --这里是时间段限制条件
group by cDepCode,cDepName,cInvCode,cInvname
select
cDepCode,cDepName,cInvCode,cInvname,sum(isnull(iQuantity,0)) iQuantity,sum(isnull(iPrice,0 )) iPrice
from a01
group by cDepCode,cDepName,cInvCode,cInvname