MySQL查询需求：展示ID与CID关联组（含反向）的最新行

这个需求的核心是要把双向关联的ID-CID对（比如id=1-cid=2和id=2-cid=1）归为同一个组，然后在每个组里筛选出最新的那条记录。我之前处理过类似场景，用窗口函数结合分组标识就能轻松实现，具体步骤如下：

思路拆解 #

1. 生成统一的组标识：用LEAST()和GREATEST()函数，把每个(id, cid)对转换成「较小值+较大值」的固定组合，这样双向的对就会被归为同一个组。
2. 组内排序并标记排名：用RANK()窗口函数，以生成的组标识为分区依据，按date字段降序排序，给每条记录标记在组内的排名。
3. 筛选最新记录：取每个组内排名为1的记录，就是该组的最新行。

完整SQL代码 #

假设你的表名为your_table，可以用以下SQL：

WITH ranked_records AS (
    SELECT
        *,
        -- 生成统一的组标识：最小id和最大id的组合
        LEAST(id, cid) AS group_min,
        GREATEST(id, cid) AS group_max,
        -- 按组分区，按date降序排名，最新的记录rank=1
        RANK() OVER (
            PARTITION BY LEAST(id, cid), GREATEST(id, cid)
            ORDER BY date DESC
        ) AS record_rank
    FROM your_table
)
SELECT id, cid, number, value, date
FROM ranked_records
WHERE record_rank = 1;

代码解释 #

• LEAST(id, cid)和GREATEST(id, cid)：确保(1,2)和(2,1)得到相同的group_min=1和group_max=2，从而被划到同一组。
• RANK() OVER (...)：在每个组内，根据date从新到旧排序，最新的记录会得到record_rank=1。
• 最后筛选record_rank=1的记录，就是每个组的最新行。

测试你的示例数据，这个SQL会准确返回你要的结果：

+----+-------+--------+--------------+---------------------+
| id | cid | number | value | date |
+----+-------+--------+--------------+---------------------+
| 1 | 2 | 57 | one to two | 2020-04-19 11:27:02 |
| 9 | 2 | 58 | nine to two | 2020-04-19 11:28:01 |
+----+-------+--------+--------------+---------------------+

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