如何在SQL Server中获取STOP之前的最后一个菜单

嘿，针对你的需求——找出每个CALLID对应通话里，STOP菜单出现之前的最后一个菜单，我整理了两种实用的SQL方案，你可以根据自己的表数据情况选择：

方法一：CTE+关联子查询（基础易理解） #

先抓取每个通话的STOP时间，再定位该时间之前最晚的菜单记录：

-- 记得把YourTableName替换成你的实际表名
WITH StopTimePerCall AS (
    SELECT CALLID, TIME AS Stop_Time
    FROM YourTableName
    WHERE MENUNAME = 'STOP'
)
SELECT 
    main.CALLID,
    main.MENUNAME AS Last_Menu_Before_Stop,
    main.TIME AS Menu_Time
FROM YourTableName main
INNER JOIN StopTimePerCall stop_times 
    ON main.CALLID = stop_times.CALLID
WHERE main.TIME < stop_times.Stop_Time
AND main.TIME = (
    SELECT MAX(TIME)
    FROM YourTableName
    WHERE CALLID = main.CALLID AND TIME < stop_times.Stop_Time
);

逻辑拆解： #

1. StopTimePerCall这个公共表表达式（CTE）先筛出所有STOP菜单的记录，拿到每个通话对应的停止时间；
2. 把原表和这个CTE关联，只保留同一通话下时间早于停止时间的菜单；
3. 用子查询找出这些前置菜单里时间最晚的那条，就是我们要的目标。

方法二：窗口函数（更简洁高效） #

如果你的SQL Server版本是2008及以上（支持窗口函数），这种写法会更清爽：

-- 替换YourTableName为实际表名
WITH RankedMenus AS (
    SELECT 
        CALLID,
        MENUNAME,
        TIME,
        -- 给每个通话的菜单按时间从晚到早排名
        ROW_NUMBER() OVER (PARTITION BY CALLID ORDER BY TIME DESC) AS Menu_Rank,
        -- 拿到当前通话的STOP菜单时间
        MAX(CASE WHEN MENUNAME = 'STOP' THEN TIME END) OVER (PARTITION BY CALLID) AS Stop_Time
    FROM YourTableName
)
SELECT 
    CALLID,
    MENUNAME AS Last_Menu_Before_Stop,
    TIME AS Menu_Time
FROM RankedMenus
WHERE TIME < Stop_Time
AND Menu_Rank = 1;

逻辑拆解： #

1. 在RankedMenus里，我们给每个通话的菜单按时间倒序排了名次，同时算出该通话的STOP时间；
2. 最后只要挑出时间早于停止时间且排名第一的记录——也就是最晚的那个前置菜单。

边界情况说明 #

• 如果某个通话没有STOP菜单：上面的查询不会返回该通话的记录，符合你要找“STOP之前”菜单的需求；
• 如果STOP是某个通话的第一个菜单（没有前置菜单）：查询也不会返回该通话的记录；
• 要是你需要把这些边界情况也返回（比如显示NULL），可以把方法一里的INNER JOIN改成LEFT JOIN，再调整条件，比如：

WITH StopTimePerCall AS (
    SELECT CALLID, TIME AS Stop_Time
    FROM YourTableName
    WHERE MENUNAME = 'STOP'
)
SELECT 
    main.CALLID,
    CASE 
        WHEN MAX(main.TIME) < stop_times.Stop_Time THEN 
            (SELECT MENUNAME FROM YourTableName WHERE CALLID = main.CALLID AND TIME = MAX(main.TIME))
        ELSE NULL 
    END AS Last_Menu_Before_Stop,
    MAX(main.TIME) AS Menu_Time
FROM YourTableName main
LEFT JOIN StopTimePerCall stop_times 
    ON main.CALLID = stop_times.CALLID
GROUP BY main.CALLID, stop_times.Stop_Time;

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