请求实现基于前后数值对test1表的查询技术方案

看来你需要查询test1表中每条记录的前后相邻数据，对吧？结合你的表结构，我给你几种实用的实现方案，你可以根据实际场景来选：

1. 基于自增ID关联前后记录 #

因为id是自增主键，只要没有手动插入非连续ID或者删除过记录，用ID关联是最高效的方式，直接匹配当前ID的前一个（id-1）和后一个（id+1）记录：

SELECT
    t.id,
    t.imei,
    t.lat,
    t.lng,
    t.ign,
    t.datetime,
    -- 前一条记录的字段
    prev.id AS prev_id,
    prev.lat AS prev_lat,
    prev.lng AS prev_lng,
    prev.ign AS prev_ign,
    prev.datetime AS prev_datetime,
    -- 后一条记录的字段
    next.id AS next_id,
    next.lat AS next_lat,
    next.lng AS next_lng,
    next.ign AS next_ign,
    next.datetime AS next_datetime
FROM test1 t
LEFT JOIN test1 prev ON prev.id = t.id - 1
LEFT JOIN test1 next ON next.id = t.id + 1
ORDER BY t.id;

用LEFT JOIN的好处是，即使是第一条或最后一条记录，也不会因为没有前后记录而被过滤掉，对应的前后字段会显示NULL。

2. 基于时间戳关联前后记录（更准确） #

如果存在ID不连续的情况（比如删过记录、手动插入了非连续ID），用datetime来关联前后记录会更符合时间上的先后逻辑。如果你的MySQL版本是8.0及以上，推荐用窗口函数LAG()和LEAD()，代码简洁性能也好：

SELECT
    id,
    imei,
    lat,
    lng,
    ign,
    datetime,
    -- 获取前一条记录的字段
    LAG(id) OVER (ORDER BY datetime) AS prev_id,
    LAG(lat) OVER (ORDER BY datetime) AS prev_lat,
    LAG(lng) OVER (ORDER BY datetime) AS prev_lng,
    LAG(ign) OVER (ORDER BY datetime) AS prev_ign,
    LAG(datetime) OVER (ORDER BY datetime) AS prev_datetime,
    -- 获取后一条记录的字段
    LEAD(id) OVER (ORDER BY datetime) AS next_id,
    LEAD(lat) OVER (ORDER BY datetime) AS next_lat,
    LEAD(lng) OVER (ORDER BY datetime) AS next_lng,
    LEAD(ign) OVER (ORDER BY datetime) AS next_ign,
    LEAD(datetime) OVER (ORDER BY datetime) AS next_datetime
FROM test1
ORDER BY datetime;

LAG()会取当前行之前的第1条数据，LEAD()取当前行之后的第1条数据，通过ORDER BY datetime来确保是按时间顺序排列的前后记录。

如果你的MySQL版本低于8.0，没法用窗口函数，可以用子查询实现（不过数据量大时性能会差一些）：

SELECT
    t.id,
    t.imei,
    t.lat,
    t.lng,
    t.ign,
    t.datetime,
    (SELECT id FROM test1 WHERE datetime < t.datetime ORDER BY datetime DESC LIMIT 1) AS prev_id,
    (SELECT lat FROM test1 WHERE datetime < t.datetime ORDER BY datetime DESC LIMIT 1) AS prev_lat,
    (SELECT lng FROM test1 WHERE datetime < t.datetime ORDER BY datetime DESC LIMIT 1) AS prev_lng,
    (SELECT ign FROM test1 WHERE datetime < t.datetime ORDER BY datetime DESC LIMIT 1) AS prev_ign,
    (SELECT datetime FROM test1 WHERE datetime < t.datetime ORDER BY datetime DESC LIMIT 1) AS prev_datetime,
    (SELECT id FROM test1 WHERE datetime > t.datetime ORDER BY datetime ASC LIMIT 1) AS next_id,
    (SELECT lat FROM test1 WHERE datetime > t.datetime ORDER BY datetime ASC LIMIT 1) AS next_lat,
    (SELECT lng FROM test1 WHERE datetime > t.datetime ORDER BY datetime ASC LIMIT 1) AS next_lng,
    (SELECT ign FROM test1 WHERE datetime > t.datetime ORDER BY datetime ASC LIMIT 1) AS next_ign,
    (SELECT datetime FROM test1 WHERE datetime > t.datetime ORDER BY datetime ASC LIMIT 1) AS next_datetime
FROM test1 t
ORDER BY t.datetime;

3. 进阶：筛选状态变化的前后记录 #

如果你是想找出比如ign状态变化（从0变1或1变0）的前后记录，可以用窗口函数配合筛选条件实现：

-- 找出ign状态发生变化的记录及之前的状态
WITH ordered_records AS (
    SELECT
        id,
        imei,
        lat,
        lng,
        ign,
        datetime,
        LAG(ign) OVER (ORDER BY datetime) AS prev_ign
    FROM test1
)
SELECT
    id,
    imei,
    lat,
    lng,
    ign,
    datetime,
    prev_ign
FROM ordered_records
WHERE prev_ign IS NOT NULL AND ign != prev_ign;

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