SQL разница между 2 столбцами таблицы
Сценарий:
Завод, производящий стальные трубы каждый день различных размеров и калибров (swg), регистрируется и хранится в таблице pipe_production (внешний ключ pipe_id к таблице труб). У завода также есть клиенты, которым он регулярно продает трубы, по запросу создается счет-фактура и регистрируется в таблице счетов-фактур. Впоследствии связанные каналы относительно каждого счета-фактуры сохраняются в таблице pipe_invoices. Когда член команды продаж авторизует счет-фактуру, столбец авторизованный переключается с false на true (0 => 1 in) в таблице счетов-фактур и сигнализирует о том, что эти трубы будут проданы и должны быть удалены со склада.
Я ищу запрос для создания таблицы запасов, чтобы точно оценить трубу на месте. Однако я ищу только разницу между произведенными трубами и авторизованными счетными трубами.
Приложение строится на фреймворке Laravel.
Таблица: трубы
id | description | swg | min_weight | max_weight
1 | 2" X 2" | 16 | 10 | 11
2 | 2" X 2" | 18 | 8 | 19
3 | 1" X 2" | 18 | 4 | 6
Таблица: pipe_productions
id | pipe_id | quantity | production_date
1 | 1 | 1000 | 2020-10-1
2 | 2 | 2000 | 2020-10-1
3 | 3 | 5500 | 2020-10-1
Таблица: счета-фактуры
id | client_id | authorised | loaded | invoice_date
1 | 1 | 0 | 0 | 2020-10-09
2 | 2 | 1 | 0 | 2020-10-09
3 | 2 | 1 | 1 | 2020-10-09
Таблица: pipe_invoices
id | invoice_id | pipe_id | quantity
1 | 1 | 3 | 2000
2 | 1 | 1 | 1000
3 | 2 | 2 | 1000
Редактировать: Мой текущий запрос, который получает только разницу между pipe_production и pipe_invoices. Он не учитывает случай, когда счет-фактура не авторизован и не должен быть удален.
SELECT *, coalesce(a.quantity, 0)-coalesce(b.quantity, 0) as diff
FROM
(SELECT pipe_id, sum(quantity) as quantity
FROM pipe_productions
GROUP BY pipe_id) a
LEFT JOIN
(SELECT pipe_id, sum(quantity) as quantity
FROM pipe_invoices
GROUP BY pipe_id) b
on a.pipe_id = b.pipe_id
LEFT JOIN pipes
on a.pipe_id = pipes.id
WHERE coalesce(a.quantity, 0)-coalesce(b.quantity, 0) != 0
ORDER BY swg asc, pipe_description desc
Ответы - SQL разница между 2 столбцами таблицы / SQL difference between 2 table columns
31.10.2020 12:08:00
Я предполагаю, что вам нужно только немного приспособиться к вашему запросу и присоединиться к invoices в вашем заявлении b:
SELECT *, coalesce(a.quantity, 0)-coalesce(b.quantity, 0) as diff
FROM
(
SELECT pipe_id, sum(quantity) as quantity
FROM pipe_productions
GROUP BY pipe_id
) a
LEFT JOIN
(
SELECT pipe_id, sum(quantity) as quantity
FROM pipe_invoices pi
JOIN invoices i ON pi.invoice_id = i.id
WHERE i.authorised = 1
GROUP BY pipe_id
) b
on a.pipe_id = b.pipe_id
LEFT JOIN pipes
on a.pipe_id = pipes.id
WHERE coalesce(a.quantity, 0)-coalesce(b.quantity, 0) != 0
ORDER BY swg asc, pipe_description desc