这如何有效?
ALTER PROCEDURE [StoredProcedure]
@abcID int = null -- optional param
SELECT columnJ, columnK, Count(eID) AS Num, Sum(OutXYZ) as TotalProdXYZ, Sum(RawXYZ) as TotalRawXYZ
FROM [v_ViewTable]
WHERE (@abcID IS NULL OR (abcID = @abcID))
GROUP BY columnJ, columnK
但这会引发“ columnJ在选择列表中无效,因为它既不在聚合函数中也不在GROUP BY子句中。”
ALTER PROCEDURE [StoredProcedure]
@abcID int = null -- optional param
-- if 0: Group by columnJ, columnK
-- else: Group by columnK, columnJ
, @Grouping int = null
SELECT columnJ, columnK, Count(eID) AS Num, Sum(OutXYZ) as TotalProdXYZ, Sum(RawXYZ) as TotalRawXYZ
FROM [v_ViewTable]
WHERE (@abcID IS NULL OR (abcID = @abcID))
GROUP BY
CASE WHEN @Grouping = 0 THEN columnJ ELSE columnK END
,CASE WHEN @Grouping = 0 THEN columnK ELSE columnJ END
我的CASE语句有什么问题?基本上,如果@Grouping = 0,我希望group by为J,K(如果还有其他内容),则应该为K,J
在此先感谢您的帮助!
如消息中所述,在第二种情况下columnJ
,columnK
它们不包含在聚合函数或GROUP BY子句中。您应该在GROUP BY部分的“按原样”选择列表中使用字段或精确表达式。
因此,以下语句将可以:
SELECT CASE WHEN @Grouping = 0 THEN columnJ ELSE columnK END,
CASE WHEN @Grouping = 0 THEN columnK ELSE columnJ END,
Count(eID) AS Num, Sum(OutXYZ) as TotalProdXYZ, Sum(RawXYZ) as TotalRawXYZ
FROM [v_ViewTable]
WHERE (@abcID IS NULL OR (abcID = @abcID))
GROUP BY
CASE WHEN @Grouping = 0 THEN columnJ ELSE columnK END
,CASE WHEN @Grouping = 0 THEN columnK ELSE columnJ END
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句