34,590
社区成员
发帖
与我相关
我的任务
分享
;WITH LI AS (
SELECT 单据类别,材料编码,AVG(单价) AS 单价,SUM(数量) AS 数量
FROM TABLE A GROUP BY 单据类别,材料编码)
SELECT A.单据类别,B.材料编码, (CASE WHEN ISNULL(A.单价,0) =0 and isnull(B.单价,0) <> 0 then B.单价 else A.单价 end),A.数量
FROM LI A LEFT JOIN LI B ON A.单据类别=B.单据类别 AND A.材料编码 = B.材料编码
SELECT 单据类别,材料编码,AVG(单价) AS 单价,SUM(数量) AS 数量
FROM TABLE A GROUP BY 单据类别,材料编码