mysql - Mysql Group By 使用 Count 获取最新记录

Question

这是我的客户表。

我想按 emp_id 和计数进行分组。但是 Group By 获得了“第一”记录，而不是“最新”记录。

我尝试过各种查询，比如这样

SELECT id, emp_id, COUNT( * ) AS count, created_at
FROM  customer c
WHERE  created_at = (
SELECT MAX(  created_at ) 
FROM  customer c2
WHERE c2.emp_id = c.emp_id
)
GROUP BY  emp_id 
ORDER BY  created_at DESC 
LIMIT 0 , 30

但无法得到计数。请帮忙。

编辑：这个答案无助于获得计数

标签： mysqlsql

Answer 1

尝试加入子查询：

SELECT c1.id, c1.emp_id, c1.created_at, c2.cnt
FROM customer c1
INNER JOIN
(
    SELECT emp_id, MAX(created_at) AS max_created_at, COUNT(*) AS cnt
    FROM customer
    GROUP BY emp_id
) c2
    ON c1.emp_id = c2.emp_id AND c1.created_at = c2.max_created_at;