合并两个单独的mysql查询

Question

需要合并(也许合并所有)这两个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

Answer 2

您可以使用条件聚合来做您想做的事情：

select u.name as Agent,
       max(case when aa.activity = 'login' then aa.created_at end) as Login_Time,
       max(case when aa.activity = 'logout' then aa.created_at end) as Logout_Time 
from agent_activities aa join
     users u
     on aa.agent_id = u.id 
where aa.created_at >= curdate() and
      aa.created_at < curdate() + interval 1 day
group by u.name 
order by u.name  asc;

备注：

• 不要在FROM子句中使用逗号。始终使用正确、显式的JOIN语法。
• 表别名使查询更易于编写和读取。表名使用缩写。
• 在列上使用函数通常会阻碍索引的使用，所以我用更简单的日期比较代替了date(created_at)。
• 您的GROUP BY列应该与SELECT中的未聚合列匹配。

Answer 3

使用条件min/max，仅当活动为created_at时才考虑min login，反之亦然。这是因为默认的ELSE值(没有显式显示)是NULL，MIN和MAX都忽略了这个值。

SELECT
    u.name as 'Agent',
    MIN(CASE WHEN a.activity = 'login'  THEN a.created_at END) AS 'Login Time' 
    MAX(CASE WHEN a.activity = 'logout' THEN a.created_at END) AS 'Logout Time' 
FROM agent_activities a
INNER JOIN users u
    ON a.agent_id = u.id 
WHERE
    DATE(a.created_at) = CURDATE()
GROUP BY
    a.agent_id, u.name
ORDER BY
    a.agent_id, u.name;

一些注意事项：

我使用ON子句将您的隐式联接转换为显式的内部联接。此外，我在查询中引入了表别名，这使得它更容易阅读。