按时间戳从联接表中查找唯一 ID 的前 1 条记录-解网

问：

我有三个表，item、unique_item_code 和 unique_hist。 item 表与unique_item_code表有映射关系，unique_item_code与表有映射关系unique_hist。每个项目都有唯一的项目代码，该代码可在表格中找到unique_item_code。unique_hist表包含unique_code的其他详细信息，并且此表具有唯一代码的多个记录。

我必须从项目表和unique_item_code表中获取详细信息，但如果表中有超过 1 条记录可供unique_code unique_hist则采用last_updated_time少于 flag_updated_time的最新记录。

表“项目”：

TID的	SID	校订	IMPORT_FLAG	量	项目	FLAG_UPDATE_TIME
23456	0	0	0	657	BX系列	08-31-2023 10:20:10
65743	0	0	0	467	BX系列	08-30-2023 11:01:39
76543	血型	1	1	8753	都	08-30-2023 09:01:39
86364	F	2	1	78	泰	09-03-2023 11:12:41
86364	N	0	1	5000	室温	08-22-2023 18:45:16

表“UNIQUE_ITEM_CODE”：

TID的	SID	校订	UNIQUE_CODE
23456	0	0	ASF456B
65743	0	0	FDGHJY6
76543	血型	1	ET567BG
86364	F	2	MHFB78型
86364	N	0	567HFV8型

表“UNIQUE_HISTORY”：

UNIQUE_CODE	优先权	类	类型	地位	LAST_UPDATE_TIME
ASF456B	高	TTH的	B	闭	08-31-2023 13:23:45
FDGHJY6	低	计算机辅助设计	S	打开	08-31-2023 09:32:25
ET567BG	低	澳大利亚	B	打开	08-30-2023 10:00:11
MHFB78型	低	澳大利亚	B	终止	09-03-2023 11:09:18
567HFV8型	低	欧元	S	打开	08-22-2023 18:28:56
ASF456B	低	计算机辅助设计	S	闭	08-15-2023 23:32:42
FDGHJY6	高	NXZ公司	B	拿	08-30-2023 03:14:25
FDGHJY6	高	NXZ公司	B	完成	08-30-2023 03:16:15
FDGHJY6	低	TTH的	S	闭	08-30-2023 14:20:02

"预期输出“：

TID的	SID	校订	IMPORT_FLAG	量	项目	FLAG_UPDATE_TIME	UNIQUE_CODE	优先权	类	类型	LAST_UPDATE_TIME
23456	0	0	0	657	BX系列	08-31-2023 10:20:10	ASF456B	低	计算机辅助设计	S	08-15-2023 23:32:42
65743	0	0	0	467	BX系列	08-30-2023 11:01:39	FDGHJY6	高	NXZ公司	B	08-30-2023 03:16:15
76543	血型	1	1	8753	都	08-30-2023 09:01:39	ET567BG	低	澳大利亚	B	08-20-2023 07:00:11
86364	F	2	1	78	泰	09-03-2023 11:12:41	MHFB78型	低	澳大利亚	B	09-03-2023 11:09:18
86364	N	0	1	5000	室温	08-22-2023 18:45:16	567HFV8型	低	欧元	S	08-22-2023 18:28:56

我尝试了这个查询：

select it.*, 
       hist.unique_code, 
       hist.priority, 
       hist.class, 
       hist.type, 
       hist.last_updated_time
from item it 
join unique_item_code uniq 
  on it.tid = uniq.tid 
 and it.sid = uniq.sid and it.revision = uniq.revision 
join unique_hist hist 
  on uniq.unique_code = hist.unique_code 
where it.flag_updated_time < hist.last_updated_time

还有这个：

select it.*, 
       hist.unique_code, 
       hist.priority, 
       hist.class, 
       hist.type, 
       hist.last_updated_time 
from item it 
join unique_item_code uniq 
  on it.tid = uniq.tid 
 and it.sid = uniq.sid and it.revision = uniq.revision 
join (select top 1 
      from unique_hist hist 
      where it.flag_updated_time < last_updated_time 
        and uniq.unique_code = hist.unique_code)

sql-server 联接 sql-server-2012 greatest-n-per-group

select it.*, hist.unique_code, hist.priority, hist.class, hist.type, hist.last_update_time
from item it
    join unique_item_code uniq on it.tid = uniq.tid and it.sid = uniq.sid and it.revision = uniq.revision
    join unique_history hist on uniq.unique_code = hist.unique_code
where hist.last_update_time = ( select min(last_update_time)
from unique_history hist_min
where uniq.unique_code = hist_min.unique_code
    and it.flag_update_time >= hist_min.last_update_time )

(2) 在 CTE 中，使用 MIN 窗口函数从早于项目表unique_history flag_update_time的表中获取最早的last_update_time;然后只选择与最旧的行匹配的行last_update_time

;with
    cte
    as
    (
        select it.*, hist.unique_code, hist.priority, hist.class, hist.type, hist.last_update_time, min(hist.last_update_time) over (
    partition by hist.unique_code ) min_last_update_time
        from item it
            join unique_item_code uniq on it.tid = uniq.tid and it.sid = uniq.sid and it.revision = uniq.revision
            join unique_history hist on uniq.unique_code = hist.unique_code
                and it.flag_update_time >= hist.last_update_time
    )
select *
from cte
where last_update_time = min_last_update_time

请记住，Stack Overflow 不仅旨在解决眼前的问题，还旨在帮助未来的读者找到类似问题的解决方案，这需要了解底层代码。这对于我们社区中不熟悉语法的初学者来说尤其重要。鉴于此，您能否编辑您的答案以包括对您正在做的事情以及为什么您认为这是最佳方法的解释？

0赞 Rahul 9/12/2023

@Nandalal Seth，此解决方案适用于条件“如果表中unique_code unique_hist有超过 1 条记录可用，则采用last_updated_time少于 flag_updated_time 的最新记录”，但它不适用于unique_code“ET567BG”

0赞 Nandalal Seth 9/12/2023

@Rahul，在您的预期输出中，您给出的LAST_UPDATE_TIME为 08-20-2023 07：00：11，这早于 08-30-2023 09：01：39 的FLAG_UPDATE_TIME，但在您上面提供的表中，LAST_UPDATE_TIME的数据是 08-30-2023 10：00：11，比 FLAG_UPDATE_TIME 更新。因此，如果您的表具有符合预期结果的数据，则查询应该可以正常工作。或者即使last_update_time比flag_update_time更新，您也想显示历史记录？我在查询中假设历史数据不会比项目更新。

3赞 lemon 9/9/2023 #2

您可以通过筛选联接表的历史记录表中的记录来解开问题。

为了安全地过滤记录，要获取按日期排序的每个分区的第一条记录，当应用于窗口函数时，可以使用 TOP（1） WITH TIES，例如，当满足级联的两个条件时，每个分区返回 1：ROW_NUMBER

"flag_update_time“低于”last_update_time"
"last_update_time“ 是最高的

SELECT TOP(1) WITH TIES 
       i.TID, 
       i.SID, 
       c.REVISION, 
       i.IMPORT_FLAG,
       i.AMOUNT,
       i.ITEM,
       i.FLAG_UPDATE_TIME,
       c.UNIQUE_CODE,
       hist.PRIORITY,
       hist.CLASS,
       hist.TYPE,
       hist.LAST_UPDATE_TIME
FROM       item i
INNER JOIN UNIQUE_ITEM_CODE c
        ON i.TID = c.TID
       AND i.SID = c.SID
INNER JOIN UNIQUE_HISTORY hist
        ON c.UNIQUE_CODE = hist.UNIQUE_CODE
ORDER BY ROW_NUMBER() OVER(PARTITION BY hist.UNIQUE_CODE 
                           ORDER     BY CASE WHEN i.FLAG_UPDATE_TIME < hist.LAST_UPDATE_TIME THEN 1 ELSE 0 END, 
                                        hist.LAST_UPDATE_TIME DESC)

在此处查看演示。

按时间戳从联接表中查找唯一 ID 的前 1 条记录

Find top 1 record from join table for unique id by timestamp

评论

评论

评论