MYSQL self join get row based on MAX 并具有

Question

我需要计算 2 个表中的行数，如果 events.event_id IN (30, 31, 32, 33)，则只获取具有最高 g_event_id 的行。仅考虑 events.event_id 为 30-33 的行。

SQL fiddle :Fiddle

我的表:

 CREATE TABLE event_parties
 (`g_event_id` int, `agent_id` int)
;

INSERT INTO event_parties
(`g_event_id`, `agent_id`)
VALUES
(2917, '2'),
(2918, '2'),
(2919, '2'),
(3067, '3'),
(3078, '3'),
(3079, '1'),
(3082, '1'),
(3917, '2'),
(3918, '2'),
(3919, '2'),
(4067, '3'),
(4078, '3'),
(4079, '1'),
(5067, '3'),
(5078, '3'),
(5079, '1'),
(6067, '3'),
(6078, '3'),
(6079, '1'),
(7067, '3'),
(7078, '3'),
(7079, '1'),
(8067, '3'),
(8078, '3'),
(8079, '1')

;
CREATE TABLE events
(`g_event_id` int, `event_id` int, `event_time` datetime)
;

INSERT INTO events
(`g_event_id`, `event_id`, `event_time`)
 VALUES
(2917, '29', '2016-10-19 15:24:25'),
(2918, '31', '2016-10-19 15:24:28'),
(2919, '21', '2016-10-19 15:29:46'),
(3067, '29', '2016-10-20 15:33:46'),
(3078, '23', '2016-10-21 15:29:46'),
(3079, '29', '2016-10-20 15:34:46'),
(3082, '30', '2016-10-21 15:42:46'),
(3917, '29', '2016-10-19 15:24:25'),
(3918, '31', '2016-10-19 15:24:28'),
(3919, '21', '2016-10-19 15:29:46'),
(4067, '29', '2016-10-20 15:33:46'),
(4078, '23', '2016-10-21 15:29:46'),
(4079, '29', '2016-10-20 15:34:46'),
(5067, '29', '2016-10-20 15:33:46'),
(5078, '23', '2016-10-21 15:29:46'),
(5079, '29', '2016-10-20 15:34:46'),
(6067, '29', '2016-10-20 15:33:46'),
(6078, '23', '2016-10-21 15:29:46'),
(6079, '29', '2016-10-20 15:34:46'),
(7067, '29', '2016-10-20 15:33:46'),
(7078, '23', '2016-10-21 15:29:46'),
(7079, '29', '2016-10-20 15:34:46'),
(8067, '29', '2016-10-20 15:33:46'),
(8078, '23', '2016-10-21 15:29:46'),
(8079, '29', '2016-10-20 15:34:46')

;

选择应该给我调用中心座席的状态，我想计算每个状态 (event_id) 中有多少座席 (agent_id)。由于表“事件”只是代理的事件，我只需要计算每个 agent_id 的最新(具有最高值)g_event_id 和棘手的部分是我只想计算 event_id IN (30, 31, 32, 32, 33).所以基本上，为每个 agent_id 选择具有最高 g_event_id(和 event_id IN (30, 31, 32, 33))的行。我需要用 g_event_id 作为 ID 在这两个表之间进行 JOIN。 g_event_id 字段是关键，只出现一次。我需要表 events 中的所有字段，并且我需要具有最高 g_event_id 或最高 event_time 的行。像这样:

event_id   N_events
--------   ----------
    31         1
    30         1

到目前为止我有这个选择:

SELECT event_id,
COUNT(events.event_id) AS N_events
FROM event_parties 
INNER JOIN events USING (g_event_id)
LEFT JOIN event_parties AS later_event
ON (later_event.agent_id = event_parties.agent_id 
  AND later_event.g_event_id > event_parties.g_event_id)
WHERE later_event.g_event_id IS NULL AND event_parties.agent_id != 0 AND     events.`event_id` IN (30,31,32,33)
GROUP BY events.event_id

上面这个选择的问题是它只会给我具有最高 g_event_id 的行，我只想选择具有 events.event_id = (30, 31 , 32, 33) 然后计算具有最高 g_event_id

的行

尝试在 GROUP 之后使用 having(HAVING events.event_id IN (30,31,32,33))但没有成功。

Answer 1

这个查询应该给你你的结果:

select e.event_id, count(stats.agent_id) as N_count
from (
  select max(p.g_event_id) as g_event_id, p.agent_id
  from events e 
  join event_parties p
  on e.g_event_id = p.g_event_id
  where e.event_id in (30,31,32,33)
  group by p.agent_id
) as stats
join events e
on e.g_event_id = stats.g_event_id
group by e.event_id;

内部查询(stats)首先检索每个代理的最新相关状态:它将为每个 agent_id 获取最大的 g_event_id给定范围内的 event_id(因此每个代理最多一行)。

然后它将与 events 表结合以检索此 g_event_id 的实际 event_id；然后计算每个 event_id 的代理数。

如评论中所述，这假设 g_event_id 是两个表的主键(但尤其是 events)，并且最新状态由最大的 g_event_id，而不是 event_time。