Запрос 2 таблиц с одинаковой спецификацией для различий
Недавно мне пришлось решить эту проблему, и я обнаружил, что в прошлом мне требовалась эта информация много раз, поэтому я решил опубликовать ее. Предполагая следующую таблицу def, как бы вы написали запрос, чтобы найти все различия между ними?
таблица def:
CREATE TABLE feed_tbl
(
code varchar(15),
name varchar(40),
status char(1),
update char(1)
CONSTRAINT feed_tbl_PK PRIMARY KEY (code)
CREATE TABLE data_tbl
(
code varchar(15),
name varchar(40),
status char(1),
update char(1)
CONSTRAINT data_tbl_PK PRIMARY KEY (code)
Вот мое решение в виде представления с использованием трех запросов, объединенных объединениями. В diff_type указано, как нужно обновить запись: удалить из _data(2), обновить в _data(1) или добавить в _data(0).
CREATE VIEW delta_vw AS (
SELECT feed_tbl.code, feed_tbl.name, feed_tbl.status, feed_tbl.update, 0 as diff_type
FROM feed_tbl LEFT OUTER JOIN
data_tbl ON feed_tbl.code = data_tbl.code
WHERE (data_tbl.code IS NULL)
UNION
SELECT feed_tbl.code, feed_tbl.name, feed_tbl.status, feed_tbl.update, 1 as diff_type
FROM data_tbl RIGHT OUTER JOIN
feed_tbl ON data_tbl.code = feed_tbl.code
where (feed_tbl.name <> data_tbl.name) OR
(data_tbl.status <> feed_tbl.status) OR
(data_tbl.update <> feed_tbl.update)
UNION
SELECT data_tbl.code, data_tbl.name, data_tbl.status, data_tbl.update, 2 as diff_type
FROM feed_tbl LEFT OUTER JOIN
data_tbl ON data_tbl.code = feed_tbl.code
WHERE (feed_tbl.code IS NULL)
)
Ответы 3
Я бы использовал небольшую вариацию во втором union:
where (ISNULL(feed_tbl.name, 'NONAME') <> ISNULL(data_tbl.name, 'NONAME')) OR
(ISNULL(data_tbl.status, 'NOSTATUS') <> ISNULL(feed_tbl.status, 'NOSTATUS')) OR
(ISNULL(data_tbl.update, '12/31/2039') <> ISNULL(feed_tbl.update, '12/31/2039'))
По причинам, которых я никогда не понимал, NULL не равен NULL (по крайней мере, в SQL Server).
Вы также можете использовать FULL OUTER JOIN и CASE ... END в столбце diff_type вместе с вышеупомянутым предложением where в запрос 2 таблиц с одинаковой спецификацией для различий
Это, вероятно, даст те же результаты, но в одном запросе.
UNION удалит дубликаты, поэтому просто объедините их вместе, а затем выполните поиск чего-либо с более чем одной записью. Учитывая «код» в качестве первичного ключа, вы можете сказать:
изменить 0: изменено, чтобы включить различия в самом поле PK
изменить 1: если вы используете это в реальной жизни, обязательно укажите фактические имена столбцов. Не используйте точку-звездочку, так как операция UNION требует, чтобы наборы результатов имели точно совпадающие столбцы. Этот пример сломается, если вы добавите / удалите столбец из одной из таблиц.
select dt.*
from
data_tbl dt
,(
select code
from
(
select * from feed_tbl
union
select * from data_tbl
)
group by code
having count(*) > 1
) diffs --"diffs" will return all differences *except* those in the primary key itself
where diffs.code = dt.code
union --plus the ones that are only in feed, but not in data
select * from feed_tbl ft where not exists(select code from data_tbl dt where dt.code = ft.code)
union --plus the ones that are only in data, but not in feed
select * from data_tbl dt where not exists(select code from feed_tbl ft where ft.code = dt.code)