執行此 MSSQL 陳述句時，它會強制我在 group by 欄位中添加 CASE WHEN 欄位 History.Email，但是我只需要在日期欄位上進行分組。知道如何解決這個問題嗎？(SQL 2008)
SELECT TOP (100)
convert(varchar, VisitDate, 111) as Date,
SUM(AmountLoaded) AS Loaded,
CASE when History.Email is null then
SUM(AmountCash)
else SUM(AmountToPay)
end AS Total,
CASE when History.Email is null then
0
else SUM(AmountToPay)
end AS App,
SUM(AmountToPay) AS Consumed
FROM dbo.History
GROUP BY convert(varchar, VisitDate, 111)
order by 1 desc
uj5u.com熱心網友回復：
執行條件聚合時，您需要將整個 CASE運算式放在聚合中，而不僅僅是要聚合的運算式：
SELECT TOP (100)
CONVERT(varchar(10), H.VisitDate, 111) AS [Date], --Always specify a length/precision/scale for your data types
SUM(H.AmountLoaded) AS Loaded,
SUM(CASE WHEN H.Email IS NULL THEN H.AmountCash ELSE H.AmountToPay END) AS Total,
SUM(CASE WHEN H..Email IS NULL THEN 0 ELSE H.AmountToPay END) AS App,
SUM(H.AmountToPay) AS Consumed
FROM dbo.History H
GROUP BY CONVERT(varchar(10), H.VisitDate, 111) --Always specify a length/precision/scale for your data types
ORDER BY [Date] DESC; --Be specific, don't use ordinal positions
另請注意，我建議不要在查詢中將您的列轉換VisitDate為 a varchar。如果您想以特定格式顯示資料，那么這是您的表示層要做的事情，而不是RDBMS。幸運的是，由于樣式111是yyyy/MM/dd，因此資料的順序不受影響。但是，如果您只想截斷值的時間部分，請將其轉換為 adate以便該值繼續保持強型別。
uj5u.com熱心網友回復：
根據定義，任何非聚合欄位都應該在 group by 子句中。你可以這樣寫：
SELECT TOP (100)
convert(varchar, VisitDate, 111) as Date,
SUM(AmountLoaded) AS Loaded,
SUM(CASE when History.Email is null
then AmountCash
else AmountToPay end) AS Total,
SUM(CASE when History.Email is null
then 0
else AmountToPay
end) AS App,
SUM(AmountToPay) AS Consumed
FROM dbo.History
GROUP BY convert(varchar, VisitDate, 111)
order by 1 desc
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/419947.html
標籤：
上一篇：微服務架構 | *2.3 Spring Cloud 啟動及加載組態檔原始碼分析（以 Nacos 為例）
下一篇：我得到這個錯誤幫助我。訊息：CI_Session_files_driver::open($save_path,$name)的回傳型別應該與