具有null值的where子句中的case

I want to check if the value is exist in the column than give that record, else give record which have NULL value in it.

我想检查列中是否存在该值而不是给出该记录，否则给出其中包含NULL值的记录。

Here is the sample query but it is not working for me as syntax is not correct.

这是示例查询，但它不适合我，因为语法不正确。

SELECT *
FROM t_config_rule
WHERE rule = (
  CASE
    WHEN rule = 'IND'
    THEN 'IND'
    ELSE NULL
  END)

1 个解决方案

#1

A CASE expression in the WHERE clause makes no sense, as you can always say the same with simple AND and/or OR. Your condition translates to:

WHERE子句中的CASE表达式毫无意义，因为您可以使用简单的AND和/或OR来表达相同的内容。您的病情转化为：

WHERE rule = 'IND' OR rule = NULL

As rule = NULL is never true (it would have to be rule IS NULL for this to work), the condition is essentially a mere

因为rule = NULL永远不是真的（它必须是规则IS NULL才能工作），条件基本上只是一个

WHERE rule = 'IND'

You seem to want something entirely different anyway, it seems. You seem to want to look for one single record. It shall be the one with rule = 'IND'. If that record does no exist, you want the record with rule IS NULL instead.

似乎你似乎想要一些完全不同的东西。你似乎想要寻找一个单一的记录。它应该是rule ='IND'的那个。如果该记录不存在，则需要具有规则IS NULL的记录。

So select both records, the one with 'IND' and the one with NULL, and then keep the preferred one:

因此，选择两个记录，一个是'IND'，另一个是NULL，然后保留首选记录：

SELECT *
FROM t_config_rule
WHERE rule = 'IND' OR rule IS NULL
ORDER BY rule NULLS LAST
FETCH FIRST ROW ONLY;

The FETCH FIRST clause is available only as of Oracle 12c, though. For older versions use:

但是，FETCH FIRST子句仅在Oracle 12c中可用。对于旧版本使用：

SELECT *
FROM
(
  SELECT *
  FROM t_config_rule
  WHERE rule = 'IND' OR rule IS NULL
  ORDER BY rule NULLS LAST
)
WHERE ROWNUM = 1;

or number the rows with ROW_NUMBER and keep the first row:

或使用ROW_NUMBER对行进行编号并保留第一行：

SELECT *
FROM
(
  SELECT r. *, ROW_NUMBER() OVER (ORDER BY rule NULLS LAST) AS rn
  FROM t_config_rule r
  WHERE rule = 'IND' OR rule IS NULL
)
WHERE rn = 1;

FETCH FIRST and ROW_NUMBER are standard SQL, whereas ROWNUM is Oracle-only (and even violating the standard at that).

FETCH FIRST和ROW_NUMBER是标准SQL，而ROWNUM只是Oracle（甚至违反了标准）。

#1