SQL分组Action与Strength后，如何判断每周各天是否存在对应记录？

针对需求中按Action和Strength分组，生成周日到周六的列并标记Y/N的场景，以下两种无需Pivot的方案均可实现：

方案1：DISTINCT分组+EXISTS子查询判断 #

先提取所有唯一的Action+Strength组合，再针对每个日期用子查询判断是否存在对应记录：

SELECT
    Action,
    Strength,
    CASE WHEN EXISTS (SELECT 1 FROM Abilities a2 WHERE a2.Action = a1.Action AND a2.Strength = a1.Strength AND a2.DayOfWeek = 'Sunday') THEN 'Y' ELSE 'N' END AS Sunday,
    CASE WHEN EXISTS (SELECT 1 FROM Abilities a2 WHERE a2.Action = a1.Action AND a2.Strength = a1.Strength AND a2.DayOfWeek = 'Monday') THEN 'Y' ELSE 'N' END AS Monday,
    CASE WHEN EXISTS (SELECT 1 FROM Abilities a2 WHERE a2.Action = a1.Action AND a2.Strength = a1.Strength AND a2.DayOfWeek = 'Tuesday') THEN 'Y' ELSE 'N' END AS Tuesday,
    CASE WHEN EXISTS (SELECT 1 FROM Abilities a2 WHERE a2.Action = a1.Action AND a2.Strength = a1.Strength AND a2.DayOfWeek = 'Wednesday') THEN 'Y' ELSE 'N' END AS Wednesday,
    CASE WHEN EXISTS (SELECT 1 FROM Abilities a2 WHERE a2.Action = a1.Action AND a2.Strength = a1.Strength AND a2.DayOfWeek = 'Thursday') THEN 'Y' ELSE 'N' END AS Thursday,
    CASE WHEN EXISTS (SELECT 1 FROM Abilities a2 WHERE a2.Action = a1.Action AND a2.Strength = a1.Strength AND a2.DayOfWeek = 'Friday') THEN 'Y' ELSE 'N' END AS Friday,
    CASE WHEN EXISTS (SELECT 1 FROM Abilities a2 WHERE a2.Action = a1.Action AND a2.Strength = a1.Strength AND a2.DayOfWeek = 'Saturday') THEN 'Y' ELSE 'N' END AS Saturday
FROM (
    SELECT DISTINCT Action, Strength FROM Abilities
) a1
ORDER BY Action, Strength;

逻辑说明：子查询先获取所有不重复的分组组合，外层通过EXISTS快速判断每组在对应日期是否有匹配记录，存在则返回Y，否则N。该写法兼容绝大多数关系型数据库，逻辑清晰易懂。

方案2：GROUP BY+条件聚合（MAX(CASE...)） #

直接对原表按Action和Strength分组，用条件聚合标记每个日期的存在状态：

SELECT
    Action,
    Strength,
    MAX(CASE WHEN DayOfWeek = 'Sunday' THEN 'Y' ELSE 'N' END) AS Sunday,
    MAX(CASE WHEN DayOfWeek = 'Monday' THEN 'Y' ELSE 'N' END) AS Monday,
    MAX(CASE WHEN DayOfWeek = 'Tuesday' THEN 'Y' ELSE 'N' END) AS Tuesday,
    MAX(CASE WHEN DayOfWeek = 'Wednesday' THEN 'Y' ELSE 'N' END) AS Wednesday,
    MAX(CASE WHEN DayOfWeek = 'Thursday' THEN 'Y' ELSE 'N' END) AS Thursday,
    MAX(CASE WHEN DayOfWeek = 'Friday' THEN 'Y' ELSE 'N' END) AS Friday,
    MAX(CASE WHEN DayOfWeek = 'Saturday' THEN 'Y' ELSE 'N' END) AS Saturday
FROM Abilities
GROUP BY Action, Strength
ORDER BY Action, Strength;

逻辑说明：对每个分组，用CASE WHEN将对应日期的记录标记为Y，其他为N，再通过MAX聚合保留该组的Y（只要有一条记录匹配日期，聚合结果就是Y）。该写法仅需扫描一次原表，性能更优，代码也更简洁。

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