Access SQL 中的 Select Case 相当于什么? [英] What is the equivalent of Select Case in Access SQL?

问题描述

我有一个查询，其中包括名为 openingbalance 和 commissions 的字段.我想根据 openingbalance 计算 commissions 的值，类似于 Access VBA 中的这个 Select Case 块:

I have a query which includes fields named openingbalance and commissions. I would like to compute values for commissions based on openingbalance, similar to this Select Case block in Access VBA:

Select Case OpeningBalance
   Case 0 To 5000
        commission = 20
   Case 5001 To 10000
        commission = 30
   Case 10001 To 20000
        commission = 40
   Case Else
        commission = 50
End Select

但是由于 Access 不允许在查询中使用 Select Case，我如何才能在 Access SQL 中实现我的目标?

But since Access doesn't allow Select Case in a query, how can I accomplish my goal in Access SQL?

推荐答案

考虑 切换函数 作为多个 IIf() 表达式的替代.它将返回第一个表达式/值对的值，其中表达式的计算结果为 True，并忽略任何剩余的对.该概念类似于您引用的 SELECT ... CASE 方法，但它在 Access SQL 中不可用.

Consider the Switch Function as an alternative to multiple IIf() expressions. It will return the value from the first expression/value pair where the expression evaluates as True, and ignore any remaining pairs. The concept is similar to the SELECT ... CASE approach you referenced but which is not available in Access SQL.

如果要将计算字段显示为 commission:

If you want to display a calculated field as commission:

SELECT
    Switch(
        OpeningBalance < 5001, 20,
        OpeningBalance < 10001, 30,
        OpeningBalance < 20001, 40,
        OpeningBalance >= 20001, 50
        ) AS commission
FROM YourTable;

如果您想将该计算值存储到名为 commission 的字段中:

If you want to store that calculated value to a field named commission:

UPDATE YourTable
SET commission =
    Switch(
        OpeningBalance < 5001, 20,
        OpeningBalance < 10001, 30,
        OpeningBalance < 20001, 40,
        OpeningBalance >= 20001, 50
        );

无论哪种方式，看看您是否发现 Switch() 更易于理解和管理.随着条件数量的增加，多个 IIf()s 可能会变得令人难以置信.

Either way, see whether you find Switch() easier to understand and manage. Multiple IIf()s can become mind-boggling as the number of conditions grows.

这篇关于Access SQL 中的 Select Case 相当于什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！