这是我的查询:
select col1, col2, (<a fairly complex query> as col3) from <a table>
<a fairly complex query>
可能会返回NULL
,在这种情况下,我想将col3
设置为 col2
。我知道我可以用CASE
语句来做到这一点:
select col1, col2,
CASE WHEN (<a fairly complex query>) is NULL col2
ELSE (<a fairly complex query>) END AS col3
from <a table>
但是,该方法执行<a fairly complex query>
两次。如果我只想执行一次<a fairly complex query>
,有哪些选择?
您可以使用子查询和COALESCE
:
SELECT col1, col2, COALESCE(col3, col2) AS col3
FROM (select col1, col2, (<a fairly complex query>) as col3
from <a table>) AS sub;
不带子查询:
SELECT col1, col2, COALESCE(<a fairly complex query>, col2) AS col3
FROM <a table>;
与另一个答案类似,但不需要子查询:
select col1, col2, COALESCE(<a fairly complex query>, col2) AS col3 from <a table>