带条件查询的嵌套GROUP BY SQL实现方案咨询

给定3张业务表，结构与测试数据如下：

表a #

主键为id，存储名称字段name：

id  name
2   A
3   B

表b #

主键为id，a字段是关联表a主键的外键，b字段存储字符串内容：

id  a        b                
3   2        Asd Ter Gsdt      
4   2        Gsd Gsdt Gsda
5   2        Asd Gsd
6   3        Uty Kggs
7   3        Tyud Hffddf

表c #

主键为id，a字段是关联表b主键的外键，b字段存储单字符内容：

id  a           b
6   3           d       
7   3           a      
8   3           g
9   3           h
10  4           j
11  5           y
12  5           s
13  6           d
14  6           h

要求编写带条件查询的嵌套GROUP BY语句，得到如下预期输出：

a     b                 c           d
A     2019-04-06        3           a
B     2019-04-06        6           b

逻辑拆解 #

先梳理表关联关系和结果字段的生成规则：

• 结果列a：取自表a的name字段，对应a表id=2、3的两条名称记录A、B
• 结果列b：查询指定的固定日期值2019-04-06
• 结果列c：表b中关联同一a.id的所有记录里的最小主键id，需要对b表按外键a分组后通过MIN()聚合计算得到
• 结果列d：对应a列值的小写形式
• 筛选规则：仅保留b表聚合得到的最小id在表c中存在关联记录（即c.a等于该最小id）的分组

嵌套GROUP BY写法 #

采用两层分组逻辑实现，内层先对b表做分组聚合计算核心关联字段，外层关联其他表后做二次分组去重，SQL如下：

SELECT
  a.name AS a,
  b_sub.b_date AS b,
  b_sub.min_b_id AS c,
  LOWER(a.name) AS d
FROM a
INNER JOIN (
  -- 第一层分组：按关联a的外键对b表分组，计算每组最小id，返回固定日期
  SELECT
    a AS rel_a_id,
    MIN(id) AS min_b_id,
    '2019-04-06' AS b_date
  FROM b
  GROUP BY a
) b_sub ON a.id = b_sub.rel_a_id
-- 加筛选条件：匹配c表的关联存在性
WHERE EXISTS (
  SELECT 1 FROM c WHERE c.a = b_sub.min_b_id
)
-- 第二层分组：保证最终结果每个a名称只返回一行，避免关联产生重复数据
GROUP BY a.id, a.name, b_sub.b_date, b_sub.min_b_id;

结果验证 #

将测试数据代入上述SQL执行，返回结果与预期完全一致：

a     b                 c           d
A     2019-04-06        3           a
B     2019-04-06        6           b

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