如果另一列为空，则选择一列 [英] SELECT one column if the other is null

问题描述

我想选择a2.date(如果存在)，但是如果它是NULL，我想选择a1.date(a2被左联接).这个:

I want to select a2.date if it's there, but if it's NULL I want to select a1.date (a2 is being left-joined). This:

SELECT a2.date OR a1.date
       ...

简单地返回一个布尔结果(正如人们期望的那样)，但是我如何获得非null列的实际值呢? (首选a2.date，但如果它为null，则a1.date)

Simply returns a boolean result (as one would expect), how do I get the actual value of the non-null column though? (a2.date is preferred, but if it's null then a1.date)

推荐答案

ANSI的意思是使用

The ANSI means is to use COALESCE:

SELECT COALESCE(a2.date, a1.date) AS `date`
   ...

MySQL本机语法为 IFNULL :

The MySQL native syntax is IFNULL:

SELECT IFNULL(a2.date, a1.date) AS `date`
   ...

与COALESCE不同，IFNULL不可移植到其他数据库.

Unlike COALESCE, IFNULL is not portable to other databases.

另一种ANSI语法， CASE表达式，是一个选择:

Another ANSI syntax, the CASE expression, is an option:

SELECT CASE
         WHEN a2.date IS NULL THEN a1.date
         ELSE a2.date
       END AS `date`
   ...

需要更多的指导才能正常工作，但是如果需求发生变化，它会更加灵活.

It requires more direction to work properly, but is more flexible if requirements change.

这篇关于如果另一列为空，则选择一列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！