php - 如何使用MySQL获得最高学历？

我在尝试获得最高学历时遇到问题。例如，我有5张桌子。

员工

+-----------+----------+
| STAFF ID  | NAME     |
+-----------+----------+
|   001     | Ramesh   |
|   002     | Khilan   |
|   003     | Kaushik  |
|   004     | Chaitali |
|   005     | Hardik   |
|   006     | Komal    |
|   007     | Muffy    |
+-----------+----------+

文凭

+-----------+-------------------------+
| STAFF ID  |          DIPLOMA        |
+-----------+-------------------------+
|   001     | Diploma in IT           |
|   003     | Diploma in Multimedia   |
|   004     | Diploma in Multimedia   |
|   005     | Diploma in IT           |
+-----------+-------------------------+

学位

+-----------+-------------------------+
| STAFF ID  |          DEGREE         |
+-----------+-------------------------+
|   002     |  Degree in Science      |
|   003     |  Degree in Multimedia   |
+-----------+-------------------------+

主

+-----------+-------------------------+
| STAFF ID  |          MASTER         |
+-----------+-------------------------+
|   006     |  Master in Arts         |
|   007     |  Master in Business     |
+-----------+-------------------------+

如何通过MySQL拥有的高学历筛选人员？例如，我想搜索仅具有文凭的员工，并且不希望在搜索结果中同时显示具有文凭和学位的员工姓名Kaushik？

最佳答案

您可以使用COALESCE函数返回第一个非null值
然后按顺序传递您的硕士，学位，文凭，以便它返回第一个非空值，即最高的教育程度，如下所示

SELECT s.staff_id,
       s.name,
       COALESCE(m.master,d.degree,di.diploma) AS highest_education
FROM staff s
LEFT JOIN master m on s.staff_id = m.staff_id
LEFT JOIN degree d on s.staff_id = d.staff_id
LEFT JOIN diploma di on s.staff_id = di.staff_id

然后仅过滤文凭即可，您可以像这样添加HAVING highest_education LIKE "Diploma%"

SELECT s.staff_id,
       s.name,
       COALESCE(m.master,d.degree,di.diploma) AS highest_education
FROM staff s
LEFT JOIN master m on s.staff_id = m.staff_id
LEFT JOIN degree d on s.staff_id = d.staff_id
LEFT JOIN diploma di on s.staff_id = di.staff_id
HAVING highest_education LIKE "Diploma%"

http://sqlfiddle.com/#!9/e1e47/3