本文介绍一种高效 sql 方法，利用子查询定位“on→off”状态转换点，直接从原始日志表中提取完整行程次数，避免应用层循环解析，显著提升性能。
本文介绍一种高效 sql 方法，利用子查询定位“on→off”状态转换点，直接从原始日志表中提取完整行程次数，避免应用层循环解析，显著提升性能。
在车辆状态监控类系统中，常需将连续的引擎 on → off 状态变化识别为一次有效“行程（Trip）”。原始数据以时间序列为前提（如示例中 ID 递增隐含时间顺序），但直接按 engine 分组（如 GROUP BY car_id, engine）无法捕获状态切换逻辑——它只会统计总 on/off 次数，而非行程数。
核心思路是：一次行程的结束，必须紧随一次“开启”之后的首次“关闭”。即：某条 engine = 'off' 的记录，其前一条记录（按 ID 顺序）的 engine 必须为 'on'。利用该逻辑，可通过关联自身表或子查询精准定位每个行程终点。
✅ 推荐解决方案（MySQL 5.7+ 兼容）：
SELECT c1.ID AS trip_end_id, c0.ID AS trip_start_id, c0.car_id FROM car c1 INNER JOIN car c0 ON c0.ID = c1.ID - 1 AND c0.engine = 'on' AND c1.engine = 'off' WHERE c1.car_id = c0.car_id;
该查询返回每段行程的起始 ID（trip_start_id）与终止 ID（trip_end_id），结果清晰可验证。对示例数据，将准确返回 3 行（对应 ID 对：(2,3)、(5,7)、(8,9) —— 注意：ID=3 前是 ID=2（on），ID=7 前是 ID=6（on），ID=9 前是 ID=8（on））。
⚠️ 关键注意事项：
- ID 必须严格递增且代表时间顺序：若实际使用时间戳字段（如 created_at），应改用窗口函数或自连接 + ORDER BY created_at LIMIT 1 子查询，避免依赖 ID。
- 边界情况处理：首条记录为 off 或末条为 on 时，不构成完整行程，本方案天然忽略，符合业务语义。
- 性能优化建议：为 engine 和 ID 字段建立联合索引 INDEX(engine, ID)，可大幅提升子查询/JOIN 效率。
- ❌ 原答案中的 id IN (SELECT id+1 ...) 存在逻辑错误：它查找的是“off 记录的 ID 恰好等于某个 on 记录 ID+1”，等价于 off 记录本身 ID 是 on ID+1，即 off 在 on 之后，但未限定“紧邻”，且易因 ID 不连续失效；正确逻辑应是 off 的前一条是 on（即 off.ID - 1 = on.ID），而非 off.ID = on.ID + 1（二者数学等价，但语义和可读性差，且原写法未关联 car_id，存在跨车误匹配风险）。
? 进阶提示（MySQL 8.0+）：
可使用 LAG() 窗口函数更直观地实现：
SELECT ID, car_id, engine,
LAG(engine) OVER (PARTITION BY car_id ORDER BY ID) AS prev_engine
FROM car;再在外层筛选 engine = 'off' AND prev_engine = 'on'，语义更清晰，扩展性强（如需分析多状态序列）。
总结：通过精准建模“状态转换”的相邻性约束，纯 SQL 即可高效完成行程识别，彻底替代低效的应用层遍历，兼顾准确性、可维护性与执行性能。
