SQL select列按查询排序

Question

我用谷歌搜索了我能想到的所有东西，但是我找不到答案，我有一个简单的数据库

技术

+------------------+------------+------+-----+
| Field            | Type       | Null | Key |
+------------------+------------+------+-----+
| employe id       | number(4)  | NO   | PRI |
| name             | char(11)   | NO   |     |
| salary           | int(11)    | NO   |     |
+------------------+------------+------+-----+

维护

+------------------+------------+------+---------+
| Field            | Type       | Null | Key     |
+------------------+------------+------+---------+
| employe id       | number(4)  | NO   | foreign |
| IP               | char(11)   | NO   | foreign |
| maintenance_date | int(11)    | NO   |         |
+------------------+------------+------+---------+

个人电脑

+------------------+------------+------+---------+
| Field            | Type       | Null | Key     |
+------------------+------------+------+---------+
| value            | number(4)  | NO   | foreign |
| IP               | char(11)   | NO   | PRI     |
| price            | int(11)    | NO   |         |
+------------------+------------+------+---------+

我需要的是显示每个technicien的姓名，id和工资谁做了一个maintenance的总数量的维护效果排序。

Answer 1

SELECT technicien.name, technicien.employeId, technicien.salary
FROM technicien
INNER JOIN  maintenance
ON maintenance.employeId = technicien.employeId
INNER JOIN pc
ON maintenance.IP = pc.IP
ORDER BY COUNT(maintenance.maintenance_date)

Answer 2

您需要的是相对于表_technicien的列执行GROUP_ing，并根据_maintenance任务的计数执行ORDER_ing：

select t.*, count(0) cnt_maintenance
  from technicien t
  inner join maintenance m on ( t.employee_id = m.employee_id )
  inner join pc p on ( p.value = m.IP )
 group by t.employee_id, t.name, t.salary
 order by count(0);