При изменении записи в таблице users создается история изменений, и эти изменения записываются в две различные таблицы, между которыми нет прямой связи: Исходная таблица: users (id, name, birthay) Таблицы истории изменений: names (id, name, user_id) и birthays (id, birthay, user_id) Каждый раз, когда в таблице users происходит изменение, в обе таблицы истории добавляется соответствующая запись, даже если старые значения не были изменены. Вопрос: как можно получить выборку всех записей из обеих таблиц для конкретного user_id так, чтобы в результирующем наборе было столько же строк, сколько в любой из этих таблиц для данного пользователя? Необходимо избежать синхронизации по дате, так как записи могут быть добавлены с разницей в миллисекунды. Требуется, чтобы записи просто группировались последовательно. Как можно объединить запросы к этим таблицам без использования циклов? Не могу вспомнить, как это сделать. Возможно, стоит создать виртуальную таблицу для каждой из таблиц и назначить динамический id, по которому будет выполняться группировка. Как еще можно решить эту задачу? Использование таблицы Pivot рассматривается как крайний случай, и хотелось бы найти решение без ее применения.
Чтобы объединить данные из двух таблиц `names` и `birthays`, так чтобы каждая запись была сопоставлена с соответствующей записью пользователя, можно использовать конструкцию SQL, которая позволяет объединить результаты, добавляя дополнительные строки, чтобы сделать набор значений одинаковым по количеству. В данном случае можно использовать `FULL OUTER JOIN` для соединения двух таблиц, а затем использовать нумерацию строк, чтобы связать их.
Мы можем добавить уникальные идентификаторы к записям в каждой таблице и затем использовать их для соединения.
Вот пример запроса, который позволяет это сделать:
```sql
WITH RankedNames AS (
SELECT id, name, user_id, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY id) AS rn
FROM names
WHERE user_id = @user_id -- Замените @user_id на нужный вам user_id
),
RankedBirthays AS (
SELECT id, birthay, user_id, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY id) AS rn
FROM birthays
WHERE user_id = @user_id -- Замените @user_id на нужный вам user_id
)
SELECT
COALESCE(n.id, b.id) AS id,
n.name,
b.birthay
FROM
RankedNames n
FULL OUTER JOIN
RankedBirthays b ON n.rn = b.rn
ORDER BY COALESCE(n.rn, b.rn);
```
### Описание запроса:
1. **CTE (Common Table Expression)**: Используем два CTE, `RankedNames` и `RankedBirthays`, чтобы создать нумерацию строк для каждой записи, разделяя их по `user_id`.
2. **ROW_NUMBER()**: Функция `ROW_NUMBER()` используется для присвоения уникального номера каждой строке в каждой из таблиц.
3. **FULL OUTER JOIN**: Соединяем две таблицы по нумерации строк (rn). Это обеспечит наличие всех записей из обеих историй изменений, даже если у одной из таблиц записей больше, чем у другой.
4. **COALESCE**: Используется для выбора ненулевых значений `id` из двух таблиц, чтобы уникальный идентификатор был из одной из таблиц.
5. **ORDER BY**: Упорядочиваем результаты по нумерации строк, чтобы сохранить последовательность.
Такой подход позволит вам получить все изменения для конкретного пользователя в одной таблице, не теряя информации и ей не требуя дополнительных синхронизаций по времени изменений.
Чтобы любую таблицу обложить историей изменения, нужно создать следующую обвязку:
1. Создать таблицу <название исходной таблицы>__history
Поля таблицы:
Первичный ключ - history_id,
Копируем названия полей исходной таблицы как есть (всем полям даете возможность быть null).
Делаем внешние ключи таблицы истории к исходной таблице (обычно id- исходной таблицы нужно привязать) и к другим таблицам, такие же как в исходной таблице, но опускаем ограничение уникальности ключей. Не используем каскадное обновление значение ключей.
Добавляете поле - вид операции над строкой исходной таблицы (вставка, обновление, удаление).
И самое главное, в таблице истории должно быть поле next_history_id - делаете по нему связанный список истории изменения одного поля. Как это поле работает? - см. пункт 2.
2. Создаете триггер/ы на события вставки, изменения, удаления для исходной таблицы в ней пишите тело со следующими действиями:
Читаете id тронутой событием записи из исходной таблицы.
Определяете history_id по данной id, где next_history_id is null - запоминаете в локальной переменной как before_history_id.
Если действие не удаление, то создаете запись в таблице истории из new-полей, которые пришли в insert/update для исходной таблицы, добавляете значение поля вида действия, next_history_id делаете null, получаете history_id этой вставленной записи - записываете в переменную new_history_id.
Если действие - удаление, то new поля вычитывать не нужно, нужно записать значение поля вида действия, next_history_id делаете null, получаете history_id этой вставленной записи - записываете в переменную new_history_id.
Делаете обновление поля next_history_id = new_history_id у записи истории со значением ключа before_history_id.
Тело триггера готово.
На любое действие в исходной таблице будет создаваться запись в истории, с указанием действия, а также будет формироваться цепочка изменений конкретной записи через атрибут next_history_id.
Чтобы вычитать последнее изменение по строчке исходной таблицы, нужно обратиться в таблицу истории по id от исходной таблицы и next_history_id is null.