如何为每个唯一equip_OID筛选最新的tracked_out_datetime？

场景说明 #

我有Table A，其中同一Workstation对应不同的equip_OID：

Table A #

生成Table A的SQL代码：

select distinct
    trim(ws.WS_name) as [Workstation],
    substring(trim(e.equip_id),1,8) as [ID],
    eqmhist.WS_OID,
    eqmhist.equip_OID
into 
    #Table_A
from
    eqmhist
inner join 
    e on eqmhist.equip_OID = e.equip_OID
inner join 
    ws on ws.WS_OID = eqmhist.WS_OID
inner join 
    a on a.mfg_area_OID = e.mfg_area_OID

将Table A与包含tracked_out_datetime字段的Table B通过[ID]和[Tool ID]内连接，得到Table C：

Table C #

生成Table C的SQL代码：

select distinct
    ta.[Workstation],
    substring(trim(tracking_interface_id), 1, 8) as [Tool ID],
    ta.equip_OID,
    ta.WS_OID,
    tracked_out_datetime
into 
    #Table_C
from  
    Table_B tb
inner join 
    #Table_A ta on substring((tb.tracking_interface_id),1,8) = ta.[ID]
where 
    exists (select taa.[ID] from #Table_A taa 
            where taa.[ID] = substring((tb.tracking_interface_id), 1, 8))

（注：修正了原代码中列名笔误：[WS Name]改为[Workstation]，[Equid ID]改为[ID]）

当前问题 #

我需要为每个唯一的equip_OID筛选对应的最新tracked_out_datetime，但现有代码仅返回一条记录：

现有代码 #

select
    x.[Workstation],
    x.[Tool ID],
    x.WS_OID,
    x.equip_OID,
    tracked_out_datetime
into 
    #main2
from
    (select 
         ta.[Workstation], 
         substring(trim(tracking_interface_id),1,8) as [Tool ID],
         ta.equip_OID, 
         ta.WS_OID, 
         tb.tracked_out_datetime,
         row_number() over(partition by tracking_interface_id order by tracked_out_datetime desc) as rn
     from 
         Table_B tb
     inner join 
         #Table_A ta on substring((tb.tracking_interface_id), 1, 8) = ta.[ID]
     where 
         exists (select taa.[ID] from #Table_A taa 
                 where taa.[ID] = substring((tb.tracking_interface_id), 1, 8))) x
where 
    x.rn = 1 

select distinct * 
from #main2

错误结果 #

解决方案 #

问题出在row_number()的分区键上——你用了tracking_interface_id，但实际需要按equip_OID分区，这样才能为每个设备单独排序取最新时间。修正后的代码如下：

select
    x.[Workstation],
    x.[Tool ID],
    x.WS_OID,
    x.equip_OID,
    tracked_out_datetime
into 
    #main2
from
    (select 
         ta.[Workstation], 
         substring(trim(tracking_interface_id),1,8) as [Tool ID],
         ta.equip_OID, 
         ta.WS_OID, 
         tb.tracked_out_datetime,
         -- 按equip_OID分区，每个设备内按时间倒序排序
         row_number() over(partition by ta.equip_OID order by tb.tracked_out_datetime desc) as rn
     from 
         Table_B tb
     inner join 
         #Table_A ta on substring((tb.tracking_interface_id), 1, 8) = ta.[ID]
     where 
         exists (select taa.[ID] from #Table_A taa 
                 where taa.[ID] = substring((tb.tracking_interface_id), 1, 8))) x
where 
    x.rn = 1 

select distinct * 
from #main2

期望结果 #

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