mysql - 内部连接 ​​4 个表，带 group、order by、having 子句

Question

我有 4 个表，我想提取:id、nume、localitate、masina_id、nr_inmatriculare、an_fabricatie、rafinarie、marca 和 sum (quantity+deliver_quantity) 作为total_quantity group by an_fabricatie , Order by marca, 放一些having clouse。

我不知道怎么做。

我的查询如下，但我认为不正确。

    select c.id, c.nume,c.localitate,l.masina_id, i.nr_inmatriculare, i.an_fabricatie, 
i.rafinarie, m.marca from clienti c inner join livrari l on c.id = l.id  inner join incarcari I on l.incarcare_id = l.livrari_id inner join masina m on i.id_marca = m.id, sum(select quantity, deliver_quantity) as total_quantity  group by an_fabricatie having quantity >1000 order by marca;

Incarcari table
Id|livrari_id|id_marca|nr_inmatriculare|an_fabricatie|rafinarie|aviz_incarcare|quantity|

Livrari table
Id|masina_id|client_id|incarcare_id|deliver_quantity|aviz_livrare

Masini table
Id|numar_inmatriculare|marca|an_fabricatie|

Clienti table
Id|nume|localitate|date_add|date_upd|

Answer 1

SELECT c.id, c.nume, c.localitate, l.masina_id, i.nr_inmatriculare, i.an_fabricatie, i.rafinarie, m.marca, (SUM(i.quantity) + SUM(l.deliver_quantity)) AS total_quantity
FROM clienti c 
INNER JOIN livrari l ON c.id = l.id 
INNER JOIN incarcari i ON l.incarcare_id = i.livrari_id 
INNER JOIN masini m ON i.id_marca = m.id
GROUP BY i.an_fabricatie, c.id, c.nume,c.localitate,l.masina_id, i.nr_inmatriculare, i.rafinarie, m.marca
HAVING i.quantity > 1000 
ORDER BY m.marca DESC;