mysql - MySQL 最佳方法

Question

这个查询是有效的。我想添加状态 = 100 的包的数量。

我在主页上运行此查询以获取驱动程序 ID 1 拥有的所有路线和包裹数量。在同一页面上，我还想获取已完成包的数量

是否可以在一个查询中完成？

SELECT r.ID, r.Name, r.Date, count(p.Route_ID) as Num_Package
FROM drvapp_routes as r 
INNER join drvapp_packages as p
ON p.Route_ID = r.ID 
WHERE r.Driver_ID = 1 
GROUP BY r.ID
ORDER by r.Name ASC 

Route Table

+---------+-----------------
| ID | Driver_ID | Name
+---------+-----------------
| 1  |      1001 | Route 1    
| 2  |      1001 | Route 2   
| 3  |      1001 | Route 3  
+---------+-----------------

Package Table

+---------+---------------
| ID | Route ID  | Status
+---------+---------------
| 1  |      1    |   100
| 2  |      2    |   70  
| 3  |      3    |   50
| 4  |      3    |   100
| 5  |      1    |   100
+---------+---------------


Result should be 

+---------+-----------------------------------------
| ID | Route Name | Num_Package | Completed_Route
+---------+-----------------------------------------
| 1  |    Route 1 |       2     |               2
| 2  |    Route 2 |       1     |               0
| 3  |    Route 3 |       2     |               1
+---------+-----------------------------------------

标签： mysql