Oracle 11g:当查询不返回任何内容时默认为静态值

2021-12-30 00:00:00 sql oracle11g oracle

在 Oracle 11g 中工作时，我需要选择一个与表中存在的输入值相对应的数据，而当该值存在于表中时，我需要选择一个静态默认值.我能找到的最好的方法是写这样的东西:

Working in Oracle 11g, I have a need to select a datum corresponding to an input value when that value exists in a table, and to instead select a static default value when it does not. The best way I could find to accomplish this was to write something like this:

SELECT desired_datum
FROM (
    --Try to get explicit datum
    SELECT desired_datum, 1 AS was_found
    FROM data_table
    WHERE the_key = &input_value
    UNION
    --Get default datum
    SELECT 'default' AS desired_datum, 0 AS was_found
    FROM dual
    --Put explicit datum on top, if it exists
    ORDER BY was_found DESC
) finder
WHERE ROWNUM <=1;

似乎必须有一些惯用的方法来做到这一点，它不依赖于 ORDER BY 的这种奇怪用法，但我找不到它.有谁知道更好的方法吗?

It seems like there must be some idiomatic way to do this which doesn't depend on this strange use of ORDER BY, but I couldn't find it. Does anyone know of any better methods?

推荐答案

这应该是你所做的更简单的版本:

This should be a simpler version of what you did:

SELECT NVL(desired_datum, 'default') AS desired_datum
FROM DUAL LEFT JOIN  data_table ON the_key = &input_value

相关文章