查找“最早的"组合主键 [英] Finding the 'earliest' composite primary key

问题描述

考虑以下数据:

ID       Year     Code1    Code2
-------- -------- -------- --------
ABC123   99/00    10010    A1121
ABC123   00/01    10010    A1131
ABC123   01/02    10010    A1141
XYZ567   06/07    12501    B2213
XYZ567   07/08    12501    B2223

这四个字段组成了主键，我需要找到其他每个ID实例的最早出现，以及其他三个键.因此，在这种情况下，我想要:

These four fields make up the primary key and I need to find the earliest occurrence of each instance of ID, with the three other keys. So, in this case, I would want:

ID       Year     Code1    Code2
-------- -------- -------- --------
ABC123   99/00    10010    A1121
XYZ567   06/07    12501    B2213

在Oracle中，我当前的解决方案是汇总Code2的第四个字符(即年索引)，并返回最小值，然后重新组合键(即变化的Year部分)；但是，这显然是模棱两可的，可能并非一对一映射. (而且，这种方法很慢！)排序也不起作用，因为通常ID下将有多个值，而Year不包括世纪.

In Oracle, my current solution is to aggregate over the fourth character of Code2, which is the year index, and return the minimum then recompose the key (i.e., the varying Year part); however, this is obviously ambiguous and may not map one-to-one. (Also, this method is slow!) An ordering won't work either because, in general, there will be multiple values under ID and Year doesn't include the century.

我当时想也许我可以对每个ID进行子查询，然后进行排序并选择第一个项目(即ROWNUM=1).但是，这至少需要两个级别的子查询，因此既笨拙又慢...那么，还有更好的主意吗?谢谢:)

I was thinking that maybe I could do a subquery for each ID and then do an ordering and pick the first item (i.e. ROWNUM=1). However, this requires at least two levels of subquery and, thus, is both awkward and slow... So, any better ideas?! Thanks :)

推荐答案

这是

This is what analytic functions were invented for! You want something like:

select id, year, code1, code2 from
( select id, year, code1, code2,
         row_number() over (partition by id order by ???) as rn
  from mytable
)
where rn = 1

我将order by留为???因为我不完全了解您的需求.可能是:

I left the order by as ??? because I don't fully understand what you want here. It could be:

order by year, code1, code2

或

order by substr(code2,4,1), year

或其他.

这篇关于查找“最早的"组合主键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！