Oracle数据库如何从两张不同表获取唯一的Item与Store配对值

需求背景 #

当前使用Oracle数据库，存在两张表T1和T2，需要提取T1的Item字段与T2的Store字段，目标是得到两个字段均无重复的行级唯一配对结果，即每个Store对应1个唯一Item。

已尝试的失效方案 #

先后测试两种查询方案均未达到预期：

• 对两张表分别去重后做全外连接，再对结果加distinct去重，该查询耗时极长，且返回结果中同一个Store对应多个Item，不符合要求；
• 按Store分组后取Item最大值，只能得到单条Item结果，无法输出全量的唯一配对。

表结构说明 #

两张表核心字段如下：

• T1表字段：ID、Item、ITEM_ID、CREATE_DATE、UPDATE_DATE
• T2表字段：Store、ITEM_ID、Itemsale、Status

可行解决方案 #

分别对两张表的目标字段用DENSE_RANK函数生成排序序号，按序号关联两张表即可得到所需结果，代码如下：

select T1.Item,T2.Store 
from
(SELECT  Item, DENSE_RANK() OVER (ORDER BY Item )  rnk from T1 group by Item   ) T1
inner join 
(SELECT Store ,DENSE_RANK() OVER (ORDER BY Store )  rnk  from T2 group by Store ) T2
on T1.rnk=T2.rnk

方案逻辑说明 #

• 先通过group by分别对T1的Item、T2的Store去重，保证两个子查询返回的目标字段无重复值
• 用DENSE_RANK窗口函数给去重后的目标字段生成连续排序序号
• 通过序号做等值关联，即可实现每个Store对应唯一的Item，且两个字段均无重复
• 若T1去重后Item数量和T2去重后Store数量不一致，inner join会返回数量等于较小值的配对结果，如果需要保留全部数据可改为full outer join，缺失的字段值会返回null

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