SQL查询去重：含临时表与关联，Group By用法咨询

一、定位重复根源 #

你的查询里重复值主要来自两个环节：

1. 生成#temp表时，left join #trlavg可能出现一对多匹配，导致单条ras记录被复制多条；
2. 三次向#ras插入数据时，不同批次插入了相同f_UnitID的记录。

二、针对性解决方法 #

1. 修复#temp表的一对多重复 #

如果#trlavg的分组逻辑没问题（理论上按PRODUCT、ORIGINCTY、ORIGINSTA、DESTINCTY、DESTINSTA分组后应该唯一），但仍出现匹配多条的情况，大概率是数据存在空格/大小写差异，或者需要确保每个ras记录只匹配一条#trlavg数据。可以用ROW_NUMBER()先给#trlavg加唯一标识，再关联：

-- 生成#temp时，先给#trlavg加行号，确保每个关联条件只取一条
Select 
'Arkema@qts.com' as Email,
t_Loaded,
t_TripID,
f_UnitID,
f_CustomerId,
t_StartDate,
t_PlaceDate,
t_OriginCity,
t_OriginState,
t_DestinCity,
t_DestinState,
t_ProdDesc,
t_BOLCust,
t_POShip,
t_NetWt,
f_Fleet,
f_Subfleet,
t_CurETA,
t_Status,
t_StatusCode,
f_LastCLMDate,
f_LastCLMLoc,
f_LastCLMCarrier,
f_LastCLMEvt,
f_EvtDesc1,
f_LastCLMDest,
case when ta.AVGLay is null then 14.0 
     else cast(ta.AVGLay as decimal(10,2))
     End as AVGLay,
case when ta.AVGEmpty is null then 14.0
     else cast(ta.AVGEmpty as decimal(10,2)) 
     End as AVGEmpty,
Datediff(d,t_PlaceDate,getdate()) as ActDwellDays,
case when t_StatusCode = 1 then null
     when t_StatusCode = 2 and ta.AVGLay is null then Dateadd(d,14,t_PlaceDate)
     when t_StatusCode = 2 and ta.AVGLay is not null then Dateadd(d,ta.AVGLay,t_PlaceDate) 
End as EstEmptyRelease
into #temp
from Host32.dbo.RAS_TripsFleetCombo ras
join Host32.dbo.Fleet F on ras.t_CustomerID = F.CustomerID and ras.f_UnitID = F.UnitId and F.ActiveRecord = 1
left join (
    select *, ROW_NUMBER() over(partition by PRODUCT, ORIGINCTY, ORIGINSTA, DESTINCTY, DESTINSTA order by PRODUCT) as rn
    from #trlavg
) ta on ras.t_DestinCity = ta.DESTINCTY
                 and ras.t_DestinState = ta.DESTINSTA
                 and ras.t_OriginCity = ta.ORIGINCTY
                 and ras.t_OriginState = ta.ORIGINSTA
                 and ras.t_ProdDesc = ta.PRODUCT 
                 and ta.rn = 1 -- 只取每组第一条
where f_CustomerID = 8 
and t_Loaded = 1
and t_StatusCode in (1,2)
and t_Origin <> t_Destination

2. 避免#ras表的跨批次重复 #

三次插入#ras时，可能同一f_UnitID在不同批次被插入，有两种解决方式：

方式一：插入前过滤已存在的记录

每次INSERT前加WHERE NOT EXISTS判断，避免重复插入：

-- 第一次插入#ras时
INSERT into #ras
Select
Email,
t_Loaded,
t_TripID,
f_UnitID,
f_CustomerId,
t_StartDate,
t_PlaceDate,
t_OriginCity,
t_OriginState,
t_DestinCity,
t_DestinState,
t_ProdDesc,
t_BOLCust,
t_POShip,
t_NetWt,
f_Fleet,
f_Subfleet,
t_CurETA,
t_Status,
t_StatusCode,
f_LastCLMDate,
f_LastCLMLoc,
f_LastCLMCarrier,
f_LastCLMEvt,
f_EvtDesc1,
f_LastCLMDest,
AVGLay,
AVGEmpty,
ActDwellDays,
case when t_StatusCode = 1 then null
     when t_StatusCode= 2 and getdate() <= EstEmptyRelease then EstEmptyRelease
     when t_StatusCode = 2 and getdate() > EstEmptyRelease then getdate()
End as EstEmptyRelease,
case when t_StatusCode = 1 then null
     when t_StatusCode= 2 and getdate() <= EstEmptyRelease then  getdate() + Datediff(d,getdate(),EstEmptyRelease) + AVGEmpty
     when t_StatusCode = 2 and getdate() > EstEmptyRelease then Dateadd(d,AVGEmpty,getdate())
End as EstEmptyAvailable,
case  when  t_StatusCode = 1 then null
      when t_StatusCode= 2 and getdate() <= EstEmptyRelease then DateDiff(d,getdate(), getdate() + Datediff(d,getdate(),EstEmptyRelease) + AVGEmpty)
       when t_StatusCode = 2 and getdate() > EstEmptyRelease then Datediff(d,getdate(),  Dateadd(d,AVGEmpty,getdate()))
End as DaysOut
from #temp
where NOT EXISTS (select 1 from #ras where f_UnitID = #temp.f_UnitID)
Order by f_UnitID

-- 第二次插入空车记录时
INSERT into #ras
Select 
'Arkema@qts.com' as Email,
t_Loaded,
t_TripID,
f_UnitID,
f_CustomerId,
t_StartDate,
t_PlaceDate,
t_OriginCity,
t_OriginState,
t_DestinCity,
t_DestinState,
t_ProdDesc,
t_BOLCust,
t_POShip,
t_NetWt,
f_Fleet,
f_Subfleet,
t_CurETA,
t_Status,
t_StatusCode,
f_LastCLMDate,
f_LastCLMLoc,
f_LastCLMCarrier,
f_LastCLMEvt,
f_EvtDesc1,
f_LastCLMDest,
null as AVGLay,
null as AVGEmpty,
null as ActDwellDays,
null as EstEmptyRelease,
t_CurETA as EstEmptyAvailable,
case when t_StatusCode in ( 3,5) then Datediff(d,getdate(),t_CurETA)
     when t_StatusCode = 4 then null
End as DaysOut
from Host32.dbo.RAS_TripsFleetCombo ras
join Host32.dbo.Fleet F on ras.t_CustomerID = F.CustomerID and ras.t_UnitID = F.UnitId and F.ActiveRecord = 1
where f_CustomerID = 8
and t_StatusCode in (3,4,5)
and t_Origin <> t_Destination
and NOT EXISTS (select 1 from #ras where f_UnitID = ras.f_UnitID)

-- 第三次插入非跟踪车辆时
INSERT into #ras
Select 
'Arkema@qts.com' as Email,
case when f_LastCLMLoaded ='L' then 1 
     when f_LastCLMLoaded = 'E' then 0
     else null End as t_Loaded,
null as t_TripID,
f_UnitID,
f_CustomerId,
null as t_StartDate,
null as t_PlaceDate,
null as t_OriginCity,
null as t_OriginState,
null as t_DestinCity,
null as t_DestinState,
null as t_ProdDesc,
null as t_BOLCust,
null as t_POShip,
t_NetWt,
f_Fleet,
f_Subfleet,
t_CurETA,
t_Status,
t_StatusCode,
f_LastCLMDate,
f_LastCLMLoc,
f_LastCLMCarrier,
f_LastCLMEvt,
f_EvtDesc1,
f_LastCLMDest,
null as AVGLay,
null as AVGEmpty,
null as ActDwellDays,
null as EstEmptyRelease,
null as EstEmptyAvailable,
null as DaysOut
from Host32.dbo.RAS_TripsFleetCombo ras
join Host32.dbo.Fleet F on ras.f_CustomerID = F.CustomerID and ras.f_UnitID = F.UnitId and F.ActiveRecord = 1
where f_CustomerID = 8
and NOT EXISTS (select 1 from #ras where f_UnitID = ras.f_UnitID)

方式二：最终查询时去重

如果不需要保留所有批次的重复记录，最后查询时用ROW_NUMBER()按f_UnitID去重，保留指定优先级的记录：

-- 替换原有的Select * from #ras
select * from (
    select *, 
           ROW_NUMBER() over(partition by f_UnitID order by t_StatusCode asc) as rn -- 按状态优先级排序，比如优先保留已加载的记录
    from #ras
) t
where rn = 1
Order by f_UnitID

3. 检查#trl表的原始数据重复 #

如果#trl表本身有重复的Trail_ID/Track_ID记录，也会导致后续聚合或关联出问题，可以提前去重：

-- 重构#trl的生成逻辑，先去重
Select 
distinct
Trail_ID,
Track_ID,
ORIGINCTY,
ORIGINSTA,
DESTINCTY,
DESTINSTA,
FINALCTY,
FINALSTA,
PRODUCT,
LEP,
abs(convert(real,DATE_TIME_ESHIP - DATE_TIME_AVAIL)) as LayDays,
abs(convert(real,DATE_TIME_EVAIL - DATE_TIME_ESHIP))as EmptyDays
into #trl
from TRLSTAT
where LEP ='P'
and (DATE_TIME_EVAIL >= @StartDate and DATE_TIME_EVAIL <= @EndDate)
and ORIGINCTY = FINALCTY and ORIGINSTA = FINALSTA
Order by TRAIL_ID

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