#sql #sql-server #tsql #sql-insert
#sql #sql-сервер #tsql #sql-вставка
Вопрос:
У меня есть таблица, в которую я добавляю изменения в значениях определенных столбцов в других таблицах:
UpdatesLogTable:
Имя таблицы | ColumnFilter | FilterName | FilterValue | Разница | Дата |
---|---|---|---|---|---|
Фрукты | FruitName | Apple | 4 | 0 | 1/13/2021 |
Фрукты | FruitName | Pear | 5 | 0 | 1/13/2021 |
Когда я вставляю новые записи на основе даты последнего обновления таблиц, у меня возникает проблема с определением условий для вставки.
Например, вчера мой инвентарь Apples
был равен 4
, а сегодня равен 5
. Итак, в мою таблицу журнала обновлений я вставлю Fruits-FruitName-Apple-5
. В столбце Difference
я вычислю разницу между вчерашним и сегодняшним значением, которое равно 1
. Это будет нормально, поскольку у меня уже есть Apple в качестве значения в столбце FilterName.
Но если в моем последнем обновлении Fruits table
я добавлю новое FruitName - Peach
, как я должен проверить, что это значение не существует, и добавить его по умолчанию Difference value equal to 0
?
Ниже приведено последнее обновление от 14 января:
Фрукты | FruitsInventory | LastUpdateDate |
---|---|---|
Apple | 5 | 1/14/2021 |
Pear | 9 | 1/14/2021 |
Персик | 10 | 1/14/2021 |
И это результат, которого я ожидаю:
Имя таблицы | ColumnFilter | FilterName | FilterValue | Разница | Дата |
---|---|---|---|---|---|
Фрукты | FruitName | Apple | 4 | 0 | 1/13/2021 |
Фрукты | FruitName | Pear | 5 | 0 | 1/13/2021 |
Фрукты | FruitName | Apple | 5 | 1 | 1/14/2021 |
Фрукты | FruitName | Pear | 9 | 4 | 1/14/2021 |
Фрукты | FruitName | Персик | 10 | 0 | 1/14/2021 |
Это мой код на случай, если я не проверю, существует ли название фрукта:
INSERT INTO UpdatesLogTable(TableName, ColumnFilter, FilterName, FilterValue, [Difference], [Date])
SELECT [TableName]
, [ColumnFilter]
, [FilterName]
, [FilterValue]
, [Difference]
, LUP as [Date]
FROM (
SELECT tr.TableName
, tr.ColumnFilter
, tr.FilterName
, tr.FilterValue
, tr.[FilterValue] - ult.[FilterAbsValue] AS [Difference]
, LUP
FROM (SELECT
'Fruits' as [TableName]
, 'FruitName' as [ColumnFilter]
, FruitName as [FilterName]
, FruitsInventory AS [FilterValue]
, MAX(LastUpdateDate as date) as LUP
FROM Fruits
WHERE 1=1
) tr
RIGHT JOIN UpdatesLogTable ult
ON tr.TableName = ult.TableName and tr.ColumnFilter = ult.ColumnFilter
WHERE hus.LastUpdateDate = (SELECT MAX(CAST(LastUpdateDate as date)) FROM UpdatesLogTable)
) a
Я думал о ЛЕВОМ или ПРАВОМ соединении, но это сработает, только если я знаю, увеличивается или уменьшается разнообразие фруктов.
Комментарии:
1. Рассмотрите возможность использования триггеров
ON INSERT
иON UPDATE
Ответ №1:
Код в вашем вопросе довольно запутанный. Например:
#HistoryUpdatesSuccess
не определено.- У вас есть неправильно сформированный агрегированный подзапрос , использующий
FRUITS
. - В
JOIN
условиях отсутствует значение фильтра.
Однако, я думаю, я понимаю, что вы хотите сделать — вставить новые строки со DIFF
столбцом, который включает в себя самые последние данные из существующих данных.
Это похоже LEFT JOIN
на самую последнюю запись в таблице истории. Остальное просто COALESCE()
.
INSERT INTO UpdatesLogTable(TableName, ColumnFilter, FilterName, FilterValue, [Difference], [Date])
SELECT f.TableName, f.ColumnFilter, f.FilterName, f.FilterValue,
COALESCE(f.FilterValue - ult.FilterValue, 0),
LUP as [Date]
FROM FRUITS F LEFT JOIN
(SELECT ult.*,
ROW_NUMBER() OVER (PARTITION BY TableName, ColumnFilter ORDER BY LastUpdateDate) as seqnum
FROM UpdatesLogTable ult
) ult
ON tr.TableName = ult.TableName AND
tr.ColumnFilter = ult.ColumnFilter AND
tr.FilterName = ult.FilterName
seqnum = 1;
Комментарии:
1. Но сработает ли это, если у меня появится новый фрукт. Я думаю, что левое соединение проигнорирует его, поскольку оно ранее не существовало в UpdatesLogTable. Это моя главная проблема
2. @Yana . . . Нет,
LEFT JOIN
он не игнорирует это. Вот почему запрос используетLEFT JOIN
, а не какой-либо другой типJOIN
.3. Я исправил #HistoryUpdatesSuccess 🙂 Теперь это правильное имя.
4. О, я понимаю, что здесь произошло 🙂 Вы оставили присоединиться к UpdatesLogTable к таблице Fruits, и именно поэтому она будет работать. Спасибо! Я попробовал обратное, и именно поэтому у меня это не сработало. Не является ROW_NUMBER() … РАЗДЕЛЕНИЕ очень медленное, если у нас большие таблицы?
5. @Yana . , , Все происходит медленно в больших таблицах, поэтому альтернативы не обязательно будут намного быстрее (на самом деле коррелированный подзапрос часто выполняется немного быстрее, но усложняет запрос, особенно для внешнего соединения).