如何合并两个结果集而不将匹配结果相乘？

Question

我有一个返回项目列表和空白序列号字段的查询，如下所示：

Item  S/N
1        
1
1
2
3
3
3
3
. . .

复制是有意的，因为项目的行数对应于所购买的项目的数量。我还有一个表，其中包含已经收到序列号的项目：

Item  S/N
2     416
3     72
3     319
. . .

构建第一个列表的查询不引用具有序列号的表。现在，我希望用表中的信息修改查询结果，以便将表中的序列号添加到匹配的项目中。但是，如果没有足够的序列号，我希望保留空的序列号(请注意，表中条目的数量永远不会超过查询的条目)。新的结果将如下所示：

Item  S/N
1
1
1
2     416
3     72
3     319
3
3
. . .

我认为使用左边的查询结果(通过一个公用表表达式)和右边的表可以得到我想要的结果。但是当我尝试的时候，我得到的是：

Item  S/N
1
1
1
2     416
3     72
3     72
3     72
3     72
3     319
3     319
3     319
3     319
. . .

它看起来像是查询在匹配项上执行了一个交叉联接，但在没有匹配项时保留了左侧行的基数；这不是我想要的。有没有连接或方法可以让我得到我想要的东西？

Answer 1

您可以使用row_number()来完成此操作

select t1.item, t2.s_n
from (select t1.*,
             row_number() over (partition by item order by item) as seqnum
      from t1
     ) t1 left join
     (select t2.*
             row_number() over (partition by item order by item) as seqnum
      from t2
     ) t2
     on t1.item = t2.item and t1.seqnum = t2.seqnum;