MySQL特殊分组查询：统计各网站最新状态（safe/normal/broken）的数量

嘿，我来帮你解决这个统计需求！要基于每个网站的最新状态记录统计指定状态的数量，我们可以分两步走：先揪出每个网站的最新状态记录，再对这些记录做分组统计。下面给你两种实用的SQL写法，适配不同的数据库场景：

方法一：用窗口函数（推荐，简洁高效） #

窗口函数是处理这类“取每个分组最新记录”需求的最佳方案，代码可读性也很强：

WITH latest_status AS (
    SELECT 
        website_id, -- 这里替换成两张表实际的关联字段，比如域名domain或者网站唯一ID
        status,
        -- 按网站分组，每条记录按时间倒序排，最新的那条rn=1
        ROW_NUMBER() OVER (PARTITION BY website_id ORDER BY record_time DESC) AS rn
    FROM defacement_monitor
    -- 关联master_websites表，确保只统计系统内存在的网站（不需要的话可以删掉这行JOIN）
    JOIN master_websites mw ON mw.website_id = defacement_monitor.website_id
)
SELECT 
    status,
    COUNT(*) AS 状态数量
FROM latest_status
WHERE rn = 1 -- 只保留每个网站的最新记录
    AND status IN ('safe', 'normal', 'broken') -- 只统计你需要的这三个状态
GROUP BY status
ORDER BY status;

方法二：用子查询（适配不支持窗口函数的旧版数据库） #

如果你的数据库版本比较旧（比如MySQL 5.7及以前），不支持窗口函数，可以用这种子查询的方式：

SELECT 
    dm.status,
    COUNT(*) AS 状态数量
FROM defacement_monitor dm
-- 先找出每个网站的最新记录时间
JOIN (
    SELECT website_id, MAX(record_time) AS latest_time
    FROM defacement_monitor
    JOIN master_websites mw ON mw.website_id = defacement_monitor.website_id
    GROUP BY website_id
) latest_dm ON dm.website_id = latest_dm.website_id AND dm.record_time = latest_dm.latest_time
WHERE dm.status IN ('safe', 'normal', 'broken')
GROUP BY dm.status
ORDER BY dm.status;

几个要注意的点 #

• 记得把website_id替换成两张表实际用来关联的字段（比如domain_name，只要是能唯一标识网站的字段就行）。
• 把record_time替换成defacement_monitor表中记录状态生成时间的字段（比如created_at或者update_time）。
• 如果defacement_monitor里的所有网站都已经在master_websites里了，那可以去掉JOIN master_websites的部分，直接统计就行。

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