如何基于VIN列并对比两个日期列去除SQL重复行？

我来帮你搞定这个查询需求！你的核心目标是每个VIN只返回一行符合特定日期优先级规则的数据，之前尝试的ELSE、COALESCE或简单MAX子查询没能解决，是因为需要同时处理「Null优先级」和「日期排序」的双重逻辑，用窗口函数ROW_NUMBER()是最直接的方案。

先明确你的需求逻辑（结合例子修正理解） #

针对每个VIN的所有行：

• 优先保留INVOICE DATE为Null的行，在这些行中取最新OPEN DATE的那一行；
• 如果该VIN没有INVOICE DATE为Null的行，就在所有有发票日期的行中，取OPEN DATE和INVOICE DATE里较晚的那个日期对应的最新行。

比如你举的例子：

VIN='XY123456'有3行数据：行1的OPEN DATE为3/1/2019、INVOICE DATE为Null；行2的OPEN DATE为3/15/2019、INVOICE DATE为3/28/2019；行3的OPEN DATE为4/1/2019、INVOICE DATE为4/5/2019

因为存在INVOICE DATE为Null的行（行1），所以直接返回该行；如果行1也有INVOICE DATE，那么所有行都有发票日期，就取MAX(OPEN DATE, INVOICE DATE)最大的行3（4/5/2019）。

解决方案：用CTE+窗口函数实现 #

思路是：

1. 先用CTE（公共表表达式）封装你的原查询，同时保留原始日期类型（不要先转成字符串，避免排序错误），并计算每行的「有效排序日期」；
2. 用ROW_NUMBER()按VIN分组，结合优先级规则排序，给每行标记序号；
3. 最后只取每个VIN中序号为1的行，再将日期转成你需要的字符串格式。

完整的SQL语句如下：

WITH RepairOrderCTE AS (
    SELECT 
        RO.[RepairOrderID],
        RO.[CompanyName],
        CUS.CustomerKey,
        UN.UnitNumber AS 'UNIT #',
        ISNULL(UC.[Tag #],'') AS 'S #',
        UN.Year,
        UN.Make,
        UN.Model,
        UN.VIN,
        ROS.[RepairOrderStatus] AS 'STATUS',
        RO.RepairOrderNumber AS 'RO #',
        -- 保留原始日期类型用于排序
        RO.[OpenDate] AS OriginalOpenDate,
        ROI.InvoiceDate AS OriginalInvoiceDate,
        -- 计算每行的有效排序日期
        CASE 
            WHEN ROI.InvoiceDate IS NOT NULL THEN IIF(RO.[OpenDate] > ROI.InvoiceDate, RO.[OpenDate], ROI.InvoiceDate)
            ELSE RO.[OpenDate]
        END AS EffectiveSortDate
    FROM [ProfitMaster].[dbo].[RepairOrder] RO with (nolock) 
    INNER JOIN [ProfitMaster].[dbo].[vwAC_SSR_Customer] CUS with (nolock) 
        ON CUS.CustomerID = RO.CustomerID 
    INNER JOIN [ProfitMaster].[dbo].[RepairOrderStatus] ROS with (nolock) 
        ON ROS.RepairOrderStatusID = RO.RepairOrderStatusID 
    LEFT OUTER JOIN [ProfitMaster].[dbo].[RepairOrderInvoice] ROI with (nolock) 
        ON ROI.RepairOrderID = RO.RepairOrderID 
    LEFT OUTER JOIN [ProfitMaster].[dbo].[vwSR_SSR_Unit] UN with (nolock) 
        ON UN.UnitInventoryID = RO.UnitInventoryID 
    LEFT OUTER JOIN [ProfitMaster].[dbo].[vwSR_SSR_UnitCharacteristics] UC with (nolock) 
        ON UC.UnitInventoryID = Ro.UnitInventoryID 
    WHERE 
        (
            (ROS.RepairOrderStatus IN ('OPEN')) 
            OR (ROS.RepairOrderStatus = 'QUOTE' AND RO.OpenDate >= (getDate()- 90)) 
            OR (ROS.RepairOrderStatus = 'INVOICED' AND ROI.InvoiceDate > '2019-01-01 00:00:00.000')
        ) 
        AND (CUS.InternalAccount = '0' AND CUS.InternalLRCustomer = '0')
),
RankedOrders AS (
    SELECT 
        *,
        -- 按VIN分组，先优先INVOICE DATE为Null的行，再按有效日期倒序排序
        ROW_NUMBER() OVER (
            PARTITION BY VIN 
            ORDER BY 
                CASE WHEN OriginalInvoiceDate IS NULL THEN 0 ELSE 1 END ASC,
                EffectiveSortDate DESC
        ) AS RowRank
    FROM RepairOrderCTE
)
-- 只取每个VIN的第一行，并转换日期格式
SELECT 
    RepairOrderID,
    CompanyName,
    CustomerKey,
    [UNIT #],
    [S #],
    Year,
    Make,
    Model,
    VIN,
    STATUS,
    [RO #],
    -- 转换OPEN DATE为你需要的格式
    CONVERT(VARCHAR(2), DATEPART(month, OriginalOpenDate)) + '/' + 
    CONVERT(VARCHAR(2), DATEPART(DAY, OriginalOpenDate)) + '/' + 
    CONVERT(VARCHAR(4), DATEPART(YEAR, OriginalOpenDate)) AS 'OPEN DATE',
    -- 转换INVOICE DATE（处理Null情况）
    CASE 
        WHEN OriginalInvoiceDate IS NOT NULL THEN 
            CONVERT(VARCHAR(2), DATEPART(month, OriginalInvoiceDate)) + '/' + 
            CONVERT(VARCHAR(2), DATEPART(DAY, OriginalInvoiceDate)) + '/' + 
            CONVERT(VARCHAR(4), DATEPART(YEAR, OriginalInvoiceDate))
        ELSE ''
    END AS 'INVOICE DATE'
FROM RankedOrders
WHERE RowRank = 1;

关键部分解释 #

1. CTE RepairOrderCTE：

   • 保留了OriginalOpenDate和OriginalInvoiceDate的原始日期类型，避免字符串排序的错误（比如'10/1/2023'字符串排序会比'9/30/2023'小，但实际日期更大）；
   • EffectiveSortDate用来计算每行的排序基准：有发票日期时取两个日期的最大值，无发票日期时取开单日期。

2. 窗口函数ROW_NUMBER()：

   • PARTITION BY VIN：按VIN分组，确保每个VIN单独计算序号；
   • ORDER BY子句：先通过CASE把INVOICE DATE为Null的行排在前面（优先级更高），再按EffectiveSortDate倒序，这样每个VIN里的第一行就是我们需要的目标行。

3. 外层查询：

   • 筛选RowRank = 1的行，只保留每个VIN的唯一目标行；
   • 最后再把原始日期转成你需要的MM/DD/YYYY格式，同时处理INVOICE DATE为Null的情况。

这个方案完美匹配你的需求，而且性能也比嵌套子查询更优，适合关联多张表的场景。

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