mysql - 计算查询结果

Question

Person table
---------------------
|email (pk)   | name|
---------------------
|a@hotmail.com| A   |
|b@hotmail.com| B   |
|c@hotmail.com| C   |

 Role table
---------------------------------
|Role               |Power     |
-------------------------------|
|Primary            |20        |
|Secondary          |10        |
|Supervisor         |30        |
--------------------------------
Assignment table
------------------------------------------------------------------
|Team Name| Term    | Role      |Email         |Join_date
------------------------------------------------------------------
|AA       |2013_1   |Supervisor |a@hotmail.com |2013-08-05
|BB       |2013_1   |Secondary  |a@hotmail.com |2013-08-05
|CC       |2013_1   |Supervisor |c@hotmail.com |2013-08-05
|DD       |2013_1   |Secondary  |a@hotmail.com |2013-08-05
|AA       |2013_1   |Secondary  |b@hotmail.com |2013-08-05

我的预期结果

|name | email        | num_of_time_pri | num_of_time_sec | num_of_time_sup|
---------------------------------------------------------------------------------------
|A    | a@hotmail.com|0                |2                | 1              |
|B    | b@hotmail.com|0                |1                | 0              |
|C    | c@hotmail.com|0                |0                | 1              |

给定一个术语，例如2013_1，我必须找到分配给至少一个角色的所有人员。并计算该人被分配到的每个角色的数量。

使用此查询:

select 
      distinct p.name,
      p.email 
 from assignment a,person p 
 where term ='2013_1' and 
       a.email = p.email;

假设它返回 3 行，如 person 表中所示。从那里，我想得到预期的结果表。我该如何继续？

Answer 1

使用聚合case语句。并且，尽可能避免使用隐式联接。

select 
      p.name,
      p.email,
      sum(case when role='Primary' then 1 else 0 end) as num_of_time_pri,
      sum(case when role='Secondary' then 1 else 0 end) as num_of_time_sec,
      sum(case when role='Supervisor' then 1 else 0 end) as num_of_time_sup
from assignment a
inner join person p on a.email = p.email
where term ='2013_1'
group by p.name,p.email;

或者

select 
      p.name,
      p.email,
      count(case when role='Primary' then 1 end) as num_of_time_pri,
      count(case when role='Secondary' then 1 end) as num_of_time_sec,
      count(case when role='Supervisor' then 1 end) as num_of_time_sup
from assignment a
inner join person p on a.email = p.email
where term ='2013_1'
group by p.name,p.email;