如何在SQL中基于关联表日期实现分组排序并生成DENSE_RANK？

核心思路 #

要实现你需要的效果，需分两步处理：

• 先给每个m_id计算出对应的最大s_date，作为分组排序的核心依据
• 基于这个最大s_date生成DENSE_RANK，同时控制最终结果的排序规则，保证同一m_id连续、组内按s_date降序排列

修改后的SQL示例 #

假设你的原查询包含UNION和分页逻辑，修改后的代码如下：

SELECT *
FROM (
    SELECT 
        *,
        -- 标记当前记录所属m_id的最大s_date
        MAX(s_date) OVER (PARTITION BY m_id) AS max_m_s_date,
        -- 按每个m_id的最大s_date降序分配DENSE_RANK，同一m_id的rank值一致
        DENSE_RANK() OVER (ORDER BY MAX(s_date) OVER (PARTITION BY m_id) DESC, m_id) AS group_rank
    FROM (
        -- 替换为你实际的UNION查询逻辑
        SELECT m_id, s_date, col1, col2 FROM table_1
        UNION ALL
        SELECT m_id, s_date, col1, col2 FROM table_2
    ) AS union_data
) AS ranked_data
-- 最终排序：保证同m_id连续，组内按s_date降序
ORDER BY max_m_s_date DESC, m_id, s_date DESC
-- 保留原分页逻辑
OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY;

关键部分说明 #

1. MAX(s_date) OVER (PARTITION BY m_id)：
   给每条记录添加一个字段，值为该记录所属m_id对应的最大s_date，确保同一m_id的所有记录拥有相同的max_m_s_date值。

2. DENSE_RANK() OVER (ORDER BY MAX(s_date) OVER (PARTITION BY m_id) DESC, m_id)：
   以每个m_id的最大s_date降序作为排序依据生成rank，当多个m_id的最大s_date相同时，按m_id排序，确保同一m_id的所有记录rank值完全一致。

3. 外层ORDER BY规则：
   通过max_m_s_date DESC, m_id保证同一m_id的记录连续排列，最后加上s_date DESC实现组内按日期降序展示。

适配实际场景 #

只需将示例中union_data部分替换为你实际的UNION查询语句，调整字段列表为你需要的内容即可，分页逻辑保持原有设置不变。

内容的提问来源于stack exchange，提问作者T.S.