試圖做一個自定義GROUP BY Range.
我構建了以下 SQL 查詢：
SELECT TheTmp, SUM(TheTmp*0.33)
FROM MyTable
WHERE Name = 'TestName1'
GROUP BY SWITCH(TheTmp BETWEEN 0 AND 5,0,50,true)
但它拋出一個錯誤：
您的查詢不包括指定的運算式“TheTmp”作為聚合函式的一部分。
'TheTmp' 是正確的欄位，因為以下查詢有效：
Select TheTmp, SUM(TheTmp*0.33)
FROM MyTable
WHERE Name = 'TestName1'
GROUP BY TheTmp
所以我認為我BETWEEN在我的SWITCH()
對于下表：
Name TheTmp
TestName1 8.76669265399322
TestName2 0.273812785388691
TestName1 1
TestName1 1.58938597048424
我的目標是獲得以下輸出（將 0 和 5 之間的值相加）
TestName1 8.766*0.33
TestName1 (1.58938597048424*0.33) (1*0.33)
我如何使用BETWEEN成SWITCH
SWITCH(TheTmp BETWEEN 0 AND 5,0,50,true)
uj5u.com熱心網友回復：
嘗試這個：
SELECT Name, Sum(0.33*[TheTmp]) AS Amount
FROM MyTable
GROUP BY Name, IIf([TheTmp]>=0 And [TheTmp]<=5,"Group1","Group2")
HAVING (((Name)="TestName1"));
結果如下：
這是您的第二個請求：
SELECT IIf([TheTmp]>=0 And [TheTmp]<=5,"Group1","Group2") AS Grouping, Sum(0.33*[TheTmp]) AS Amount
FROM MyTable
GROUP BY IIf([TheTmp]>=0 And [TheTmp]<=5,"Group1","Group2"), Name
HAVING ((([Name])="TestName1"));
這是請求編號 3：
SELECT IIf([TheTmp]>=0 And [TheTmp]<=5,"Group1","Group2") AS Grouping, Sum(0.33*[TheTmp]) AS Amount
FROM MyTable
GROUP BY IIf([TheTmp]>=0 And [TheTmp]<=5,"Group1","Group2"), Name
HAVING ((([Name])="TestName1"))
ORDER BY IIf([TheTmp]>=0 And [TheTmp]<=5,"Group1","Group2") DESC;
uj5u.com熱心網友回復：
Between .. And是SQL語法。在Switch使用VBA語法：
TheTmp >= 0 And TheTmp <= 5
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/334859.html