mysql从三个表中查询

Question

我正在使用此查询

SELECT
orders_list.nr_comanda_orders AS Comanda,
SUM(orders_list.cantitate) AS Total, 
SUM(orders_list.qty_to_add) AS InStoc,
COUNT(entries_list.id) AS Picked
FROM orders_list 
LEFT JOIN entries_list ON (entries_list.file_id = orders_list.id AND entries_list.orders_list_id = orders_list.nr_comanda_orders)
GROUP BY orders_list.nr_comanda_orders
ORDER BY orders_list.nr_comanda_orders DESC

有这个结果

Comanda    | Total | InStoc | Picked
-----------|----------------|--------
 AAY280838 |   64  | 6      |   0
 AAY280837 |   50  | 8      |   0
 AAY280836 |   89  | 8      |   0

和第二个查询

SELECT
entries_list.orders_list_id AS Comanda,
SUM(orders_list.cantitate) AS Total, 
SUM(orders_list.qty_to_add) AS InStoc,
COUNT(entries_list.id) AS Picked
FROM  entries_list 
LEFT JOIN orders_list ON (entries_list.file_id = orders_list.id AND entries_list.orders_list_id = orders_list.nr_comanda_orders) 
LEFT JOIN orders_uploaded ON orders_list.file_id = orders_uploaded.id
GROUP BY 
entries_list.orders_list_id  
ORDER BY 
entries_list.orders_list_id DESC,
orders_uploaded.id_routing DESC, 
orders_uploaded.upload_date DESC, 
orders_uploaded.progress ASC

其结果如下:

Comanda    | Total   | InStoc | Picked
-----------|------------------|--------
 AAY280838 |   NULL  | NULL   |   55
 AAY280837 |   NULL  | NULL   |   39
 AAY280836 |   NULL  | NULL   |   76

您能帮我查询一下，以便我得到这个结果吗？

Comanda    | Total | InStoc | Picked
-----------|----------------|--------
 AAY280838 |   64  | 6      |   55
 AAY280837 |   50  | 8      |   39
 AAY280836 |   89  | 8      |   76

提前致谢。

Answer 1

SELECT t1.Comanda,t1.Total,t1.InStoc,t2.Picked
(
SELECT
orders_list.nr_comanda_orders AS Comanda,
SUM(orders_list.cantitate) AS Total, 
SUM(orders_list.qty_to_add) AS InStoc,

FROM orders_list 
LEFT JOIN entries_list ON (entries_list.file_id = orders_list.id AND entries_list.orders_list_id = orders_list.nr_comanda_orders)
GROUP BY orders_list.nr_comanda_orders
) t1

LEFT JOIN

(

SELECT
entries_list.orders_list_id AS Comanda,
COUNT(entries_list.id) AS Picked
FROM  entries_list 
LEFT JOIN orders_list ON (entries_list.file_id = orders_list.id AND entries_list.orders_list_id = orders_list.nr_comanda_orders) 
LEFT JOIN orders_uploaded ON orders_list.file_id = orders_uploaded.id
GROUP BY 
entries_list.orders_list_id  

) t2
ON t1.Comanda = t2.Comanda