mysql - SQL select total 并分为成功和失败

Question

我有两张 table

Table 1                Table 2                   
|leadid|Location|      |leadid|leadstatus|       
|---------------|      |-----------------|
|1     |Japan   |      |1     | Hired    |
|2     |China   |      |2     | Failed   |
|3     |Korea   |      |3     | Hired    |
|4     |Japan   |      |4     | Hired    |
|5     |Japan   |      |5     | Hired    |

我的目标是统计每个国家/地区的面试次数，以及每个国家/地区的招聘人数和失败人数。结果表应该是这样的

|Location|Interview|Hired|Failed|
|-------------------------------|
|Japan   | 3       |3    |0     |
|Korea   | 1       |1    |0     |
|China   | 1       |0    |1     |

我已经统计了每个国家的采访次数。我的问题是我无法计算每个国家/地区的招聘人数和失败人数。这是我目前的 MySQL 代码:

SELECT Location, count(*) as Interview 
FROM table1 
GROUP BY Location 
ORDER BY Interview DESC

Answer 1

这应该适合你:

SELECT Location, COUNT(*) as Interview,
SUM(CASE WHEN leadstatus = 'Hired' THEN 1 ELSE 0 END) as Hired,
SUM(CASE WHEN leadstatus = 'Failed' THEN 1 ELSE 0 END) as Failed
FROM table1 
LEFT JOIN table2 ON table1.leadid = table2.leadid
            GROUP BY Location 
            ORDER BY Interview DESC

Here是一个工作的 sqlfiddle。

EDIT 2019:这可以在不使用 case 语句的情况下进行简化，因为条件语句本身返回 1 或 0，因此您只需 SUM() 即可:

SELECT Location, COUNT(*) as Interview,
SUM(leadstatus = 'Hired') as Hired,
SUM(leadstatus = 'Failed') as Failed
FROM table1 
LEFT JOIN table2 ON table1.leadid = table2.leadid
            GROUP BY Location 
            ORDER BY Interview DESC

Here是更新的sqlfiddle。