这是另一种无需相关子查询或窗口函数即可工作的方法：
对于每一行w1，尝试找到另一行w2具有相同的 call_id 和 0 表示呼叫已完成。使用 LEFT OUTER JOIN，我们可以测试没有的情况w2给定 call_id 存在行。
然后对假设的行进行另一个连接w3具有相同的 call_id 和较小的 ast_num_curr 值。再次，使用外连接，我们可以检查不存在这样的行。这意味着w1必须具有该 call_id 的 ast num 的最小值。
SELECT w1.call_id, w1.queue_num_curr
FROM waiter_log AS w1
LEFT OUTER JOIN waiter_log AS w2
ON w1.call_id = w2.call_id AND w2.proceed_wait = 0
LEFT OUTER JOIN waiter_log AS w3
ON w1.call_id = w3.call_id AND w1.ast_num_curr > w3.ast_num_curr
WHERE w2.call_id IS NULL AND w3.call_id IS NULL;
Output:
+---------+----------------+
| call_id | queue_num_curr |
+---------+----------------+
| 49c43ad | 9004 |
| asdf231 | 9010 |
| rete125 | 9010 |
+---------+----------------+
要获取每个queue_num_curr的计数，请将上面的查询包装在派生表子查询中，并在外部查询中进行计数：
SELECT queue_num_curr, COUNT(*) AS count
FROM (
SELECT w1.call_id, w1.queue_num_curr
FROM waiter_log AS w1
LEFT OUTER JOIN waiter_log AS w2
ON w1.call_id = w2.call_id AND w2.proceed_wait = 0
LEFT OUTER JOIN waiter_log AS w3
ON w1.call_id = w3.call_id AND w1.ast_num_curr > w3.ast_num_curr
WHERE w2.call_id IS NULL AND w3.call_id IS NULL
) AS t
GROUP BY queue_num_curr;
Output:
+----------------+-------+
| queue_num_curr | count |
+----------------+-------+
| 9004 | 1 |
| 9010 | 2 |
+----------------+-------+
