当查询字段有聚合函数需要where条件时用having代替

最新推荐文章于 2023-08-20 15:38:38 发布

雪落夜

最新推荐文章于 2023-08-20 15:38:38 发布

阅读量1.3k

点赞数

分类专栏： SQL

本文链接：https://blog.csdn.net/a1017680279/article/details/27196623

版权

SQL 专栏收录该内容

7 篇文章 0 订阅

订阅专栏

SELECT Mate_PBusinessDocNo 采购单号,Mate_PTotalQuantity	总计数量,Mate_PTotalTon	总计吨数,Mate_PTotalAmount,
	SUM(Mate_PDWholeQuantity) 细表数量, SUM(Mate_PDWholeTon) 细表吨数, SUM(Mate_PDAmount) 细表金额
FROM dbo.Mate_Purchasing p -- 采购单
LEFT JOIN dbo.Mate_PurchasingDetail pd ON p.Mate_PCode = pd.Mate_PCode-- 采购明细
GROUP BY Mate_PBusinessDocNo,Mate_PTotalQuantity,Mate_PTotalTon,Mate_PTotalAmount
HAVING Mate_PTotalQuantity <> SUM(Mate_PDWholeQuantity) OR Mate_PTotalTon <> SUM(Mate_PDWholeTon) OR Mate_PTotalAmount <> SUM(Mate_PDAmount)
		OR COUNT(Mate_PDWholeQuantity) = 0 --不存在细表数据的单据