php - MYSQL选择语句

  显示原文与译文双语对照的内容

我有两张桌子


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

时间:

这应该适合你:


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

这里的是一个工作的sqlfiddle 。

你可以使用条件和和使用用户定义变量的排序系统作为


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 ;

简单条件聚合是这里所必需的。 同时将两个表合并在一起:


select t1.location, count(*) as Interview, 
 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 t1
 inner join table2 t2
 on t1.leadid = t2.leadid
 group by t1.location

count() 只计算non-null字段,当条件不满足时 case 语句结果是 null 。 在很多用例中应用的便捷技术。

这将只包括至少有一个访谈的位置。 如果要包括所有国家,请将 inner join 更改为 left join

演示这里

已经测试过了。请找到 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

小提琴


Select location,count(*) as Interview,
SUM(CASE WHEN (status='Hired')Then 1 Else 0 END) as Hired,
SUM(CASE WHEN(status='Failed') Then 1 Else 0 END) as Failed 
from loc inner join status on loc.leadid= status.leadid 
group by location;

其中第一个表格是组委会 containg leadid位置,第二个表是包含 状态 leadid状态


SELECT table1.location, COUNT(*) as Interview, 
COUNT(CASE WHEN table2.leadstatus = 'hired' THEN table2.leadstatus END) as Hired, 
COUNT(CASE WHEN table2.leadstatus = 'failed' THEN table2.leadstatus END) as Failed
FROM table1 
INNER JOIN table2 ON table1.leadid = table2.leadid
GROUP BY table1.location
ORDER BY Interview DESC;

fiddle http://sqlfiddle.com/#!9/269da/14

...