mysql - 如何从创建虚拟列的表中获取不同的值 'types' - MySQL

Question

我有这些 table 。

tb_employee:

ID_EMP           NAME_EMP
1               Employee 1
2               Employee 2
3               Employee 3
4               Employee 4

tb_requirements:

ID_REQ                  DESCRIPTION_REQ               TYPE_REQ
1                        Requirement 1                   1
2                        Requirement 2                   1
3                        Requirement 3                   1
4                        Requirement 4                   2
5                        Requirement 5                   2
6                        Requirement 6                   2
7                        Requirement 7                   2

tb_detail:

ID_DET                        ID_EMP                        ID_REQ
1                               1                             1
2                               1                             2
3                               1                             4
4                               2                             1
5                               2                             6
6                               3                             4
7                               3                             7

我需要创建一个SELECT QUERY来计算每个员工有多少需求以及哪种类型，如下所示:

ID_EMP               NAME_EMP          TYPE_REQ1(virtual column)     TYPE_REQ2 (virt. c.)
1                    Employee 1               2                         4
2                    Employee 2               1                         1
3                    Employee 3               0                         2
4                    Employee 4               0                         0

我真的不知道该怎么办。

Answer 1

试试这个

SELECT 
e.ID_EMP 
,e.NAME_EMP
,(CASE WHEN SUM(r.TYPE_REQ=1) IS NULL THEN 0 ELSE SUM(r.TYPE_REQ=1) END ) TYPE_REQ1
,(CASE WHEN SUM(r.TYPE_REQ=2) IS NULL THEN 0 ELSE SUM(r.TYPE_REQ=2) END ) TYPE_REQ2
FROM 
tb_employee e
LEFT JOIN tb_detail d ON (e.ID_EMP=d.ID_EMP)
LEFT JOIN  tb_requirements r ON (d.ID_REQ=r.ID_REQ)
GROUP BY e.ID_EMP