SQL Server嵌套查询：获取客户从1001到1005的最终状态变更记录

背景说明 #

假设数据库中有一张存储客户状态变更历史的表，若要查询状态从1001直接变更为1002的客户，可使用简单查询：

SELECT * FROM TableName WHERE StartStatus = 1001 AND EndStatus = 1002;

现在需要编写查询语句，获取状态从1001最终变更为1005的客户记录，需满足以下要求：

• 每个客户仅返回一条结果
• 忽略中间状态变更（如1001→1002→1003→1005这类流程，只要初始是1001、最终是1005即符合条件）
• 排除初始状态非1001但最终到1005的客户（例如ID为2的客户从1006变更为1005，不应被返回）

解决方案 #

假设表结构包含CustomerID（客户唯一标识）、StartStatus（变更起始状态）、EndStatus（变更结束状态）、ChangeTime（变更时间，用于判断变更顺序）字段，以下提供两种可行的查询方式：

方式一：使用窗口函数获取初始与最终状态 #

WITH CustomerStatusHistory AS (
    SELECT 
        CustomerID,
        StartStatus,
        EndStatus,
        -- 获取每个客户最早变更记录的起始状态
        FIRST_VALUE(StartStatus) OVER (PARTITION BY CustomerID ORDER BY ChangeTime) AS InitialStatus,
        -- 获取每个客户最晚变更记录的结束状态
        LAST_VALUE(EndStatus) OVER (PARTITION BY CustomerID ORDER BY ChangeTime ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS FinalStatus
    FROM TableName
)
SELECT DISTINCT
    CustomerID,
    InitialStatus,
    FinalStatus
FROM CustomerStatusHistory
WHERE InitialStatus = 1001 AND FinalStatus = 1005;

方式二：使用分组聚合确定初始与最终状态 #

-- 获取每个客户的初始状态（最早变更记录的StartStatus）
WITH InitialStatus AS (
    SELECT 
        CustomerID,
        StartStatus AS InitialStatus
    FROM TableName
    WHERE (CustomerID, ChangeTime) IN (
        SELECT CustomerID, MIN(ChangeTime)
        FROM TableName
        GROUP BY CustomerID
    )
),
-- 获取每个客户的最终状态（最晚变更记录的EndStatus）
FinalStatus AS (
    SELECT 
        CustomerID,
        EndStatus AS FinalStatus
    FROM TableName
    WHERE (CustomerID, ChangeTime) IN (
        SELECT CustomerID, MAX(ChangeTime)
        FROM TableName
        GROUP BY CustomerID
    )
)
SELECT
    i.CustomerID,
    i.InitialStatus,
    f.FinalStatus
FROM InitialStatus i
JOIN FinalStatus f ON i.CustomerID = f.CustomerID
WHERE i.InitialStatus = 1001 AND f.FinalStatus = 1005;

注意事项 #

• 如果表中没有ChangeTime字段，可替换为其他能判断变更先后顺序的字段（如自增主键ID），确保能准确识别每个客户的第一条和最后一条变更记录。
• 两种方式均能确保每个客户仅返回一条符合条件的结果，且过滤掉初始状态非1001的客户。

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