时间戳

首页 > 解决方案 > 比较mssql中每一列的所有数据

sql - 比较mssql中每一列的所有数据

问题描述

我有一张如下表

  OrderId   Carrier Truck   Trailer
  10001     ABC     TruckA  TrailerA
  10001     ABC     TruckA  TrailerA
  10001     ABC     TruckB  TrailerA
  10001     ABC     TruckC  TrailerB
  10001     ABC     TruckC  TrailerD

我的查询的输出应该是单行

 OrderId   Carrier Truck   Trailer
 10001     ABC     NULL    NULL

必须应用逻辑，就好像每列包含相同的值，然后该值必须在该特定列中，否则它将为 NULL

我尝试了以下查询，但没有得到正确的输出

  SELECT 
        s.OrderId
       ,LEAD(s.OrderId,1) OVER (ORDER BY t.Code) AS PreviousOrderId
       ,c.LegalName AS CarrierName
       ,LEAD(c.LegalName,1) OVER (ORDER BY t.Code) AS PreviousCarrierName
       ,t.TruckLicensePlate
       ,LEAD(t.TruckLicensePlate,1) OVER (ORDER BY t.Code) AS PreviousTruckLicensePlate
       ,t.TrailerLicensePlate
       ,LEAD(t.TrailerLicensePlate,1) OVER (ORDER BY t.Code) AS PreviousTrailerLicensePlate
    INTO #tempResult
    FROM
       OrderDetails

    SELECT
        CASE WHEN t.OrderId = t.PreviousOrderId 
             THEN t.OrderId ELSE NULL END AS OrderId
        ,CASE WHEN t.CarrierName = t.PreviousCarrierName
             THEN t.CarrierName ELSE NULL END AS CarrierName
        ,CASE WHEN t.TruckLicensePlate = t.PreviousTruckLicensePlate
             THEN t.TruckLicensePlate ELSE NULL END AS TruckLicensePlate
        ,CASE WHEN t.TrailerLicensePlate = t.PreviousTrailerLicensePlate
             THEN t.TrailerLicensePlate ELSE NULL END AS TrailerLicensePlate
    from #tempResult  t
    order by orderid desc

标签： sqlsql-servercountpivotcase

解决方案

必须应用逻辑，就好像每列包含相同的值，然后该值必须在该特定列中，否则它将为 NULL

使用聚合和条件逻辑。我发现比较min()andmax()是一种直接的方法：

select
    orderid,
    case when min(carrier) = max(carrier) then min(carrier) end carrier,
    case when min(truck)   = max(truck)   then min(carrier) end truck,
    case when min(trailer) = max(trailer) then min(carrier) end trailer
from orderdetails
group by orderid

推荐阅读