跨平台SQL方案：按分组获取含自定义排序位的首行数据

背景 #

• 使用SQL Server 2019，优先采用跨平台ANSI SQL方案，禁止使用TOP子句（需兼容多数据库）。
• 父表tb_book关联子分类表tb_linked_categories，通过ordinal_position列排序。
• ordinal_position列值可重复（只需大于0）。

需求 #

为每个父项（parent_id）获取最小ordinal_position对应的默认分类：

1. 先筛选出该父项最小的ordinal_position值；
2. 若同一排序位有多行记录，取其中最小的category_id作为默认分类。

数据与DDL #

表tb_linked_categories的DDL如下：

CREATE TABLE [tb_linked_categories]
(
   [notes] NVARCHAR(200),
   [id] BIGINT NOT NULL CONSTRAINT [pk_tb_linked_categories] PRIMARY KEY,
   [ordinal_position] INT DEFAULT 1 NOT NULL,
   [category_id] BIGINT NOT NULL,
   [parent_id] INT NOT NULL
)

待解决问题 #

1. 是否存在无需子查询或CTE的单SQL查询方案？
2. 最高效的查询方案是什么？

现有方案 #

已实现基于CTE的可行方案，代码如下：

;WITH [cte] AS (
   SELECT
      'group by parent, ordinal position' [cte_dev_message]
      , [t].[parent_id]
      , [t].[ordinal_position]
      , MIN([t].[category_id]) [category_first_item_id]
      , COUNT([t].[id]) [category_count]
      , STRING_AGG([t].[category_id] ,',') [category_ids_csv]
   FROM [tb_linked_categories] [t]
   GROUP BY [t].[parent_id], [t].[ordinal_position] )
SELECT
   cte1.[parent_id], [category_first_item_id], [ordinal_position], [category_count]
FROM [cte] [cte1]
   INNER JOIN (
   SELECT [parent_id], MIN([ordinal_position]) [min_ordinal_position]
   FROM [cte]
   GROUP BY [parent_id] ) [mins] ON [cte1].[parent_id] = [mins].[parent_id]
WHERE [mins].[min_ordinal_position] = [cte1].[ordinal_position]
ORDER BY [cte1].[parent_id];

优化方案 #

问题1：无需子查询/CTE的单SQL方案 #

对于支持ANSI SQL QUALIFY子句的数据库（如PostgreSQL、BigQuery等），可以直接用QUALIFY过滤窗口函数结果，实现无嵌套的单SQL查询：

SELECT DISTINCT
  parent_id,
  MIN(category_id) OVER (PARTITION BY parent_id, ordinal_position) AS category_first_item_id,
  ordinal_position,
  COUNT(id) OVER (PARTITION BY parent_id, ordinal_position) AS category_count
FROM tb_linked_categories
QUALIFY ROW_NUMBER() OVER (PARTITION BY parent_id ORDER BY ordinal_position, category_id) = 1
ORDER BY parent_id;

但SQL Server 2019不支持QUALIFY，因此在该版本下无法完全避免子查询或CTE，不过可以用简化的子查询实现相同逻辑。

问题2：最高效的查询方案 #

最优方案是基于窗口函数+索引优化的实现，相比原CTE方案减少了一次分组操作，逻辑更简洁且性能更优：

WITH ranked_data AS (
  SELECT
    parent_id,
    ordinal_position,
    MIN(category_id) OVER (PARTITION BY parent_id, ordinal_position) AS category_first_item_id,
    COUNT(id) OVER (PARTITION BY parent_id, ordinal_position) AS category_count,
    ROW_NUMBER() OVER (PARTITION BY parent_id ORDER BY ordinal_position, category_id) AS rn
  FROM tb_linked_categories
)
SELECT
  parent_id,
  category_first_item_id,
  ordinal_position,
  category_count
FROM ranked_data
WHERE rn = 1
ORDER BY parent_id;

性能优化建议

为进一步提升查询效率，建议创建复合索引，让数据库直接按分组、排序逻辑读取数据，避免回表操作：

CREATE INDEX idx_linked_categories_parent_ordinal ON tb_linked_categories (parent_id, ordinal_position, category_id) INCLUDE (id);

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