sql - 如何在 SQL 查询中声明一个常量？

Question

我正在使用 Oracle 11g R2。有没有办法在同一表达式中的 SQL 查询之前为从表中选择的单个值提供名称(别名)？这是一个单一的 SQL 命令，我的意思是，没有 PL/SQL。

我最接近的是:

WITH
Approved AS (SELECT c.value FROM configuration c WHERE c.code = 'Approved'),
Arrived AS (SELECT c.value FROM configuration c WHERE c.code = 'Arrived'),
Scheduled AS (SELECT c.value FROM configuration c WHERE c.code = 'Scheduled')

SELECT *
FROM list l WHERE l.status_key > (SELECT value FROM Approved);

我正在寻找类似的东西，比如:

WITH
Approved AS CONSTANT (SELECT c.value FROM configuration c WHERE c.code = 'Approved'),
Arrived AS CONSTANT (SELECT c.value FROM configuration c WHERE c.code = 'Arrived'),
Scheduled AS CONSTANT (SELECT c.value FROM configuration c WHERE c.code = 'Scheduled')

SELECT *
FROM list l WHERE l.status_key > Approved;

我不想内联值的 select 语句的原因是我的查询已经足够复杂，如果可能的话，我宁愿去掉一些复杂性。

Answer 1

我有时会使用这样的构造:

WITH const as
      (select max(case when c.code = 'Approved' then c.value end) as Approved,
              max(case when c.code = 'Approved' then c.value end) as Approved,
              max(case when c.code = 'Scheduled' then c.value end) as Scheduled
       from configuration c
      ),
      . . .
SELECT
FROM const cross join
     list l
WHERE status_key > Approved;

有时如果我需要查询中不同地方的常量，那么我必须引入 const CTE不止一次。