SQL选择总计，然后分裂成成功和失败。

Question

我有两张桌子

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

这里是一个工作的小木琴。

编辑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

这里是更新的sqlfiddle。

Answer 2

可以使用用户定义的变量使用条件和排序系统，如

select
@rn:=@rn+1 as rank, 
location,
interview,
hired,
failed
from(
    select 
        t1.location, 
        count(*) as interview,
        sum(t2.leadstatus='Hired') as hired,
        sum(t2.leadstatus='Failed') as failed
        from table1 t1
        join table2 t2 on t1.leadid = t2.leadid 
        group by t1.location
        order by interview desc
)x,(select @rn:=0)y 
order by rank ;

Answer 3

测试过这个。请找到SQL小提琴链接

SELECT 

    t1.leadid, 
    t1.Location, 
    count( t2.leadstatus ) Location, 
    count(case when t2.leadstatus = 'Hired' then t2.leadstatus end) as Hired, 
    count(case when t2.leadstatus = 'Failed' then t2.leadstatus end) as Failed  

FROM table1 AS t1 
    INNER JOIN table2 AS t2  
    ON t1.leadid = t2.leadid 

GROUP BY t1.Location,t2.leadstatus 
Order BY Hired DESC

小提琴