从序列中查找缺失的记录 [英] Find missing records from sequence

问题描述

我需要在表中找到丢失的记录.表一:

I need to find the missing records in a table. Table 1:

ID|Num
X|1
X|2
X|3
X|4
Y|1
Y|3
Y|5

表 2:

Num
1
2
3
4
5

我需要返回:

ID|编号

X|5

Y|2

Y|4

我找到了其他可以给我 5、2、4 的解决方案，但我也需要与丢失记录关联的 ID.

I've found other solutions that would give me 5,2,4 but I need the ID associated with the missing record as well.

推荐答案

如果您想要缺失的数字，请使用 cross join 生成所有数字，然后过滤掉存在的数字:

If you want the missing numbers, use a cross join to generate all numbers and then filter out the ones that exist:

select i.id, t2.num
from (select distinct id from t1) i cross join
     table2 t2 left join
     t1
     on t1.id = i.id and t1.num = t2.num
where t1.id is null;

这篇关于从序列中查找缺失的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！