如何编写SQL从关联表T1、T2提取指定条件的合并行数据？

现有两张关联表T1和T2，表结构及数据如下：

表T1 #

说明：Id为该表主键

表T2 #

说明：Id为该表主键，record_id为表T1中Id列的外键

查询需求 #

输出包含T1的Id、Level_type、State_type，以及以下两个字段的结果集：

• MaId：T2中c_type='M'且action_type='Ma'对应的c_Id
• ChId：T2中c_type='C'且action_type='A'对应的c_Id
  结果按Created_at降序排序，期望输出如下：

期望输出 #

提供两种实用的实现方案：

方案1：条件聚合（推荐） #

通过CASE WHEN结合聚合函数，按T1的主键分组直接提取目标字段：

SELECT
    t1.Id,
    t1.Level_type,
    t1.State_type,
    MAX(CASE WHEN t2.c_type = 'M' AND t2.action_type = 'Ma' THEN t2.c_Id END) AS MaId,
    MAX(CASE WHEN t2.c_type = 'C' AND t2.action_type = 'A' THEN t2.c_Id END) AS ChId
FROM T1 t1
LEFT JOIN T2 t2 ON t1.Id = t2.record_id
GROUP BY t1.Id, t1.Level_type, t1.State_type
ORDER BY MAX(t2.Created_at) DESC;

方案2：子查询关联 #

分别筛选出MaId和ChId的子结果集，再与T1关联：

SELECT
    t1.Id,
    t1.Level_type,
    t1.State_type,
    ma.MaId,
    ch.ChId
FROM T1 t1
LEFT JOIN (
    SELECT record_id, c_Id AS MaId
    FROM T2
    WHERE c_type = 'M' AND action_type = 'Ma'
) ma ON t1.Id = ma.record_id
LEFT JOIN (
    SELECT record_id, c_Id AS ChId
    FROM T2
    WHERE c_type = 'C' AND action_type = 'A'
) ch ON t1.Id = ch.record_id
ORDER BY (SELECT MAX(Created_at) FROM T2 WHERE record_id = t1.Id) DESC;

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