什么是Access SQL中的Select Case等效项? [英] What is the equivalent of Select Case in Access SQL?

问题描述

我有一个查询，其中包含名为 openingbalance 和 commissions 的字段.我想基于 openingbalance 计算佣金的值，类似于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()表达式的替代方法.它将从第一个表达式/值对返回该表达式为真的值，而忽略其余所有对.该概念类似于您引用的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屋！