基于time列筛选最新id的SQL查询优化方案咨询

有一张包含category、id、time字段的表，原始数据如下：

+----------------+--------------+-----+
| category       | id           | time|
+----------------+--------------+-----+
| A              | abc          | 1   |
| A              | abc          | 1   |
| B              | abc          | 3   |
| C              | abc          | 4   |
| A              | xyz          | 4   |
| B              | xyz          | 5   |
| C              | xyz          | 7   |
| C              | xyz          | 7   |
+----------------+--------------+-----+

我们需要得到各category对应全局最新（time值最大）id的计数结果，期望输出：

+----------------+--------------+-----+
| category       | id           | cnt |
+----------------+--------------+-----+
| A              | xyz          | 1   |
| B              | xyz          | 1   |
| C              | xyz          | 2   |
+----------------+--------------+-----+

已经写出基础分组统计SQL：

select category, id, count(*) as cnt
from table
group by category, id

当前使用的子查询方案如下，但希望找到更优写法：

select category, id, count(*) as cnt
from table
where id=(select id from table order by time desc limit 1)
group by category, id

1. 先取全局最大time再关联统计 #

这个写法解决了原方案的逻辑漏洞（比如多个id对应同一最大time时，原方案只会取一个），而且性能更优——无需全表排序，只需一次聚合找到最大time：

WITH max_time AS (
    SELECT MAX(time) AS max_t FROM `table`
)
SELECT t.category, t.id, COUNT(*) AS cnt
FROM `table` t
JOIN max_time mt ON t.time = mt.max_t
GROUP BY t.category, t.id;

2. 窗口函数实现（适配每个category各自取最新id的场景） #

如果你的实际需求是每个category下单独取最新time对应的id计数（而非全局统一最新），可以用窗口函数标记每个分类下的最新记录：

WITH ranked_data AS (
    SELECT 
        category, 
        id,
        -- 同一category下按time倒序排名，最大time的记录排第1
        RANK() OVER(PARTITION BY category ORDER BY time DESC) AS rnk
    FROM `table`
)
SELECT category, id, COUNT(*) AS cnt
FROM ranked_data
WHERE rnk = 1
GROUP BY category, id;

提示：如果同一个分类下多个id对应相同的最大time，RANK()会保留所有符合条件的记录；如果只想保留其中一条，换成ROW_NUMBER()即可。

3. 传统关联子查询（兼容老版本数据库） #

如果你的数据库不支持CTE或窗口函数，用这个写法：

SELECT t.category, t.id, COUNT(*) AS cnt
FROM `table` t
JOIN (
    SELECT category, MAX(time) AS max_t
    FROM `table`
    GROUP BY category
) mt ON t.category = mt.category AND t.time = mt.max_t
GROUP BY t.category, t.id;

1. 逻辑有漏洞：如果存在多个id对应全局最大的time，order by time desc limit 1只会返回其中一个id，导致其他符合条件的记录被过滤，结果不准确。
2. 性能较差：子查询需要对全表进行排序，数据量越大，性能损耗越明显。

内容的提问来源于stack exchange，提问作者user0000