Мне нужен код SQL для решения проблемы комбинации таблиц, описанной на ниже:
Таблица старые данные: старая таблица
name version status lastupdate ID
A 0.1 on 6/8/2010 1
B 0.1 on 6/8/2010 2
C 0.1 on 6/8/2010 3
D 0.1 on 6/8/2010 4
E 0.1 on 6/8/2010 5
F 0.1 on 6/8/2010 6
G 0.1 on 6/8/2010 7
Таблица новые данные: новая таблица
name version status lastupdate ID
A 0.1 on 6/18/2010
#B entry deleted
C 0.3 on 6/18/2010 #version_updated
C1 0.1 on 6/18/2010 #new_added
D 0.1 on 6/18/2010
E 0.1 off 6/18/2010 #status_updated
F 0.1 on 6/18/2010
G 0.1 on 6/18/2010
H 0.1 on 6/18/2010 #new_added
H1 0.1 on 6/18/2010 #new_added
различие новых данных и старой даты:
B запись удален
C версия записи обновляется
E состояние записи обновляется
Запись C1/H/H1, новая добавленный
То, что я хочу, всегда сохраняет идентификатор - отношения отображения имени в старой таблице данных, неважно, как данные изменились позже, иначе имя всегда имеет число уникального идентификатора, связывают с ним.
Если запись имеет обновление, то обновите данные, если запись новая добавленный, вставьте в таблицу, затем дают новый присвоенный уникальный идентификатор. Если запись была удалена, удалите запись и не снова используйте тот идентификатор позже.
Однако я могу только использовать SQL с простым выбором или обновить оператор затем, он может слишком трудный для меня для написания такого кода, затем я надеюсь, что кто-то с экспертными знаниями может дать направление, никакие детали, необходимые на различном из варианта SQL, стандартный код SQL, как образец достаточно.
Заранее спасибо!
Пожелания
KC
======== Я перечислил свой проект sql здесь, но не уверенный, если он работает, кто-то с экспертными знаниями, прокомментируйте, Спасибо!
составьте таблицу tmp как выбор * от старого
обновите tmp, где имя в (выбирают имя из нового),
вставьте в tmp (состояние версии имени lastupdate, идентификатор) устанавливает idvar = макс. (выберите макс. (идентификатор) из tmp), + 1 выбор * от (выбирают new.name new.version new.status new.lastupdate новый. Идентификатор от старого, нового, где old.name <> new.name)
удалите из tmp где (выбирают???)
Вы не упомянули, какую СУБД вы используете, но если вы используете SQL Server, то одним из действительно хороших вариантов является оператор SQL MERGE
. См: http://www.mssqltips.com/tip.asp?tip=1704
Оператор MERGE в основном работает как отдельные вставки, обновления и удаления в рамках одного оператора. Вы указываете "Источник" набор записей и "целевую" таблицу, а также соединение между ними. Затем вы указываете тип модификации данных который должен произойти, когда записи между двумя данными совпадают или не совпадают. MERGE очень полезен, особенно когда речь идет о загрузке таблиц хранилища данных, которые могут быть очень большими и требуют определенных действий при наличии или отсутствуют.
Пример:
MERGE Products AS TARGET
USING UpdatedProducts AS SOURCE
ON (TARGET.ProductID = SOURCE.ProductID)
--When records are matched, update
--the records if there is any change
WHEN MATCHED AND TARGET.ProductName <> SOURCE.ProductName
OR TARGET.Rate <> SOURCE.Rate THEN
UPDATE SET TARGET.ProductName = SOURCE.ProductName,
TARGET.Rate = SOURCE.Rate
--When no records are matched, insert
--the incoming records from source
--table to target table
WHEN NOT MATCHED BY TARGET THEN
INSERT (ProductID, ProductName, Rate)
VALUES (SOURCE.ProductID, SOURCE.ProductName, SOURCE.Rate)
--When there is a row that exists in target table and
--same record does not exist in source table
--then delete this record from target table
WHEN NOT MATCHED BY SOURCE THEN
DELETE
--$action specifies a column of type nvarchar(10)
--in the OUTPUT clause that returns one of three
--values for each row: 'INSERT', 'UPDATE', or 'DELETE',
--according to the action that was performed on that row
OUTPUT $action,
DELETED.ProductID AS TargetProductID,
DELETED.ProductName AS TargetProductName,
DELETED.Rate AS TargetRate,
INSERTED.ProductID AS SourceProductID,
INSERTED.ProductName AS SourceProductName,
INSERTED.Rate AS SourceRate;
SELECT @@ROWCOUNT;
GO
Позвольте мне начать с конца:
В #4 вы удалите все строки в tmp; вы хотели сказать WHERE tmp.name NOT IN (SELECT name FROM new)
; аналогично #3 не является правильным синтаксисом, но если бы это было так, то он попытался бы вставить все строки.
Что касается #2, почему бы не использовать автоматический инкремент для ID
?
Что касается #1, если ваша таблица tmp такая же, как и new, запросы #2-#4 не имеют смысла, если только вы не измените (обновите, вставите, удалите) new
таблицу каким-либо образом.
Но (!), если вы обновляете таблицу new
и в ней есть поле с автоинкрементом на ID
и если вы правильно обновляете таблицу (используя ID
) из приложения, то вся ваша процедура не нужна (!).
Итак, важно то, что вы не должны проектировать систему так, чтобы она работала, как описано выше.
Чтобы понять концепцию обновления данных в базе данных со стороны приложения, посмотрите примеры здесь (php/mysql).
Кроме того, для правильного синтаксиса ваших запросов пройдитесь по базовой версии команд SET, INSERT, DELETE и SELECT (без этого не обойтись).
Примечание. Если вас беспокоит производительность, вы можете пропустить весь этот ответ: -)
Если вы можете перепроектировать, имейте 2 таблицы - одна с данными, а другая с именем-идентификатором связи. Что-то вроде
table_original
name version status lastupdate
A 0.1 on 6/8/2010
B 0.1 on 6/8/2010
C 0.1 on 6/8/2010
D 0.1 on 6/8/2010
E 0.1 on 6/8/2010
F 0.1 on 6/8/2010
G 0.1 on 6/8/2010
и name_id
name ID
A 1
B 2
C 3
D 4
E 5
F 6
G 7
Когда вы получите table_new с новым набором данных
Примечание: я думаю, здесь есть некоторая двусмысленность в отношении удаления
Если запись была удалена, удалите запись и не используйте этот идентификатор позже.
Если имя A будет удалено и снова появится в более позднем наборе обновлений, хотите ли вы, чтобы a. повторно использовать исходный идентификатор, помеченный как A, или b. сгенерировать новый идентификатор?
Если это b. вам нужна колонка "Удалено"? в name_id и последнем шаге
4. установить Удалено? = Y, где имя не указано в table_original
и 2. исключает "Удалено"? = Y записей.
Вы также можете сделать то же самое без таблицы name_id, основываясь на логике, что единственное, что вам нужно от table_old, - это ссылки name - ID. Все остальное, что вам нужно, находится в table_new,
Это работает в Informix и дает именно тот дисплей, который вам нужен. Казалось бы, то же самое или подобное должно работать в MySQL. Хитрость здесь в том, чтобы получить объединение всех имен во временную таблицу и левое соединение, чтобы можно было сравнить значения из двух других.
SELECT DISTINCT name FROM old UNION SELECT DISTINCT name FROM new INTO TEMP _tmp; SELECT CASE WHEN b.name IS NULL THEN '' ELSE aa.name END AS name, CASE WHEN b.version IS NULL THEN '' WHEN a.version = b.version THEN a.version ELSE b.version END AS version, CASE WHEN a.status = b.status THEN a.status WHEN b.status IS NULL THEN '' ELSE b.status END AS status, CASE WHEN a.lastupdate = b.lastupdate THEN a.lastupdate WHEN b.lastupdate IS NULL THEN null ELSE b.lastupdate END AS lastupdate, CASE WHEN a.name IS NULL THEN '#new_added' WHEN b.name IS NULL THEN '#' || aa.name || ' entry deleted' WHEN a.version b.version THEN '#version_updated' WHEN a.status b.status THEN '#status_updated' ELSE '' END AS change FROM _tmp aa LEFT JOIN old a ON a.name = aa.name LEFT JOIN new b ON b.name = aa.name;
предварительный подход, я понятия не имею, работает ли он нормально ......
СОЗДАТЬ ТРИГГЕР auto_next_id ПОСЛЕ ВСТАВИТЬ В ТАБЛИЦУ ДЛЯ КАЖДОЙ СТРОКИ НАЧИНАТЬ ОБНОВЛЕНИЕ таблицы SET uid = max (uid) + 1; КОНЕЦ;
Если я правильно понял, что вам нужно, основываясь на комментариях в двух таблицах, я думаю, вы можете значительно упростить свою проблему, если не будете сливать или обновлять старую таблицу, потому что вам нужна новая таблица с ID в старой таблице, когда они существуют, и новые ID, когда они не существуют, верно?
Новые записи: в новой таблице уже есть новые записи - ОК (но им нужен новый ID). Удаленные записи: их нет в таблице new - OK Обновленные записи: уже обновлены в таблице new - OK (нужно скопировать ID из таблицы old) Неизмененные записи: уже в таблице new - OK (нужно скопировать ID из таблицы old)
Таким образом, единственное, что вам нужно сделать, это: (a) скопировать идентификаторы из старой таблицы в новую, если они существуют (b) создать новые идентификаторы в таблице new, если они не существуют в таблице old (c) скопировать таблицу new в таблицу old.
(a) UPDATE new SET ID = IFNULL((SELECT ID FROM old WHERE new.name = old.name),0);
(b) UPDATE new SET ID = FUNCTION_TO GENERATE_ID(new.name) WHERE ID = 0;
(c) Drop table old; CREATE TABLE old (select * from new);
Поскольку я не знаю, какую базу данных SQL вы используете, в пункте (b) вы можете использовать sql-функцию для генерации уникального идентификатора в зависимости от базы данных. В SQL Server - newid(), в postgresql (не слишком старых версиях) - now(), поскольку ее точность кажется достаточной (но не в других базах данных, таких как MySQL, например, поскольку я думаю, что точность ограничена секундами)
Edit: Sorry, I hadn't seen you're using sqlite and python. В этом случае вы можете использовать функцию str(uuid.uuid4()) (модуль uuid) в python для генерации uuid и заполнения ID в новой таблице, где ID = 0 в шаге (b). Таким образом, при необходимости вы сможете объединить 2 независимые базы данных без конфликтов идентификаторов.
Почему вы не используете для этого UUID? Сгенерируйте его один раз для плагина и включите / сохраните в плагине, а не в БД. Теперь, когда вы упомянули python, вот как его сгенерировать:
import uuid
UID = str(uuid.uuid4()) # this will yield new UUID string
Конечно, он не гарантирует глобальной уникальности, но вероятность того, что вы получите такую же строку в своем проекте, довольно низка.