LEFT JOIN с множественным вложенным выбором
Следующее утверждение на удивление работает, но я не уверен, что объединение одной и той же таблицы 3 раза будет эффективным. Пришлось отключить ONLY_FULL_GROUP_BY, чтобы он заработал.
В игре 2 стола. Одна - это основная таблица с информацией о дистрибьюторе, вторая - это таблица покупок, которая содержит amount, date и id соответствующего дистрибьютора в основной таблице (ассоциированная).
Мне нужны 3 вещи. Годовая сумма продаж, которая СУММИрует объем продаж определенного Дистрибьютора за текущий год. Продажи в прошлом году, что и в предыдущем году. Тогда, наконец, просто получите последнюю дату покупки и сумму.
Пользователь должен иметь возможность фильтровать по этим значениям (lys, ytd и т. д.), Поэтому объединение их как переменных кажется правильным решением. Размер БД составляет около 7000 записей.
SELECT
d.*,
ytd_total,
lys_total,
last_amount,
last_purchase
FROM Distributor as d
LEFT JOIN (
SELECT
assoc, SUM(amount) ytd_total
FROM purchases
WHERE db = 1 AND purchase_date >= '{$year}-01-01'
GROUP BY assoc
) AS ytd
ON ytd.assoc = d.id
LEFT JOIN (
SELECT
assoc, SUM(amount) lys_total
FROM purchases
WHERE db = 1 AND purchase_date BETWEEN '{$lyear}-01-01' AND '{$lyear}-12-31'
GROUP BY assoc
) AS lys
ON lys.assoc = d.id
LEFT JOIN (
SELECT
assoc, amount last_amount, purchase_date last_purchase
FROM purchases
WHERE db = 1
GROUP BY assoc
) AS lst
ON lst.assoc = d.id
WHERE ........
Ответы 1
Вы можете выполнять больше работы с каждым запросом агрегирования. Я думаю, это больше то, что вам нужно:
select d.*, pa.ytd_total, pa.lys_total, pa.last_purchase_date, p.amount
from distributor d left join
(select p.assoc,
sum(case when p.purchase_date >= '{$year}-01-01' then p.amount end) as ytd_total,
sum(case when p.purchase_date BETWEEN '{$lyear}-01-01' AND '{$lyear}-12-31' then p.amount end) as lys_total,
max(p.purchase_date) as last_purchase_date
from purchases p
where p.db = 1
group by p.assoc
) pa left join
purchases p
on pa.assoc = p.assoc and pa.last_purchase_date = p.purchase_date;
Спасибо. Однако я получаю синтаксическую ошибку [42000]. Я забыл упомянуть, что у меня есть предложение WHERE в конце моего запроса, чтобы пользователь мог дополнительно фильтровать поля из таблицы распространителя.
Если вы используете MySQL 8.0, вы можете изучить Оконные функции.