消除SCCM查询报告中的重复项 [英] Eliminating duplicates in SCCM query report

问题描述

我使用以下代码获得重复项，虚线下方的第二部分来自下拉菜单的第二个数据集。我添加了DISTINCT，但仍然有重复。感谢任何帮助，谢谢。

I get duplicates with the following code, the second part below the dashed line is from a 2nd dataset for a pull down menu. I added the DISTINCT but still have duplicates. Appreciate any help, thanks.

SELECT DISTINCT
  v_R_System.Name0
  ,v_R_System.AD_Site_Name0
  ,v_R_System.User_Name0
  ,v_R_System.Last_Logon_Timestamp0
  ,v_R_System.Operating_System_Name_and0
  ,v_R_System.CPUType0
  ,v_RA_System_IPAddresses.IP_Addresses0
  ,v_RA_System_MACAddresses.MAC_Addresses0
  ,v_GS_COMPUTER_SYSTEM.NumberOfProcessors0
  ,v_GS_COMPUTER_SYSTEM.SystemType0
  ,v_GS_COMPUTER_SYSTEM.Status0
  ,v_GS_COMPUTER_SYSTEM.Manufacturer0
  ,v_GS_COMPUTER_SYSTEM.Model0
  ,v_GS_SYSTEM_ENCLOSURE.SerialNumber0
  ,v_GS_X86_PC_MEMORY.TotalPhysicalMemory0
  ,v_GS_LastSoftwareScan.LastScanDate
  ,v_GS_WORKSTATION_STATUS.LastHWScan
  ,v_GS_PROCESSOR.NumberOfCores0
  ,v_GS_PROCESSOR.NumberOfLogicalProcessors0
FROM
  v_R_System
  INNER JOIN v_RA_System_IPAddresses
    ON v_R_System.ResourceID = v_RA_System_IPAddresses.ResourceID
  INNER JOIN v_RA_System_MACAddresses
    ON v_R_System.ResourceID = v_RA_System_MACAddresses.ResourceID
  INNER JOIN v_GS_COMPUTER_SYSTEM
    ON v_R_System.ResourceID = v_GS_COMPUTER_SYSTEM.ResourceID
  INNER JOIN v_GS_SYSTEM_ENCLOSURE
    ON v_R_System.ResourceID = v_GS_SYSTEM_ENCLOSURE.ResourceID
  INNER JOIN v_GS_X86_PC_MEMORY
    ON v_R_System.ResourceID = v_GS_X86_PC_MEMORY.ResourceID
  INNER JOIN v_GS_LastSoftwareScan
    ON v_R_System.ResourceID = v_GS_LastSoftwareScan.ResourceID
  INNER JOIN v_GS_WORKSTATION_STATUS
    ON v_R_System.ResourceID = v_GS_WORKSTATION_STATUS.ResourceID
  INNER JOIN v_GS_PROCESSOR
    ON v_R_System.ResourceID = v_GS_PROCESSOR.ResourceID

where

v_R_System.AD_Site_Name0 = @SiteCode
----------------------------------------------------------------------------------------------------------------------------

SELECT distinct
  v_R_System.AD_Site_Name0 as SIteCode
FROM
  v_R_System order by SiteCode

推荐答案

我调试此类型的方式问题是使用select *。注释掉字段和内部联接，并使用where子句在基表上执行select *。



记录记录数



你可能想为重复的记录添加一个过滤器以减少查询时间。



开始取消注释内连接直到得到愚蠢数字，现在您知道问题连接，开始检查重复项的连接字段数据。

The way I debug this type of problem is to use select *. Comment out the fields and inner joins and do a select * on the base table with the where clause.

note the record count

You might want to add a filter for a duplicated record to reduce the query time.

start uncommenting the inner joins until to get the silly numbers, now you know the problem join, start checking the join field data for duplicates.

这篇关于消除SCCM查询报告中的重复项的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！