Sql join: обнаружение изменений
Периодически я хочу сравнить глобальную таблицу sql (называемую «ресурс») с локальной резервной таблицей (называемой «region_db»), чтобы увидеть, было ли изменено поле. Поле, которое я отслеживаю таким образом, называется «состояние», а первичный ключ называется «id». В настоящее время я делаю
SELECT id, state FROM resource
Затем вручную перебираем полученные строки в цикле. Для каждого кортежа (id, state) я делаю
SELECT state FROM region_db WHERE id = id
И проверьте, совпадает ли состояние из локальной базы данных region_d с состоянием из глобальной базы данных ресурсов. Таким образом я могу обнаружить два случая: 1) когда к ресурсу добавляется новый идентификатор и 2) когда изменяется состояние существующей строки.
Однако я упускаю случай, когда строка удаляется из таблицы ресурсов.
Я думаю об использовании JOIN, но не уверен, как эффективно различать три случая (изменить существующую, добавить новую и удалить строку из таблицы ресурсов), сводя к минимуму количество операций JOIN/DB.
Ответы 3
Вы можете использовать full join:
select coalesce(r.id, reg.id) as id,
(case when r.id is null then 'DELETED'
when reg.id is null then 'CREATED'
else 'UPDATED'
end)
from resource r full join
region_db reg
on r.id = reg.id
where r.id is null or reg.id is null or r.state <> reg.state; -- something changed
Это довольно сложно. Может ли он просто не использовать UNION ALL и не определять все строки, которые существуют в одном и другом?
@Зак. . . Мне это кажется проще, чем union all.
это гладко. Хорошее использование оператора case, объединения и внешнего соединения.
WITH joined AS (
SELECT
region.state as 'region_state',
resource.state as 'global_state'
FROM
resource
INNER JOIN
region_db
ON
resource.id = region_db.id
) SELECT * FROM joined WHERE region_state <> 'global_state';
;
Этот запрос даст вам таблицу, которая отражает изменение состояния существующей строки. Если вы сделаете left join вместо inner join в запросе with, вы получите записи, которые могли быть добавлены, но еще не скопированы в region_db. Аналогичным образом, с помощью right join вы можете получить записи, которые были удалены, но еще не распространены.
Надеюсь, это поможет.
Вы можете использовать UNION ALL, который должен указать вам различия в таблицах - в основном проверка того, где count(*) = 1 означает, где строки не совпадают (из-за GROUP BY)
SELECT id,state
FROM (
SELECT id, state FROM resource
UNION ALL
SELECT id,state FROM region_db
) tbl
GROUP BY id, state
HAVING count(*) = 1
ORDER BY id;
@GordonLinoff Готово