Как добавить суммированные значения вместе с соединениями в sql
у меня 3 стола
таблица 1 и таблица 2 связаны с таблицей 3 с внешним ключом "ItemId"
Я хочу написать запрос, который вернет некоторые статистические данные на основе этих таблиц.
в таблице 3 указан столбец имени, остальные — из двух других
SELECT t1.ItemId, name, currentQuantity, previousQuantity, currentValue, previousValue
FROM (
SELECT ItemId, SUM(quantity) as currentQuantity, SUM(quantity * purchaseNetPrice) AS currentValue
FROM table_1
GROUP BY ItemId
) AS t1
LEFT JOIN (
SELECT ItemId, SUM(quantity) as previousQuantity, SUM(quantity * purchaseNetPrice) AS previousValue
FROM table_2
GROUP BY ItemId
) AS t2 ON t1.ItemId = t2.ItemId
INNER JOIN table_3 ON table_1.ItemId = table_3.ItemId
Это выведет
ItemId | name | currentQuantity | previousQuantity| currentValue | previousValue
-------+------+-------------------+-----------------+--------------+---------------
1 |ITEM1 | 4500 | 27000 | 523000 | 3240000 |
2 |ITEM2 | 5800 | 15000 | 386000 | 2250000 |
На данный момент это нормально, но я также хочу рассчитать разницу в количестве и стоимости, поэтому
ItemId | name | currentQuantity | previousQuantity| currentValue | previousValue | qDiff | valDiff
-------+------+-------------------+-----------------+--------------+---------------
1 |ITEM1 | 4500 | 27000 | 523000 | 3240000 | diff | diff
2 |ITEM2 | 5800 | 15000 | 386000 | 2250000 | diff | diff
со следующей логикой:
разница количества -> текущее количество - предыдущее количество
разница значений -> текущее значение - предыдущее значение
Где я могу разместить это в одном запросе?
Хм.. интересно. Это работает, по какой-то причине, когда я пробовал это раньше, я получил сообщение об ошибке, говорящее, что столбец не существует.
@Michal, вы должны добавить это как ответ :) (поскольку это работает для ОП)
Ответы 2
Вы поняли большую часть этого правильно. Я бы исправил это, используя факторинг подзапросов (WITH AS) для чистого кода.
WITH t1_summary as
(
SELECT ItemId, SUM(quantity) as currentQuantity, SUM(quantity * purchaseNetPrice) AS currentValue
FROM table_1
GROUP BY ItemId
),
t2_summary as (SELECT ItemId, SUM(quantity) as previousQuantity, SUM(quantity * purchaseNetPrice) AS previousValue
FROM table_2
GROUP BY ItemId
)
SELECT t3.ItemId, name, currentQuantity, previousQuantity, currentValue, previousValue, currentQuantity - previousQuantity as quantity_difference, currentValue - previousValue as value_difference
from t3,t1_summary,t2_summary
where t1_summary.ItemId = t3.ItemId
and t2_summary.ItemId = t3.ItemId
Вычисление дополнительных столбцов во внешнем SELECT и присвоение псевдонимов столбцам с AS выполняет эту работу:
SELECT t1.ItemId,
name,
currentQuantity,
previousQuantity,
currentValue,
previousValue,
currentQuantity - previousQuantity as qDiff,
currentValue - previousValue as valDiff
FROM ....
Не
SELECT t1.ItemId, ....., currentQuantity - previousQuantity as qDiff, currentValue - previousValue as valDiff FROM (....сделать?