SQL Server中如何统计各车辆最新状态的数量分布？

当然有更高效的纯SQL方案啦！完全不用折腾临时表和逐个遍历查询，用窗口函数就能一步到位，性能和简洁度都甩原来的方案几条街～

核心思路 #

我们需要先定位到每辆车对应最新Year的状态记录，再对这些最新状态做统计聚合，直接得到你想要的结果。

具体实现代码 #

首先用ROW_NUMBER()窗口函数给每辆车的记录按年份倒序排名，排名第一的就是该车辆的最新状态：

SELECT 
    name,
    status,
    Year,
    -- 按车辆分组，每组内按年份降序排，第一条就是最新记录
    ROW_NUMBER() OVER (PARTITION BY name ORDER BY Year DESC) AS rn
FROM `order`; -- 注意order是SQL关键字，需要用反引号/方括号包裹

接着基于这个子查询，筛选出排名第一的记录，再用条件聚合统计各状态的数量：

SELECT
    SUM(CASE WHEN status = 'enter' THEN 1 ELSE 0 END) AS enter,
    SUM(CASE WHEN status = 'checking' THEN 1 ELSE 0 END) AS checking,
    SUM(CASE WHEN status = 'exit' THEN 1 ELSE 0 END) AS exit
FROM (
    SELECT 
        name,
        status,
        ROW_NUMBER() OVER (PARTITION BY name ORDER BY Year DESC) AS rn
    FROM `order`
) AS latest_status
WHERE rn = 1;

方案优势 #

• 高效简洁：单条SQL完成所有逻辑，避免了创建临时表、循环遍历的额外开销，数据量越大优势越明显
• 性能优异：窗口函数是数据库原生优化过的操作，比多次单条查询的IO成本低很多
• 易维护：逻辑清晰直观，后续修改状态类型或统计规则时，只需要调整聚合条件即可

结果验证 #

根据你提供的表数据，各车辆最新状态如下：

• f1 car: 2018年的checking
• f2 car: 2020年的exit
• f3 car: 2020年的exit
• f4 car: 2020年的checking
• f5 car: 2019年的enter

统计后正好得到你期望的结果：

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