SQL优先级列表中间值查询：结果不符问题排查

我们公司多个组织关联了带独立priority_level的分组分类（group categories），已通过SQL查询得到优先级最低（MIN）的primary_item_category和优先级最高（MAX）的third_item_category。但部分组织的优先级层级为1-3级，在获取中间的second_item_category时遇到问题：

• 原查询返回second_item_category为nil
• 改用ROW_NUMBER()窗口函数的查询返回错误的org_group2，而非预期的org_group3

原查询片段 #

(             
  SELECT g.name             
  FROM groups g             
  INNER JOIN group_categories gc ON gc.id = g.group_category_id             
  INNER JOIN item_groups ig ON ig.group_id = g.id AND ig.item_id = i1."id"             
  WHERE gc.priority_level = 
  (               
     SELECT MIN(gc_inner.priority_level)               
     FROM group_categories gc_inner               
     INNER JOIN item_groups ig_inner ON ig_inner.group_id = g.id 
     AND ig_inner.item_id = i1."id"               
     WHERE gc_inner.id = g.group_category_id AND gc_inner.type = 'item'             
  )                  
  AND gc.type = 'item'             
  LIMIT 1           
) AS "primary_item_category",             
(             
  SELECT g.name             
  FROM groups g             
  INNER JOIN group_categories gc ON gc.id = g.group_category_id             
  INNER JOIN item_groups ig ON ig.group_id = g.id 
  AND ig.item_id = i1."id"             
  WHERE gc.priority_level = 
  (               
    SELECT MIN(gc_inner.priority_level)               
    FROM group_categories gc_inner               
    INNER JOIN item_groups ig_inner ON ig_inner.group_id = g.id 
    AND ig_inner.item_id = i1."id"               
    AND gc_inner.priority_level > 
    (                 
      SELECT MIN(gc_inner2.priority_level)                 
      FROM group_categories gc_inner2                 
      INNER JOIN item_groups ig_inner2 ON ig_inner2.group_id = g.id 
      AND ig_inner2.item_id = i1."id"               
    )
  )             
  AND gc.type = 'item'             
  LIMIT 1           
) AS "second_item_category", 
(             
  SELECT g.name             
  FROM groups g             
  INNER JOIN group_categories gc ON gc.id = g.group_category_id             
  INNER JOIN item_groups ig ON ig.group_id = g.id 
  AND ig.item_id = i1."id"             
  WHERE gc.priority_level = 
  (               
    SELECT MAX(gc_inner.priority_level)               
    FROM group_categories gc_inner               
    INNER JOIN item_groups ig_inner ON ig_inner.group_id = g.id 
    AND ig_inner.item_id = i1."id"
  )             
  AND gc.type = 'item'             
  LIMIT 1           
) AS "third_item_category",
...

测试数据 #

org = insert(:organization)
group_category1 = insert(:group_category, type: "organization", priority_level: 1, organization: org, name: "OrganizationCategory")    
group_category2 = insert(:group_category, type: "item", priority_level: 2, organization: org, name: "PrimaryItemCategory")    
group_category3 = insert(:group_category, type: "item", priority_level: 3, organization: org, name: "SecondItemCategory")    
group_category4 = insert(:group_category, type: "item", priority_level: 4, organization: org, name: "ThirdItemCategory")    

org_group1 = Factory.insert(:group, group_category: group_category1, name: "org_group1")   
org_group2 = Factory.insert(:group, group_category: group_category2, name: "org_group2")   
org_group3 = Factory.insert(:group, group_category: group_category3, name: "org_group3")   
org_group4 = Factory.insert(:group, group_category: group_category4, name: "org_group4")

修改后的second_item_category查询片段 #

(
  SELECT g.name             
  FROM groups g             
  INNER JOIN group_categories gc ON gc.id = g.group_category_id             
  INNER JOIN item_groups ig ON ig.group_id = g.id AND ig.item_id = i1."id"             
  WHERE gc.priority_level = 
  (               
    SELECT priority_level               
    FROM 
    (                 
      SELECT 
        gc_inner.priority_level, 
        ROW_NUMBER() OVER (ORDER BY gc_inner.priority_level) AS rn,                       
        COUNT(*) OVER () AS cnt                 
      FROM group_categories gc_inner                 
      INNER JOIN item_groups ig_inner ON ig_inner.group_id = g.id AND ig_inner.item_id = i1."id"                 
      WHERE gc_inner.id = g.group_category_id AND gc_inner.type = 'item'               
    ) subquery               
    WHERE rn = (cnt + 1) / 2             
  )             
  AND gc.type = 'item'             
  LIMIT 1           
) AS "second_item_category",

问题原因分析 #

原查询和修改后的查询都存在同一个逻辑错误：内层子查询关联item_groups时使用了ig_inner.group_id = g.id，这导致子查询仅针对当前外层groups行对应的单个分组处理，而非关联到该item的所有分组分类。

以测试数据为例，外层遍历groups时，当取到org_group2（对应priority_level=2），内层子查询只能看到这个分组的category，自然无法找到中间优先级的分类；而窗口函数的排序和计数也只基于单个分组的category，导致计算出的中间值错误。

修正方案 #

先通过CTE提前获取每个item关联的所有type='item'的分组分类，按优先级排序后标记序号和总数量，再在主查询中根据序号提取对应分类：

WITH item_category_groups AS (
  SELECT
    ig.item_id,
    g.name AS group_name,
    gc.priority_level,
    -- 按优先级升序排序，序号从1开始
    ROW_NUMBER() OVER (PARTITION BY ig.item_id ORDER BY gc.priority_level) AS rn,
    -- 统计当前item对应的有效分类总数
    COUNT(*) OVER (PARTITION BY ig.item_id) AS total_cnt
  FROM item_groups ig
  JOIN groups g ON ig.group_id = g.id
  JOIN group_categories gc ON g.group_category_id = gc.id
  WHERE gc.type = 'item'
)
SELECT
  -- 其他主查询字段
  (SELECT group_name FROM item_category_groups WHERE item_id = i1.id AND rn = 1) AS primary_item_category,
  -- 取中间序号：总数为奇数时取中间，偶数时取上中位数（可根据需求调整）
  (SELECT group_name FROM item_category_groups WHERE item_id = i1.id AND rn = (total_cnt + 1) / 2) AS second_item_category,
  (SELECT group_name FROM item_category_groups WHERE item_id = i1.id AND rn = total_cnt) AS third_item_category
-- 主查询的表关联和条件
FROM ... i1 ...

这个方案先统一处理每个item的所有有效分类，避免了原查询中内层关联范围错误的问题，确保能正确获取到中间优先级的分类。

内容的提问来源于stack exchange，提问作者Sandy S L