DECLARE @TuiHuoNumber BIGINTSELECT SUM(RefundAmount)
FROM
Refund
WHERE
[Status]<>3
AND
NOT
(ReferenceType IN (4,19,20))
AND
@TuiHuoNumber= [Service].[dbo].[refund].ReferenceNumber
GROUP BY ReferenceNumber HAVING SUM(RefundAmount)>0
这是之前别人写的,DBA说' GROUP BY'这里多余,效率不好,求大家给优化一下,谢谢.
FROM
Refund
WHERE
[Status]<>3
AND
NOT
(ReferenceType IN (4,19,20))
AND
@TuiHuoNumber= [Service].[dbo].[refund].ReferenceNumber
GROUP BY ReferenceNumber HAVING SUM(RefundAmount)>0
这是之前别人写的,DBA说' GROUP BY'这里多余,效率不好,求大家给优化一下,谢谢.
declare @TuiHuoNumber bigint
;WITH maco AS
(
SELECT SUM(RefundAmount) AS SumRefundAmount
FROM Refund
WHERE [Status] <> 3
AND ReferenceNumber=@TuiHuoNumber
AND ReferenceType NOT IN ( 4, 19, 20 )
)
SELECT * FROM maco WHERE SumRefundAmount>0