Как вернуть уникальные строки из двух таблиц в SQL Server

У меня есть две таблицы учеников в SQL Server, базовая таблица из моего первоначального прогона и новая таблица из прогона следующих дней. Каждая таблица имеет такую ​​информацию, как Building, UserID LastName FirstName:

Базовый стол:

3   2509381 Brooks  Corey   
3   2527352 Doss    Dawone  
14  170163  Belin   Teaira  
14  2465666 Arlington   Xavier  
14  2465941 Smith   Jerald  
14  2466022 Junious Dontrell    
14  2466898 Shelton Rayonna 
14  2468144 Sullivan    James   
14  2468612 Brown   Jerron  
14  2469949 Quinn   Jordan

Новая таблица:

3   2527352 Doss    Dawone  
14  170163  Belin   Teaira  
14  2465666 Arlington   Xavier  
14  2465941 Smith   Jerald  
14  2466022 Junious Dontrell    
14  2466898 Shelton Rayonna 
14  2468144 Sullivan    James   
14  2468612 Brown   Jerron  
3   2469949 Quinn   Jordan  
14  1234512 Davis   John

В моем примере здесь Брукс был удален, Куинн изменил номер здания, а Дэвис был добавлен. Мне бы хотелось, чтобы моя таблица результатов выглядела примерно так.

Аудиторская таблица:

3   2509381 Brooks  Corey   
14  2469949 Quinn   Jordan  
14  1234512 Davis   John

Я использую два, кроме запросов, чтобы получить эти результаты

выберите * из теста1, кроме выбора * из теста2

3   2509381 Brooks  Corey   
14  2469949 Quinn   Jordan

выберите * из теста2, кроме выбора * из теста1

3   2469949 Quinn   Jordan  
14  1234512 Davis   John

Как мне заставить их выводить все дельты в таблицу аудита. Я попытался объединить два моих избранных утверждения, но это не сработало...

Источник

2 ответа

Решение

Я предполагаю, что числа студентов уникальны? Или есть какой-то способ определить, что такое уникальная строка, если вы не указали все столбцы. Вам это нужно, иначе вы не сможете обнаружить, что нового и что изменилось. Или что удалено и добавлено.

Обычно мы используем запрос, который объединяет элементы в одно уникальное поле, в данном случае это может быть UserID. Ваше объединение должно будет сравнить различные другие поля.

Внешнее объединение расскажет вам что-нибудь о вставках и удалениях. Например, я мог бы сделать это:

select *
 from base b
   full outer join new n
   on b.userid = n.userid

Если вы сделаете это, то увидите разные вещи. Если для b.userid есть значения NULL, но нет n.userid, то у вас есть новые строки в новой таблице, которых нет в базовой таблице. Если у вас есть обратное, то вы удалили. Для изменений вы можете сравнить значения каждого столбца, а затем определить, что отличается. Вы можете решить эту проблему в нескольких запросах, каждый из которых ищет отдельные вещи, такие как запрос на вставку, запрос на удаление и запрос на изменение. Вы можете объединить их вместе для результата, который вы можете вставить в таблицу аудита.

Примечание. Во многих системах мы использовали бы триггер для захвата этих изменений в режиме реального времени для аудита (или другого механизма аудита в SQL Server).

Источник

Одним из способов является создание запроса на исключение и использование вставки в новую таблицу для сбора данных:

Создать таблицу mytable_audit (Building, UserID, Фамилия, Имя, Audit_Date)

Insert into mytable_audit
Select Building, UserID, Lastname, Firstname, getdate() from mytable2
EXCEPT 
Select Building, UserID, Lastname, Firstname, getdate() from mytable

Единственная проблема с этим подходом состоит в том, что он не скажет вам, почему данные находятся в таблице. Вы можете обойти это, сделав это подзапросом (при условии, что wayOutwest сказал, что у вас есть уникальный ключ), а затем снова присоединиться к исходной таблице. Таким образом, вы можете добавить выписку кейса, чтобы предоставить индикатор. Наконец UNION ко второму запросу проверяет удаленных студентов:

Create table mytable_audit
    (Building int, Username int, Lastname varchar(100), Firstname varchar(100), Audit_Date datetime, ChangeType varchar(100))

    Insert into mytable_audit

    Select aa.*, getdate(),
    case when aa.building<>mt.building then 'Building Changed'
         when mt.userid is null then 'Student Added'
         when aa.lastname<>mt.lastname or aa.firstname<>mt.firstname then 'Student Name Changed'
    end as changetype
    from 
    (Select Building, UserID, Lastname, Firstname from mytable2
    EXCEPT 
    Select Building, UserID, Lastname, Firstname from mytable) aa

    left join mytable mt on aa.userID=mt.userID

    UNION 

    Select Building, UserID, Lastname, Firstname, getdate(), 'Student Removed' from mytable mt
    left join mytable2 mt2 on mt.userid=mt2.userid
    where mt2.userid is null
Источник
Другие вопросы по тегам