mysql - 合并两个单独的 mysql 查询

Question

需要合并(可能是 UNION ALL)这两个 mysql 查询..

select users.name as 'Agent', min(agent_activities.created_at) as 'Login Time' 
from agent_activities, users
where 
agent_activities.agent_id = users.id 
and date(agent_activities.created_at) = curdate() 
and agent_activities.activity='login' 
group by agent_activities.agent_id 
order by agent_activities.agent_id asc;

select users.name as 'Agent', max(agent_activities.created_at) as 'Logout Time' 
from agent_activities, users
where 
agent_activities.agent_id = users.id 
and date(agent_activities.created_at) = curdate() 
and agent_activities.activity='logout' 
group by agent_activities.agent_id 
order by agent_activities.agent_id asc;

现在结果是

Agent       | Login Time
test Admin  | 2018-09-27 10:26:54
Agent 1     | 2018-09-27 11:43:44
Hari        |  2018-09-27 11:10:41

Agent       | Logout Time
test Admin  | 2018-09-27 11:43:41
Agent 1     | 2018-09-27 11:45:04
ttt         | 2018-09-27 11:21:06
Hari VH     | 2018-09-27 15:18:04

想要的结果

Agent       | Login Time           | Logout Time
test Admin  | 2018-09-27 10:26:54  | 2018-09-27 11:43:41
Agent 1     | 2018-09-27 11:43:44  | 2018-09-27 11:45:04
Hari        |  2018-09-27 11:10:41 | 2018-09-27 15:18:04

请帮忙，......................................

提前致谢。

Answer 1

尝试条件聚合:

select users.name as 'Agent', min(case when agent_activities.activity='login' then agent_activities.created_at end) as 'Login Time' ,
max(case when agent_activities.activity='logout' then agent_activities.created_at end) as 'Logout Time'
from agent_activities inner join users
on agent_activities.agent_id = users.id 
and date(agent_activities.created_at) = curdate() 
group by users.name 
order by users.name asc