SQL如何按用户分组查询各设备更新时间及最新设备版本信息

修正后的查询语句 #

你原代码中存在笔误：你提到的设备表名为DEVICE，但代码中引用的是SOFTWARE，下面的代码已修正该问题，同时实现你需要的所有字段需求：

WITH device_ranked AS (
    SELECT 
        person_id,
        device,
        version,
        udt,
        -- 对每个用户的设备按更新时间倒序排序，最新的设备行号为1
        ROW_NUMBER() OVER(PARTITION BY person_id ORDER BY udt DESC) AS rn
    FROM DEVICE
)
SELECT 
    p.name,
    MAX(CASE WHEN dr.device = 'mobile' THEN dr.udt END) AS mobile_udt,
    MAX(CASE WHEN dr.device = 'computer' THEN dr.udt END) AS computer_udt,
    MAX(CASE WHEN dr.device = 'laptop' THEN dr.udt END) AS laptop_udt,
    MAX(CASE WHEN dr.rn = 1 THEN dr.device END) AS Device_newest,
    MAX(CASE WHEN dr.rn = 1 THEN dr.version END) AS Latest_version
FROM device_ranked dr
LEFT JOIN PERSON p ON dr.person_id = p.person_id
GROUP BY p.person_id, p.name

逻辑说明 #

• 通过CTE先给每个用户的所有设备按更新时间udt倒序排名，每个用户最新的设备行号固定为1
• 聚合阶段除了保留你原有三类设备最新更新时间的计算逻辑外，额外取行号为1的设备名称和版本号即可
• 分组时加入person_id是为了避免重名用户的数据被错误合并，如果你能确认name字段全局唯一，也可以保留原有仅按name分组的逻辑

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