MySQL查询如何过滤掉BOO_BookingStatus为NULL的行？

嘿，这个需求很常见，我给你分享几种实用的解决方案，你可以根据自己使用的数据库和实际场景选择：

方法1：使用HAVING子句（最简洁） #

很多现代数据库允许在HAVING子句中直接引用SELECT里定义的别名，这样你不用重复写CASE的逻辑，代码非常简洁：

SELECT *, 
  CASE 
    WHEN ___Bookings.BOO_DateCI = '2017-04-15' THEN "In Arrival" 
    WHEN ___Bookings.BOO_DateCO = '2017-04-15' THEN "In Departure" 
    WHEN '2017-04-15' > ___Bookings.BOO_DateCI AND '2017-04-15' < ___Bookings.BOO_DateCO THEN "In House" 
  END AS BOO_BookingStatus 
FROM ___Bookings
HAVING BOO_BookingStatus IS NOT NULL;

方法2：在WHERE子句中复用CASE的判断条件 #

如果你的数据库不支持在HAVING里用别名（比如一些老版本的数据库），或者你想让优化器更好地处理过滤逻辑，可以把CASE里的条件直接搬到WHERE中：

方式A：重复CASE逻辑 #

SELECT *, 
  CASE 
    WHEN ___Bookings.BOO_DateCI = '2017-04-15' THEN "In Arrival" 
    WHEN ___Bookings.BOO_DateCO = '2017-04-15' THEN "In Departure" 
    WHEN '2017-04-15' > ___Bookings.BOO_DateCI AND '2017-04-15' < ___Bookings.BOO_DateCO THEN "In House" 
  END AS BOO_BookingStatus 
FROM ___Bookings
WHERE 
  CASE 
    WHEN ___Bookings.BOO_DateCI = '2017-04-15' THEN 1 
    WHEN ___Bookings.BOO_DateCO = '2017-04-15' THEN 1 
    WHEN '2017-04-15' > ___Bookings.BOO_DateCI AND '2017-04-15' < ___Bookings.BOO_DateCO THEN 1 
  END = 1;

方式B：合并为OR条件（更高效） #

把CASE里的三个判断条件用OR连接，这样数据库的查询优化器更容易利用索引，性能可能更好：

SELECT *, 
  CASE 
    WHEN ___Bookings.BOO_DateCI = '2017-04-15' THEN "In Arrival" 
    WHEN ___Bookings.BOO_DateCO = '2017-04-15' THEN "In Departure" 
    WHEN '2017-04-15' > ___Bookings.BOO_DateCI AND '2017-04-15' < ___Bookings.BOO_DateCO THEN "In House" 
  END AS BOO_BookingStatus 
FROM ___Bookings
WHERE
  ___Bookings.BOO_DateCI = '2017-04-15'
  OR ___Bookings.BOO_DateCO = '2017-04-15'
  OR ('2017-04-15' > ___Bookings.BOO_DateCI AND '2017-04-15' < ___Bookings.BOO_DateCO);

方法3：使用CTE/子查询（可读性最优） #

如果你的CASE逻辑比较复杂，或者你想让代码结构更清晰，可以先用CTE（公共表表达式）或者子查询生成包含BOO_BookingStatus的中间结果，再从中过滤非NULL的行：

CTE版本（适用于PostgreSQL、SQL Server、MySQL 8.0+等） #

WITH BookingsWithStatus AS (
  SELECT *, 
    CASE 
      WHEN ___Bookings.BOO_DateCI = '2017-04-15' THEN "In Arrival" 
      WHEN ___Bookings.BOO_DateCO = '2017-04-15' THEN "In Departure" 
      WHEN '2017-04-15' > ___Bookings.BOO_DateCI AND '2017-04-15' < ___Bookings.BOO_DateCO THEN "In House" 
    END AS BOO_BookingStatus 
  FROM ___Bookings
)
SELECT * FROM BookingsWithStatus
WHERE BOO_BookingStatus IS NOT NULL;

子查询版本（兼容性更好） #

SELECT *
FROM (
  SELECT *, 
    CASE 
      WHEN ___Bookings.BOO_DateCI = '2017-04-15' THEN "In Arrival" 
      WHEN ___Bookings.BOO_DateCO = '2017-04-15' THEN "In Departure" 
      WHEN '2017-04-15' > ___Bookings.BOO_DateCI AND '2017-04-15' < ___Bookings.BOO_DateCO THEN "In House" 
    END AS BOO_BookingStatus 
  FROM ___Bookings
) AS BookingsWithStatus
WHERE BOO_BookingStatus IS NOT NULL;

选择建议 #

• 优先用方法1，代码最简洁，大部分现代数据库都支持；
• 如果追求性能或者数据库不支持HAVING用别名，选方法2的方式B；
• 当CASE逻辑复杂时，方法3的可读性最好，便于后续维护。

内容的提问来源于stack exchange，提问作者user4440254