带过滤分页排序的多表关联查询优化咨询：简化现有SQL实现

针对需求——展示至少有一个设备token匹配BIZ%的用户及其所有设备，支持过滤、分页、排序，以下是两种简化原SQL的优化方案，可减少显式ORDER BY的次数：

方案1：利用窗口函数合并用户筛选与分页 #

通过ROW_NUMBER()窗口函数直接对符合条件的用户按最大token排序并编号，筛选出前5个用户后关联设备，写法更紧凑：

SELECT
  a.id,
  d.token
FROM (
  SELECT
    a.id,
    ROW_NUMBER() OVER(ORDER BY MAX(d.token) DESC) AS rn
  FROM account a
  JOIN device d ON d.account_id = a.id
  WHERE d.token LIKE 'BIZ%'
  GROUP BY a.id
) a
JOIN device d ON d.account_id = a.id
WHERE a.rn <= 5
ORDER BY d.token DESC;

这个方案将用户的排序、分页逻辑合并到子查询中，避免了原CTE中单独的ORDER BY + OFFSET/FETCH结构，仅在最终结果中保留一次设备排序。

方案2：关联用户排序键统一排序逻辑 #

先获取目标用户的ID及其排序依据（最大token）并分页，再关联设备时结合用户排序键和设备token排序，保证结果顺序与需求一致：

SELECT
  user_info.id,
  d.token
FROM (
  SELECT
    a.id,
    MAX(d.token) AS max_token
  FROM account a
  JOIN device d ON d.account_id = a.id
  WHERE d.token LIKE 'BIZ%'
  GROUP BY a.id
  ORDER BY max_token DESC
  OFFSET 0 ROWS FETCH FIRST 5 ROWS ONLY
) user_info
JOIN device d ON d.account_id = user_info.id
ORDER BY user_info.max_token DESC, d.token DESC;

此方案保留用户分页时的排序，但主查询的排序结合了用户的max_token，确保用户组的顺序符合要求，同时设备在组内按token降序排列，相比原SQL去掉了CTE结构，逻辑更直观。

方案优势 #

两种方案均简化了原SQL的结构，减少了冗余的排序声明，同时完全满足过滤、分页、排序的需求，且性能上与原SQL相当（若数据库对窗口函数和子查询的优化到位）。

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