MySQL通过当前排序字段获取相邻数据项及同表计算

通过当前排序字段获取相邻数据项

1.业务场景

在实际应用中，经常需要在一个弹窗页面展示某条数据的详细字段值。由于部分字段内容较长，用户往往希望能左右切换查看上一条和下一条数据。前提条件通常包括：

• 存在一个明确的排序依据（如主键 id）。
• 需要在前端交互时高效加载相邻数据。

2.思路解析

核心逻辑是通过 SQL 语句分别查询小于当前 ID 的最大值（上一项）和大于当前 ID 的最小值（下一项），然后将两者合并返回。在前端逻辑上，可在展示当前项时预加载相邻数据缓存，切换时直接读取，避免每次切换都发起数据库请求。

3.操作步骤：使用 UNION 联合查询

以下示例以查询 id = 40 的前后相邻数据为例。该方案适用于大多数基于单一数值字段排序的场景。

第一步：编写联合查询 SQL

利用 UNION 将两个子查询结果合并。第一个子查询筛选小于当前 ID 的记录并按 ID 降序排列取第一条（即紧邻的前一项）；第二个子查询筛选大于当前 ID 的记录按 ID 升序排列取第一条（即紧邻的后一项）。

(SELECT * FROM [表名] WHERE id < 40 ORDER BY id DESC LIMIT 1)
UNION
(SELECT * FROM [表名] WHERE id > 40 ORDER BY id ASC LIMIT 1)

第二步：应用至前端接口

后端执行上述 SQL 并返回包含最多两条记录的 JSON 数据。前端根据业务需求判断返回的数据是否为“上一条”或“下一条”，并填充到相应的展示区域。

同表相邻数据查询或计算

在处理时序性数据（如用户的订单记录、日志流水）时，不仅需要获取相邻行，还需要对相邻行之间的差异进行计算（例如计算两次访问的时间间隔）。以下以电商场景中“计算用户首单与第二单的时间间隔”为例，演示如何在一个表中完成此类复杂查询。

场景数据准备

假设存在一张销售订单表 sales_flat_order，结构如下：

案例一：查询用户首单和第二单的时间间隔

目标：找出每个用户的第二笔订单及其对应的第一笔订单，并计算天数差。

第一步：确定第二笔订单的身份

对于某条订单记录 m，如果在其之前（时间更早）只有一笔其他订单记录，那么 m 就是该用户的第二单。可以通过子查询统计满足条件的行数来实现：

WHERE (
    SELECT count(*)
    FROM sales_flat_order n
    WHERE m.customer_id = n.customer_id
        AND m.created_at > n.created_at
) = 1

第二步：通过 LEFT JOIN 关联首单信息

既然确定了 m 是第二单，我们需要找到比 m 更早的那一笔订单（即首单）。这里再次使用自连接，但只需找到时间早于 m 且属于同一客户的任意一条即可（因为已限定 m 为第二单，所以比它早的必然只有首单）：

LEFT JOIN sales_flat_order sfo 
ON m.customer_id = sfo.customer_id 
AND sfo.created_at < m.created_at

第三步：计算时间差

利用 unix_timestamp() 函数将时间戳转为整数相减，再除以 86400 得到天数。

完整 SQL 示例：

SELECT
    m.customer_id,
    sfo.created_at as '首单时间',
    m.created_at as '第二单时间',
    (unix_timestamp(m.created_at) - unix_timestamp(sfo.created_at))/86400 as '两单相差天数'
FROM
    sales_flat_order m 
LEFT JOIN 
    sales_flat_order sfo ON m.customer_id = sfo.customer_id AND sfo.created_at < m.created_at
WHERE
    (
        SELECT count(*)
        FROM sales_flat_order n
        WHERE m.customer_id = n.customer_id
            AND m.created_at > n.created_at
    ) = 1
GROUP BY m.customer_id, m.created_at, sfo.created_at;

预期结果：

案例二：查询任意相连两笔订单的时间间隔

如果需要计算任意相邻两单（如第二单与第三单）的间隔，逻辑略有不同。此时需统计该记录之前有 N 条记录。

第一步：定位特定位置的订单

若要查第二单与第三单的间隔，首先筛选出“第三单”。即在该客户的所有订单中，比当前记录时间早的记录数量为 2：

WHERE (
    SELECT count(*)
    FROM sales_flat_order n
    WHERE m.customer_id = n.customer_id
        AND m.created_at > n.created_at
) = 2

第二步：获取前一单的时间

此时通过 LEFT JOIN 会匹配到所有比 m 早的两笔订单（首单和二单）。为了精确获取紧邻的前一笔（即第二单），可以使用 GROUP_CONCAT 配合 SUBSTRING_INDEX 函数：

1. 将关联到的前序订单时间按降序排列并用逗号连接。
2. 取连接字符串的第一个元素，即为紧邻的前一笔订单时间。

完整 SQL 示例：

SELECT
    m.customer_id,
    m.created_at as '后一单时间',
    SUBSTRING_INDEX(
        GROUP_CONCAT(sfo.created_at ORDER BY sfo.created_at DESC),
        ',',
        1
    ) as '前一单时间',
    (unix_timestamp(m.created_at) - unix_timestamp(
        SUBSTRING_INDEX(
            GROUP_CONCAT(sfo.created_at ORDER BY sfo.created_at DESC),
            ',',
            1
        )
    ))/86400 as '两单相差天数'
FROM
    sales_flat_order m 
LEFT JOIN 
    sales_flat_order sfo ON m.customer_id = sfo.customer_id AND sfo.created_at < m.created_at
WHERE
    (
        SELECT count(*)
        FROM sales_flat_order n
        WHERE m.customer_id = n.customer_id
            AND m.created_at > n.created_at
    ) = 2
GROUP BY m.customer_id, m.created_at;

结果说明：

此查询仅返回那些至少有 3 笔订单的用户的相关间隔数据。例如用户 1 的第三单与第二单间隔约为 0.37 天。

总结

以上介绍了两种 MySQL 中处理相邻数据的常见模式。简单的位置相邻推荐使用 UNION 结合 ORDER BY 和 LIMIT，效率高且易于理解。而同表内的逻辑相邻（基于分组和时序）则需要借助自连接、子查询计数以及聚合函数进行处理。开发者应根据实际业务复杂度选择最合适的方式。

以上就是MySQL如何通过当前排序字段获取相邻数据项及同表计算的详细内容，更多关于MySQL相邻查询的技巧与资料请关注本站其它相关文章！