如何调整SQL的CASE语句及关联逻辑获取预期输出？

针对Room与Clean表关联后出现重复行，需按RoomId保留最高优先级状态（存在满足条件的记录则显示clear，否则显示unclear）的问题，提供以下几种可行方案：

方案一：利用MAX()聚合函数取最高优先级状态 #

字符串'clear'的字典序大于'unclear'，通过MAX()函数可在分组后直接保留每组的最高优先级状态：

SELECT 
    r.RoomId,
    MAX(
        CASE 
            WHEN r.State = 1 THEN 'clear'
            WHEN r.Date BETWEEN c.Start-Date AND c.End-Date THEN 'clear'
            ELSE 'unclear'
        END
    ) AS State
FROM Room r
JOIN Clean c ON r.RoomId = c.RoomId
GROUP BY r.RoomId;

方案二：使用EXISTS子查询（逻辑更直观） #

直接通过子查询判断当前房间是否存在符合条件的清洁记录，无需先关联产生重复行，性能更优：

SELECT 
    r.RoomId,
    CASE
        WHEN r.State = 1 THEN 'clear'
        WHEN EXISTS (
            SELECT 1 
            FROM Clean c 
            WHERE c.RoomId = r.RoomId 
              AND r.Date BETWEEN c.Start-Date AND c.End-Date
        ) THEN 'clear'
        ELSE 'unclear'
    END AS State
FROM Room r;

方案三：条件聚合统计满足条件的记录数 #

通过SUM()统计满足清洁时间条件的记录数，只要数量大于0则判定为clear：

SELECT 
    r.RoomId,
    CASE
        WHEN r.State = 1 OR SUM(
            CASE WHEN r.Date BETWEEN c.Start-Date AND c.End-Date THEN 1 ELSE 0 END
        ) > 0 THEN 'clear'
        ELSE 'unclear'
    END AS State
FROM Room r
JOIN Clean c ON r.RoomId = c.RoomId
GROUP BY r.RoomId, r.State;

以上三种方案均可实现每个RoomId仅返回一行，且优先显示clear状态的需求。

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