时间戳

首页 > 解决方案 > 在有重复时仅选择最新记录的 SQL（SCCM 数据库）中的查询

sql - 在有重复时仅选择最新记录的 SQL（SCCM 数据库）中的查询

问题描述

LastLogin如果BIOSSerialNumber重复，我需要修改以下查询以仅选择最新的记录。我已经尝试过类似问题（row_number（），子查询，...）中提出的解决方案，但我无法使其工作。

这是对 SCCM 数据库的 SQL 查询：

SELECT 
v_GS_COMPUTER_SYSTEM.Name0 as name,
v_GS_COMPUTER_SYSTEM_PRODUCT.Version0 as model,
v_GS_COMPUTER_SYSTEM.ResourceID,
v_GS_SYSTEM.SystemRole0 as SystemRole,
v_GS_OPERATING_SYSTEM.Caption0 as caption,
v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 as chassistype,
v_GS_WORKSTATION_STATUS.LastHWScan, 
v_GS_PC_BIOS.SerialNumber0 as BIOSSerialNumber,
v_GS_COMPUTER_SYSTEM_PRODUCT.UUID0 as UUIDSerialNumber,
v_GS_SYSTEM_ENCLOSURE.SerialNumber0 as ChassisSerialNumber,
v_GS_BASEBOARD.SerialNumber0 as BaseboardSerialNumber,
v_R_SYSTEM.Last_Logon_Timestamp0 as LastLogin

FROM v_GS_COMPUTER_SYSTEM
LEFT JOIN v_GS_WORKSTATION_STATUS ON v_GS_COMPUTER_SYSTEM.ResourceID = v_GS_WORKSTATION_STATUS.ResourceID 
LEFT JOIN v_GS_SYSTEM ON v_GS_COMPUTER_SYSTEM.ResourceID = v_GS_SYSTEM.ResourceID 
LEFT JOIN v_GS_PC_BIOS ON v_GS_COMPUTER_SYSTEM.ResourceID = v_GS_PC_BIOS.ResourceID
LEFT JOIN v_GS_OPERATING_SYSTEM ON v_GS_COMPUTER_SYSTEM.ResourceID = v_GS_OPERATING_SYSTEM.ResourceID
LEFT JOIN v_GS_COMPUTER_SYSTEM_PRODUCT ON v_GS_COMPUTER_SYSTEM.ResourceID = v_GS_COMPUTER_SYSTEM_PRODUCT.ResourceID
LEFT JOIN v_GS_SYSTEM_ENCLOSURE ON v_GS_COMPUTER_SYSTEM.ResourceID = v_GS_SYSTEM_ENCLOSURE.ResourceID
LEFT JOIN v_GS_BASEBOARD ON v_GS_COMPUTER_SYSTEM.ResourceID = v_GS_BASEBOARD.ResourceID
LEFT JOIN v_R_SYSTEM ON v_GS_COMPUTER_SYSTEM.ResourceID = v_R_SYSTEM.ResourceID

如果结果是：

name    model   ResourceID  …   BIOSSerialNumber    …   LastLogin
ABC-123 HP XYZ  567845678   …   SerialNumber1       …   2020-10-26 21:14:28.000
DEF-456 HP XYZ  984567432   …   SerialNumber1       …   2020-10-24 07:32:05.000
EFG-789 HP XYZ  127687643   …   SerialNumber2       …   2020-10-21 08:35:05.000

我只需要返回第 1 行和第 3 行，因为第 2 行的 BIOSSerialNumber 与 1 相同，但 LastLogin 较旧：

name    model   ResourceID  …   BIOSSerialNumber    …   LastLogin
ABC-123 HP XYZ  567845678   …   SerialNumber1       …   2020-10-26 21:14:28.000
EFG-789 HP XYZ  127687643   …   SerialNumber2       …   2020-10-21 08:35:05.000

有什么办法可以实现吗？提前谢谢了。

标签： sqlsccm

解决方案

使用 row_number 函数，如果您按 BIOSSerialNumber 分区并按 LastLogin 排序，如下所示：

;with cte_SCCM as
(
    SELECT 
    v_GS_COMPUTER_SYSTEM.Name0 as name,
    v_GS_COMPUTER_SYSTEM_PRODUCT.Version0 as model,
    v_GS_COMPUTER_SYSTEM.ResourceID,
    v_GS_SYSTEM.SystemRole0 as SystemRole,
    v_GS_OPERATING_SYSTEM.Caption0 as caption,
    v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 as chassistype,
    v_GS_WORKSTATION_STATUS.LastHWScan, 
    v_GS_PC_BIOS.SerialNumber0 as BIOSSerialNumber,
    v_GS_COMPUTER_SYSTEM_PRODUCT.UUID0 as UUIDSerialNumber,
    v_GS_SYSTEM_ENCLOSURE.SerialNumber0 as ChassisSerialNumber,
    v_GS_BASEBOARD.SerialNumber0 as BaseboardSerialNumber,
    v_R_SYSTEM.Last_Logon_Timestamp0 as LastLogin,
    ROW_NUMBER() OVER (PARTITION BY v_GS_PC_BIOS.SerialNumber0 ORDER BY v_R_SYSTEM.Last_Logon_Timestamp0 DESC) as RN

    FROM v_GS_COMPUTER_SYSTEM
    LEFT JOIN v_GS_WORKSTATION_STATUS ON v_GS_COMPUTER_SYSTEM.ResourceID = v_GS_WORKSTATION_STATUS.ResourceID 
    LEFT JOIN v_GS_SYSTEM ON v_GS_COMPUTER_SYSTEM.ResourceID = v_GS_SYSTEM.ResourceID 
    LEFT JOIN v_GS_PC_BIOS ON v_GS_COMPUTER_SYSTEM.ResourceID = v_GS_PC_BIOS.ResourceID
    LEFT JOIN v_GS_OPERATING_SYSTEM ON v_GS_COMPUTER_SYSTEM.ResourceID = v_GS_OPERATING_SYSTEM.ResourceID
    LEFT JOIN v_GS_COMPUTER_SYSTEM_PRODUCT ON v_GS_COMPUTER_SYSTEM.ResourceID = v_GS_COMPUTER_SYSTEM_PRODUCT.ResourceID
    LEFT JOIN v_GS_SYSTEM_ENCLOSURE ON v_GS_COMPUTER_SYSTEM.ResourceID = v_GS_SYSTEM_ENCLOSURE.ResourceID
    LEFT JOIN v_GS_BASEBOARD ON v_GS_COMPUTER_SYSTEM.ResourceID = v_GS_BASEBOARD.ResourceID
    LEFT JOIN v_R_SYSTEM ON v_GS_COMPUTER_SYSTEM.ResourceID = v_R_SYSTEM.ResourceID
)

SELECT *
FROM cte_SCCM 
WHERE RN = 1

推荐阅读