如何按列优先级查询非空记录：优先First，其次Second，最后Third

示例表结构 #

需求规则 #

• 按 First → Second → Third 的优先级筛选记录
• 若 First 列存在非空值，仅返回该列非空的记录
• 若 First 列全为空，则检查 Second 列，返回该列非空的记录
• 若 Second 列也全为空，则返回 Third 列非空的记录

解决方案SQL #

SELECT *
FROM your_table
WHERE 
    -- 优先级1：存在First非空时，取First非空记录
    EXISTS (SELECT 1 FROM your_table WHERE First IS NOT NULL AND First <> '')
    AND First IS NOT NULL AND First <> ''
    -- 优先级2：First全空但存在Second非空时，取Second非空记录
    OR NOT EXISTS (SELECT 1 FROM your_table WHERE First IS NOT NULL AND First <> '')
    AND EXISTS (SELECT 1 FROM your_table WHERE Second IS NOT NULL AND Second <> '')
    AND Second IS NOT NULL AND Second <> ''
    -- 优先级3：First和Second都全空时，取Third非空记录
    OR NOT EXISTS (SELECT 1 FROM your_table WHERE First IS NOT NULL AND First <> '')
    AND NOT EXISTS (SELECT 1 FROM your_table WHERE Second IS NOT NULL AND Second <> '')
    AND Third IS NOT NULL AND Third <> '';

逻辑说明 #

针对示例表，First 和 Second 列均无任何非空值，因此触发优先级3的筛选条件，最终返回 Third 列非空的ID为1和5的记录，完全符合需求。

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