SQL Server 2014中按订单状态ID取最大日期标识ID及行转列求助

我理解你的需求是：针对每个客户，将不同的订单状态ID转成列，每个列显示该客户对应状态下订单日期最新的那条记录的标识ID（也就是Id字段）。下面分两种场景给出解决方案：

一、订单状态ID固定的情况（静态Pivot） #

如果你的订单状态ID是已知且固定的（比如只有1、2、3三种），可以用静态Pivot来实现，步骤如下：

1. 先筛选每个客户每个状态下的最新订单ID #

我们用窗口函数ROW_NUMBER()给每个客户+状态分组内的订单按日期降序排名，取排名第一的就是最新订单：

WITH RankedOrders AS (
    SELECT 
        Id,
        [Customer ID],
        [Order Status ID],
        [Order Date],
        -- 按客户和状态分组，日期最晚的排第一
        ROW_NUMBER() OVER (PARTITION BY [Customer ID], [Order Status ID] ORDER BY [Order Date] DESC) AS rn
    FROM YourActualTableName -- 替换成你的表名
)
SELECT 
    [Customer ID],
    [Order Status ID],
    Id AS LatestOrderId
FROM RankedOrders
WHERE rn = 1;

2. 对结果进行行转列 #

把上面的结果作为数据源，用Pivot将订单状态ID转成列：

WITH RankedOrders AS (
    SELECT 
        Id,
        [Customer ID],
        [Order Status ID],
        [Order Date],
        ROW_NUMBER() OVER (PARTITION BY [Customer ID], [Order Status ID] ORDER BY [Order Date] DESC) AS rn
    FROM YourActualTableName
),
LatestOrders AS (
    SELECT 
        [Customer ID],
        [Order Status ID],
        Id AS LatestOrderId
    FROM RankedOrders
    WHERE rn = 1
)
SELECT 
    [Customer ID],
    [1] AS Status1_LatestOrderId, -- 状态1对应的最新订单ID
    [2] AS Status2_LatestOrderId, -- 状态2对应的最新订单ID
    [3] AS Status3_LatestOrderId  -- 状态3对应的最新订单ID
    -- 有更多状态的话继续添加对应的列
FROM LatestOrders
PIVOT (
    MAX(LatestOrderId) -- 因为每个分组只有一条记录，MAX/MIN都可以
    FOR [Order Status ID] IN ([1], [2], [3]) -- 这里列出所有固定的状态ID
) AS PivotResult;

二、订单状态ID不固定的情况（动态Pivot） #

如果订单状态ID是动态新增的，静态Pivot每次都要修改代码，这时候用动态SQL更方便。SQL Server 2014不支持STRING_AGG，所以我们用FOR XML PATH来拼接动态列：

DECLARE @StatusIds NVARCHAR(MAX), @PivotColumns NVARCHAR(MAX);

-- 第一步：获取所有唯一的订单状态ID，拼接成Pivot需要的列名格式（比如[1], [2]）
SELECT @StatusIds = STUFF((
    SELECT ', ' + QUOTENAME([Order Status ID])
    FROM (SELECT DISTINCT [Order Status ID] FROM YourActualTableName) AS StatusList
    ORDER BY [Order Status ID]
    FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'), 1, 2, '');

-- 第二步：拼接列的别名（比如[1] AS Status1_LatestOrderId）
SELECT @PivotColumns = STUFF((
    SELECT ', ' + CONCAT(QUOTENAME([Order Status ID]), ' AS Status', [Order Status ID], '_LatestOrderId')
    FROM (SELECT DISTINCT [Order Status ID] FROM YourActualTableName) AS StatusList
    ORDER BY [Order Status ID]
    FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'), 1, 2, '');

-- 第三步：构建并执行动态Pivot语句
DECLARE @DynamicSql NVARCHAR(MAX) = CONCAT(N'
WITH RankedOrders AS (
    SELECT 
        Id,
        [Customer ID],
        [Order Status ID],
        [Order Date],
        ROW_NUMBER() OVER (PARTITION BY [Customer ID], [Order Status ID] ORDER BY [Order Date] DESC) AS rn
    FROM YourActualTableName
),
LatestOrders AS (
    SELECT 
        [Customer ID],
        [Order Status ID],
        Id AS LatestOrderId
    FROM RankedOrders
    WHERE rn = 1
)
SELECT 
    [Customer ID],
    ', @PivotColumns, '
FROM LatestOrders
PIVOT (
    MAX(LatestOrderId)
    FOR [Order Status ID] IN (', @StatusIds, ')
) AS PivotResult;');

EXEC sp_executesql @DynamicSql;

期望结果示例 #

假设你的数据中有客户1001、1002，订单状态1、2、3，最终的行转列结果会是这样：

• 其中NULL表示该客户没有对应状态的订单
• 如果同一个客户同一个状态下有多个订单日期相同（都是最大日期），ROW_NUMBER()会随机返回其中一个的ID；如果需要保留所有符合条件的ID，可以把ROW_NUMBER()换成RANK()，再根据需求调整Pivot逻辑。

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