Данные SQL, где не существует в таблице и не дублировать

Question

Это немного сложно, пожалуйста, сфокусируйтесь на моих требованиях, у меня есть 2 таблицы, я хочу получить данные из первой таблицы, которой нет во второй, И данные в первом столбце не дублируются для вспомогательного и дочернего идентификатора.

пример: у меня есть эта таблица

tab1 

id   subid     childid
1     11       77
2     22       55
3     33       66
4     11       77
7     22       55
8     33       60
9     99       98
10    33       60
11    97       98

tab2

id
1
4
7
10

первое, что я хочу, это идентификатор в tab1 не существует в tab2, который будет2,3,8,9,11 Однако некоторые из этих идентификаторов имеют дубликаты subid и chilid, поэтому я должен исключить их, поэтому я должен иметь идентификатор 3, 9, 11

Я пробовал этот запрос, но он также возвращает мне 3,9,11, 8, я не хочу 8, как исправить запрос?

select *
  from tab1 a
 where not exists (select 1 from tab2 b where a.id = b.id)
 and a.sub_id in (select c.sub_id
                                from tab1 c
                                group by c.sub_id,c.evt_id
                                having count(1) = 1)

Answer 1

Я думаю, что для нескольких поставщиков баз данных самым простым решением будет пара not exists запросы:

select *
from tab1 a
where not exists (
    select 1 
    from tab2 b 
    where a.id = b.id
)
and not exists (
    select 1 
    from tab1 c 
    where c.sub_id = a.sub_id 
    and c.evt_id = a.evt_id 
    and c.id <> a.id
)

Answer 2

Просто чтобы добавить подход с использованием CTE, вы можете сначала определить уникальный childid,subid пары, а затем объединить эту таблицу с вашей основной таблицей:

DB Fiddle

Схема (PostgreSQL v9.6)

create table tab1 (
  id integer primary key unique not null
, subid integer not null
, childid integer not null
  );
insert into tab1 (id,subid,childid) values (1, 11, 77);
insert into tab1 (id,subid,childid) values (2, 22, 55);
insert into tab1 (id,subid,childid) values (3, 33, 66);
insert into tab1 (id,subid,childid) values (4, 11, 77);
insert into tab1 (id,subid,childid) values (7, 22, 55);
insert into tab1 (id,subid,childid) values (8, 33, 60);
insert into tab1 (id,subid,childid) values (9, 99, 98);
insert into tab1 (id,subid,childid) values (10, 33,60);
insert into tab1 (id,subid,childid) values (11,    97       ,98);

create table tab2 (
      id integer primary key unique not null
  );

insert into tab2 (id) values (1);
insert into tab2 (id) values (4);
insert into tab2 (id) values (7);
insert into tab2 (id) values (10);

---

Запрос № 1

with tab1_unique as (
    select subid, childid, count(*) as duplicate
      from tab1
     group by subid, childid
    having count(*) = 1
)
select *
  from tab1 a
  join tab1_unique u on a.subid = u.subid and a.childid = u.childid
 where not exists (select 1 from tab2 b where a.id = b.id);

| id  | subid | childid | subid | childid | duplicate |
| --- | ----- | ------- | ----- | ------- | --------- |
| 11  | 97    | 98      | 97    | 98      | 1         |
| 9   | 99    | 98      | 99    | 98      | 1         |
| 3   | 33    | 66      | 33    | 66      | 1         |

Answer 3

Я думаю, что ниже запрос будет работать

select a.*
  from tab1 a
 where not exists (select 1 from tab2 b where a.id = b.id)
 and  not exists (select 1  from tab1 c 
                                 where c.sub_id = a.sub_id 
                                 and c.childid = a.childid
                                 group by c.sub_id,childid
                                 having count(*)> = 2
                              )

Answer 4

not exists должен сделать это:

select t1.*
from (select t1.*, count(*) over (partition by subid, childid) as cnt
      from tab1 t1
     ) t1
where not exists (select 1 from tab2 t2 where t2.id = t1.id) and
      cnt = 1;

Ты можешь использовать not exists а также для subid/childid с предположением, что строки являются уникальными в первой таблице. Без этого предположения оконные функции являются лучшим решением - и, возможно, лучшим решением в любом случае.