求助：MySQL条件不匹配时返回移除sender_id条件的结果集

核心需求 #

当原查询（包含sender_id = 'TEXT'）有结果时返回该结果；无结果时自动移除sender_id条件，返回仅满足user_id=1、is_enabled=1、country='IN'的记录。

解决方案一：EXISTS 动态判断（推荐） #

直接在WHERE子句中通过EXISTS判断是否存在满足完整条件的记录，动态切换过滤规则：

SELECT country, price, cost
FROM table1
WHERE 
    user_id = 1 AND is_enabled = 1 AND country = 'IN'
    AND (
        -- 存在完整条件的记录时，强制要求sender_id匹配
        (EXISTS (SELECT 1 FROM table1 WHERE user_id = 1 AND is_enabled = 1 AND country = 'IN' AND sender_id = 'TEXT') AND sender_id = 'TEXT')
        -- 无完整条件记录时，忽略sender_id限制
        OR NOT EXISTS (SELECT 1 FROM table1 WHERE user_id = 1 AND is_enabled = 1 AND country = 'IN' AND sender_id = 'TEXT')
    );

解决方案二：UNION ALL 结合优先级排序 #

通过给不同条件的结果设置优先级，优先返回完整条件的结果，无结果时返回降级后的结果：

SELECT country, price, cost
FROM (
    -- 优先级1：完整条件的结果
    SELECT country, price, cost, 1 AS priority
    FROM table1
    WHERE user_id = 1 AND is_enabled = 1 AND country = 'IN' AND sender_id = 'TEXT'
    UNION ALL
    -- 优先级2：降级条件的结果
    SELECT country, price, cost, 2 AS priority
    FROM table1
    WHERE user_id = 1 AND is_enabled = 1 AND country = 'IN'
) AS temp_result
-- 先按优先级排序，确保完整结果优先
ORDER BY priority
-- 只保留最高优先级的所有记录
LIMIT (
    -- 如果有完整条件的记录，取其数量；否则取降级条件的记录数量
    SELECT IF(COUNT(*) > 0, COUNT(*), (SELECT COUNT(*) FROM table1 WHERE user_id = 1 AND is_enabled = 1 AND country = 'IN'))
    FROM table1
    WHERE user_id = 1 AND is_enabled = 1 AND country = 'IN' AND sender_id = 'TEXT'
);

为什么 CASE WHEN 没生效？ #

大概率是你把CASE WHEN用在了SELECT字段列表中，而非WHERE条件里。如果想尝试用CASE WHEN实现，可以这么写：

SELECT country, price, cost
FROM table1
WHERE 
    user_id = 1 AND is_enabled = 1 AND country = 'IN'
    AND sender_id = CASE 
        WHEN EXISTS (SELECT 1 FROM table1 WHERE user_id = 1 AND is_enabled = 1 AND country = 'IN' AND sender_id = 'TEXT') THEN 'TEXT'
        ELSE sender_id -- 无匹配时，等于自身即取消该条件限制
    END;

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