MySQL中如何选取Journeys表内EventType=3之前至EventType=1的所有行？

嘿，我完全懂你的需求啦——你就是想把每一段已经结束的完整行程（从EventType=1的起点，到EventType=3的终点，中间所有EventType=2的GPS点都包含在内）都查出来对吧？别担心，虽然看起来有点绕，但用窗口函数就能轻松实现，我给你一步步讲清楚：

核心思路 #

我们需要先给每一段独立的行程分配一个唯一的分组ID：每当遇到EventType=1（行程开始），就创建一个新分组；之后的所有记录（包括EventType=2和最终的EventType=3）都归到这个分组里，直到下一个EventType=1出现。这样我们就能精准定位到每个包含结束记录（EventType=3）的分组，把整个行程的所有GPS数据都取出来。

具体SQL代码（主流数据库通用） #

假设你的表有一个用来排序的字段（比如记录生成时间RecordTime，或者自增主键ID，GPS数据必须按时间顺序处理，这个字段很重要），代码如下：

WITH JourneyGroups AS (
    SELECT 
        *,
        -- 关键：遇到EventType=1就给分组ID加1，同一行程的记录会有相同的GroupID
        SUM(CASE WHEN EventType = 1 THEN 1 ELSE 0 END) OVER (ORDER BY RecordTime ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS GroupID
    FROM Journeys
)
SELECT 
    Latitude, 
    Longitude, 
    EventType,
    RecordTime -- 这里可以替换成你需要的其他字段
FROM JourneyGroups
-- 只保留那些存在结束记录（EventType=3）的完整行程分组
WHERE GroupID IN (
    SELECT DISTINCT GroupID
    FROM JourneyGroups
    WHERE EventType = 3
)
ORDER BY GroupID, RecordTime;

代码解释 #

1. CTE临时表JourneyGroups：用SUM()窗口函数给每条记录分配分组ID。ORDER BY RecordTime确保我们按时间顺序处理记录，ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW表示从第一条记录到当前记录的范围累加，这样每遇到一个EventType=1，GroupID就会+1，后面的记录都继承这个ID直到下一个EventType=1。
2. 主查询筛选：通过子查询找出所有包含EventType=3的分组ID，确保我们只取已经结束的完整行程。
3. 排序：最后按分组ID和时间排序，保证每段行程的记录是按时间顺序排列的。

兼容老版本数据库的方案（比如MySQL 5.x不支持CTE） #

如果你的数据库不支持WITH子句，可以用子查询改写：

SELECT 
    j1.Latitude, 
    j1.Longitude, 
    j1.EventType,
    j1.RecordTime
FROM (
    SELECT 
        *,
        SUM(CASE WHEN EventType = 1 THEN 1 ELSE 0 END) OVER (ORDER BY RecordTime ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS GroupID
    FROM Journeys
) j1
INNER JOIN (
    SELECT DISTINCT 
        SUM(CASE WHEN EventType = 1 THEN 1 ELSE 0 END) OVER (ORDER BY RecordTime ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS GroupID
    FROM Journeys
    WHERE EventType = 3
) j2 ON j1.GroupID = j2.GroupID
ORDER BY j1.GroupID, j1.RecordTime;

注意事项 #

• 如果你的表没有RecordTime，一定要用自增主键（比如ID）来替换ORDER BY里的字段，确保记录是按生成顺序处理的，否则分组会出错。
• 如果你的数据库不支持窗口函数（非常老的版本），可以用关联子查询来计算分组ID，但效率会低一些，不过对于GPS数据量来说应该也能应付。

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