sql - 在 Switch 语句中使用 BETWEEN

Question

试图做一个自定义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)

标签： sqlms-access

Answer 1

试试这个：

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;