ФорумПрограммированиеПыхнуть хотите?F.A.Q. → результат несоответствия двух таблиц

результат несоответствия двух таблиц

  • ghost

    Сообщения: 883 Репутация: N Группа: Джедаи

    Spritz 10 августа 2007 г. 15:47

    Попадалась мне как-то нетривиальная задачка
    "собственно есть 2 таблицы, нужно создать запрос так, что бы вывести результат несоответствия двух таблиц. возможно изменение в строке. а возможно и отсутствие строки"
    Как выяснилось дело было вот в чем:

    Были основныя таблицы tab1, tab2 в которой хранились записи:

    id - int
    field1
    field2
    field3
    field4

    количество полей и тип данных разные

    были вспомагательные таблицы - tmp_tab1, … такой же структуры - в которые импортировалась актуальная информация, которую желательно было бы занести в основную таблицу. C временными таблицами работали юзеры, основные таблицы требовалось обновлять, но не автоматом, а обдумано, под контролем оператора.

    Соответственно возник вопрос поиска новых и измененных записей, и их вывод оператору. id для этих целей не подошол - это был просто автоинкимент как у основной, так и у временной таблицы. Следующим шагом стало формирование запроса, который бы выбрал записи из таблиц, которые были изменены.. или проще точнее: записи основной таблицы - у которых нет однозначного соответствия (по всем полям, кроме id) во временной и наоборот - все поля временной таблы, у которых нет соотв. в основной.

    Делается это довольно просто - составляем ключ записи, выбираем ключи из обоих таблиц, и среди всех ключей выбираем только те - которые имеются в количестве 1 штука

    SELECT str, COUNT(*) AS  co 
    FROM
    (SELECT CONCAT(field1, field2, field3, field4) AS str from tab1
    UNION ALL
    SELECT CONCAT(field1, field2, field3, field4) AS str from tab2)
    AS tmp
    GROUP BY str
    HAVING co=1


    На что здесь нужно обратить внимание
    1. ключ состоит из простой конкатенации значений полей, это на самом деле не правильно, например записи (field1, field2): {1,22} и {12, 2} будут идентичнымим
    этого достаточно просто избежать, добавив разделитель "SELECT CONCAT(field1, "___***___", field2, "___***___", field3, "___***___", field4)"
    2. UNION ALL - здесь ALL существенное дополнение, в противном случае SQL свернент автоматически совпадающие записи, и выдаст только различные
    3. Это все имеет смысл только в случае когда в исходной таблице (и во временной) нет дублей, в противном случае альтернативный алгоритм может давать сбои (например не COUNT(*) AS co , а MOD(COUNT(*), 2) AS co ) - например есть 2 одинаковые записи и обе были изменены.
    4. про оптимизацию при использовании условий в HAVING можно забыть. Есдли таблицы большие - запрос будет выполняться долго - будут выбраны все записи из обоих таблиц, сгруппированы, вычисленно co, и лиш потом отсеяны ненужные

    а в остальном, прекрасная маркиза… запрос вполне рабочий :)

Пожалуйста, авторизуйтесь, чтобы написать комментарий!