SQL多表查询方法：根据UnitNo关联获取username并统计超速记录

原SQL存在的问题 #

• 关联逻辑错误：users_table不存在UnitNo字段，原语句使用ut.UnitNo = av.UnitNo关联完全错误，正确关联逻辑应为unit_table.userid = users_table.userid
• 执行效率低下：多次嵌套子查询重复扫描unitlog_table，数据量大时性能会极差
• 统计逻辑错误：ttover原判断条件要求同时满足3个速度阈值，实际应为所有超速记录的总计数
• 缺少输出字段：原查询未返回userid字段，不符合预期输出要求

优化后查询语句（适配SQL Server语法） #

WITH speed_stats AS (
    SELECT 
        UnitNo,
        COUNT(CASE WHEN speed >= 41 THEN 1 END) AS overord,
        COUNT(CASE WHEN speed >= 71 THEN 1 END) AS overex,
        COUNT(CASE WHEN speed >= 91 THEN 1 END) AS overc,
        COUNT(CASE WHEN speed >=41 THEN 1 END) AS ttover
    FROM unitlog_table
    WHERE timestamp BETWEEN '2021-09-01 00:00:00.00' AND '2021-09-01 08:00:00.00'
    GROUP BY UnitNo
),
unit_user_mapping AS (
    SELECT 
        u.UnitNo, u.userid, ut.username,
        ROW_NUMBER() OVER(PARTITION BY u.userid ORDER BY u.UnitNo,ut.username) AS rn
    FROM unit_table u
    INNER JOIN users_table ut ON u.userid = ut.userid
    WHERE u.userid = '1122'
)
SELECT TOP 2
    mum.username,
    mum.userid,
    mum.UnitNo,
    ISNULL(ss.overord, 0) AS overord,
    ISNULL(ss.overex, 0) AS overex,
    ISNULL(ss.overc, 0) AS overc,
    ISNULL(ss.ttover, 0) AS ttover
FROM unit_user_mapping mum
LEFT JOIN speed_stats ss ON mum.UnitNo = ss.UnitNo
ORDER BY mum.UnitNo

逻辑说明 #

1. 先通过CTE speed_stats 一次性统计所有Unit在指定时段的各阈值超速次数，仅扫描一次日志表，性能远高于原嵌套子查询写法
2. 第二个CTE unit_user_mapping 处理同用户ID下多用户和多设备的对应关系，按Unit编号和用户名排序一一匹配，符合你给出的预期输出对应规则
3. 最后关联两个CTE得到最终结果，用ISNULL处理无超速记录时返回0的需求，加TOP 2限定输出和你给出的预期结果一致

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