相当于Oracle的Access/jet解码 [英] Access/jet equivalent of Oracle's decode

问题描述

在Access(或Jet，就此而言)中，Oracle的decode()是否具有等效项.

Is there an equivalent for Oracle's decode() in Access (or Jet, for that matter).

我面临的问题是:我应该基于以下条件对结果集进行排序(排序) 最后一个状态和日期(所有记录的状态均为2).

The problem I am facing is: I should sort (order) a resultset based basically upon a status and a date (with all records having status = 2) at the end.

在Oracle中，我会做类似的事情

In Oracle I'd go something like

select
  ...
from
  ...
where
  ..
order by
  decode(status, 2, 0, 1),
  date_column

推荐答案

最接近的类比是SWITCH()函数，例如

The closest analogy is the SWITCH() function e.g.

Oracle:

SELECT supplier_name,
       decode(supplier_id,  10000, 'IBM',
                            10001, 'Microsoft',
                            10002, 'Hewlett Packard',
                                   'Gateway') result
  FROM suppliers;

访问数据库引擎

SELECT supplier_name,
       SWITCH(supplier_id = 10000, 'IBM',
              supplier_id = 10001, 'Microsoft',
              supplier_id = 10002, 'Hewlett Packard',
              TRUE, 'Gateway') AS result
  FROM suppliers; 

请注意，使用SWITCH()函数时，您每次都必须提供完整的谓词，因此，您不仅限于使用vendor_id.对于默认值，请使用对人类读者显而易见的谓词，例如TRUE. 1 = 1或实际上只是TRUE:)

Note that with the SWITCH() function you have to supply the full predicate each time, so you are not restricted to using just supplier_id. For the default value, use a predicate that is obvious to the human reader that it is TRUE e.g. 1 = 1 or indeed simply TRUE :)

可能不太明显的是SWITCH()函数中的逻辑不会短路，这意味着必须能够对该函数中的每个表达式进行正确的计算.如果需要短路逻辑，则需要使用嵌套的IIF()函数.

Something that may not be obvious is that the logic in the SWITCH() function doesn't short circuit, meaning that every expression in the function must be able to be evaluated without error. If you require logic to short circuit then you will need to use nested IIF() functions.

这篇关于相当于Oracle的Access/jet解码的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！