mysql - SQL查询以计算所有具有按主键分割的重复值的行

Question

我的数据库有两个表。一份是司机名单，一份是违规名单。每个违规都有一个状态，黄色或红色，我需要计算特定驾驶员的黄色违规和红色违规次数，并按红色或黄色 ASC 或 DESC 进行排序。

驱动程序表:AutoID(违规表driverID的主键和外键)客户端ID雇主编号

违规表:违规ID(主键)地位driverID(Drivers 表中 autoID 的外键)

我运行下面的查询以获取所有红色或黄色违规

SELECT d.autoID, d.clientID, v.status FROM edsp_drivers d, edsp_violations v 
WHERE d.employerID='000000028' AND d.autoID=v.driverID 
AND (v.status = 'yellow' OR v.status='red') 
ORDER BY d.autoID ASC

结果如下:

autoID          clientID    status

000000206   000000015   Yellow

000000206   000000015   Red

000000206   000000015   Yellow

000000206   000000015   Yellow

000000206   000000015   Yellow

000000206   000000015   Yellow

000000206   000000015   Yellow

000000206   000000015   Yellow

000000206   000000015   Yellow

000000206   000000015   Yellow

000000207   000000015   Yellow

000000207   000000015   Yellow

000000367   000000015   Yellow

000000367   000000015   Yellow

000000367   000000015   Red

000000368   000000015   Red

000000369   000000015   Yellow

000000369   000000015   Yellow

000000369   000000015   Red

000000369   000000015   Yellow

000000369   000000015   Yellow

000000369   000000015   Yellow

000000398   000000015   Yellow

000000398   000000015   Yellow

我需要做的是计算所有按驱动程序分组的黄色或红色警报，并按红色/黄色 DESC 进行排序 - 这就是我遇到的问题。

希望有人能在这里提供帮助？

Answer 1

听起来你想按司机和违规颜色分组:

SELECT d.autoID, v.status, COUNT(v.status)
FROM edsp_drivers d
LEFT JOIN edsp_violations v 
    ON d.autoID = v.driverID 
    AND (v.status = 'yellow' OR v.status='red')
WHERE d.employerID = '000000028' 
GROUP BY d.autoID, v.status
ORDER BY v.status ASC, COUNT(v.status) DESC