如何编写SQL查询获取路段观测次数最多的前10辆车？

嘿，你的思路已经完全找对方向啦！只需要把排序和取前10的逻辑补充进去就行，不同数据库的语法会有一点点小差异，我给你整理了几种常见场景的完整SQL语句：

基础实现（取前10条，不处理并列情况） #

MySQL / MariaDB #

SELECT nplate, COUNT(*) AS pass_count
FROM observations
GROUP BY nplate
ORDER BY pass_count DESC
LIMIT 10;

这里把COUNT('x')换成了更常用的COUNT(*)，效果完全一致；给统计结果起了别名pass_count，排序的时候用这个别名会更清晰。

PostgreSQL #

PostgreSQL支持两种写法，选你习惯的就行：

-- 写法1：用LIMIT
SELECT nplate, COUNT(*) AS pass_count
FROM observations
GROUP BY nplate
ORDER BY pass_count DESC
LIMIT 10;

-- 写法2：用FETCH FIRST
SELECT nplate, COUNT(*) AS pass_count
FROM observations
GROUP BY nplate
ORDER BY pass_count DESC
FETCH FIRST 10 ROWS ONLY;

SQL Server #

用TOP 10来限制结果数量：

SELECT TOP 10 nplate, COUNT(*) AS pass_count
FROM observations
GROUP BY nplate
ORDER BY pass_count DESC;

Oracle #

如果是11g及以上版本，推荐用FETCH FIRST；旧版本可以用子查询加ROWNUM：

-- 11g+版本
SELECT nplate, COUNT(*) AS pass_count
FROM observations
GROUP BY nplate
ORDER BY pass_count DESC
FETCH FIRST 10 ROWS ONLY;

-- 旧版本兼容写法
SELECT *
FROM (
    SELECT nplate, COUNT(*) AS pass_count
    FROM observations
    GROUP BY nplate
    ORDER BY pass_count DESC
) ranked_obs
WHERE ROWNUM <= 10;

进阶实现（包含并列第10的所有车辆） #

如果需要把所有和第10名通行次数相同的车辆都包含进来（比如第10名有3辆车，它们的次数一样，都要显示），可以用窗口函数RANK()，这种写法适用于支持窗口函数的数据库（MySQL 8.0+、PostgreSQL、SQL Server、Oracle等）：

SELECT nplate, pass_count
FROM (
    SELECT 
        nplate, 
        COUNT(*) AS pass_count,
        RANK() OVER (ORDER BY COUNT(*) DESC) AS rank_num
    FROM observations
    GROUP BY nplate
) ranked_obs
WHERE rank_num <= 10;

这里RANK()会给并列的车辆分配相同的名次，确保所有次数最多的前10个“档位”的车辆都被选中。

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