У меня есть 2 таблицы в Oracle:
Recipient:
RecipientDeposit:
Я ищу, чтобы выбрать каждую строку получателя (с тем же адресом электронной почты и тем же редактором), которая не имеет никакого значения в recipientDeposit.
С помощью этого запроса я получаю получателя, которого не существует в RecipientDeposit.
SELECT
re.id_recipient , re.editor_id, re.email
FROM
recipient re
WHERE
re.id_recipient NOT IN (SELECT rd.id_recipient
FROM RecipientDeposit rd
WHERE re.id_recipient = rd.id_recipient)
Я попробовал это
SELECT
re.id_recipient, re.editor_id, re.email
FROM
recipient re
JOIN
(SELECT editor_id, email
FROM Recipient re2
GROUP BY editor_id, email
HAVING COUNT(*) > 1) b ON re.editor_id = b.editor_id
AND re.email = b.email
WHERE
re.id_recipient NOT IN (SELECT rd.id_recipient
FROM RecipientDeposit rd
WHERE re.id_recipient = rd.id_recipient)
но я правда не уверен.
🤔 А знаете ли вы, что...
SQL позволяет объединять данные из разных таблиц с помощью операторов JOIN.
Используйте адрес электронной почты и editor_id в качестве пары, проверяющей существование в подзапросе предложения Where, который находит пары, имеющие 0 счетчиков id_recipients в таблице RecipientDeposit...
WITH -- S a m p l e D a t a :
Recipient ( id_recipient, email, editor_id ) AS
( Select 1, '[email protected]', 1 From Dual Union All
Select 2, '[email protected]', 1 From Dual Union All
Select 3, '[email protected]', 5 From Dual Union All
Select 4, '[email protected]', 3 From Dual Union All
Select 5, '[email protected]', 4 From Dual
),
RecipientDeposit ( id_recipient, id_deposit ) AS
( Select 3, 1 From Dual Union All
Select 4, 2 From Dual
)
-- S Q L :
SELECT *
FROM Recipient
WHERE (email, editor_id)
IN ( Select r.email, r.editor_id
From Recipient r
Left Join RecipientDeposit rd ON(rd.id_recipient = r.id_recipient)
Group By r.email, r.editor_id
Having Count(rd.id_recipient) = 0
)
ORDER BY id_recipient
/* R e s u l t :
ID_RECIPIENT EMAIL EDITOR_ID
------------ -------------- ----------
1 [email protected] 1
2 [email protected] 1
5 [email protected] 4 */
Вы можете сделать это, только один раз запросив таблицу Recipient с помощью аналитических функций:
SELECT id_recipient,
email,
editor_id
FROM (
SELECT id_recipient,
email,
editor_id,
COUNT(*) OVER (PARTITION BY email, editor_id) AS num_rows,
COUNT(
CASE
WHEN EXISTS(
SELECT 1
FROM RecipientDeposit rd
WHERE rd.id_recipient = r.id_recipient
)
THEN 1
END
) OVER (PARTITION BY email, editor_id) AS has_deposit
FROM Recipient r
)
WHERE has_deposit = 0
AND num_rows > 1
Что для примера данных:
CREATE TABLE Recipient (id_recipient, email, editor_id) AS
SELECT 1, '[email protected]', 1 FROM DUAL UNION ALL
SELECT 2, '[email protected]', 1 FROM DUAL UNION ALL
SELECT 3, '[email protected]', 5 FROM DUAL UNION ALL
SELECT 4, '[email protected]', 3 FROM DUAL UNION ALL
SELECT 5, '[email protected]', 4 FROM DUAL UNION ALL
SELECT 6, '[email protected]', 1 FROM DUAL;
CREATE TABLE RecipientDeposit (id_recipient, id_deposit) AS
SELECT 3, 1 FROM DUAL UNION ALL
SELECT 4, 2 FROM DUAL;
Выходы: