按sec_id分组限数量的A、B模型关联SQL查询需求

我来帮你搞定这个SQL查询问题，你的需求核心是从指定sec_id的分组里最多取2条A表数据，再关联对应B表的第一条记录，咱们一步步来实现：

先说说你现有SQL的问题 #

你当前的查询存在几个关键问题：

• JOIN条件写错了：B.sec_id=A.id应该是B.a_id=A.id（毕竟B表的a_id才是指向A表的外键）
• 完全没实现按sec_id分组取最多2条A数据的逻辑
• 也没处理提取每个A对应的第一条B数据的需求

正确的SQL实现方案 #

我们可以用ROW_NUMBER()窗口函数分别处理A表的分组筛选和B表的第一条记录提取，逻辑清晰又高效：

WITH filtered_A AS (
    -- 第一步：筛选指定sec_id，每个sec_id最多保留2条A记录（按id升序排序）
    SELECT 
        id,
        name AS name_A,
        sec_id,
        ROW_NUMBER() OVER (PARTITION BY sec_id ORDER BY id) AS row_num
    FROM A
    WHERE sec_id IN (5, 7)
),
first_B AS (
    -- 第二步：提取每个a_id对应的第一条B记录（按id升序，也就是最早的那条）
    SELECT 
        a_id,
        name AS name_B,
        ROW_NUMBER() OVER (PARTITION BY a_id ORDER BY id) AS row_num
    FROM B
)
-- 关联两个子查询，拿到最终结果
SELECT 
    fa.id,
    fa.name_A,
    fa.sec_id,
    fb.name_B
FROM filtered_A fa
JOIN first_B fb ON fa.id = fb.a_id
WHERE fa.row_num <= 2 -- 每个sec_id最多取2条A数据
  AND fb.row_num = 1 -- 只保留每个A对应的第一条B数据
ORDER BY fa.id;

逻辑拆解说明 #

1. filtered_A子查询：

   • 用PARTITION BY sec_id把A表按sec_id分组
   • ORDER BY id保证每个分组内按A表的id从小到大排序
   • ROW_NUMBER()给每个分组内的记录编序号，后续用row_num <=2筛选出每个sec_id最多2条数据

2. first_B子查询：

   • 用PARTITION BY a_id按A表的id分组（因为B的a_id关联A的id）
   • ORDER BY id确保取到B表中每个a_id对应的第一条记录（id最小的那条）
   • 通过row_num =1精准筛选出每个A对应的第一条B记录

3. 最终关联查询：

   • 把筛选后的A表和提取好第一条记录的B表通过fa.id = fb.a_id关联起来
   • 最后按id排序，就能得到你想要的输出格式

执行这个SQL后，会输出和你期望完全一致的结果：

id | name_A | sec_id | name_B
1  | char   | 5      | char1-1
2  | char   | 5      | char2-1
3  | char   | 7      | char3-1

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