SQL 查询语句引用

项目
12/19/2014

应用到: System Center Configuration Manager 2007, System Center Configuration Manager 2007 R2, System Center Configuration Manager 2007 R3, System Center Configuration Manager 2007 SP1, System Center Configuration Manager 2007 SP2

在创建 Configuration Manager 2007 报表时可以使用很多有用的 Microsoft SQL Server 语句，本节对此进行了简要介绍。要完成本讨论，您应当具备基本的 SQL 查询语句知识，并能够编写下列类似查询：

SELECT Name, Comment, CollectionID

FROM v_Collection

WHERE Name LIKE 'All Windows%'

ORDER BY Name

有关如何编写基本查询的信息，请在 Microsoft SQL Server 2005 联机丛书中搜索“查询基础”。

聚合函数

聚合函数（如 SUM、AVG、COUNT、COUNT(*)、MAX 和 MIN）在查询结果集中生成汇总值。一个聚合函数（COUNT(*) 除外）处理单个列中选择的所有值以生成一个结果值。聚合函数可应用到视图中的所有行、由 WHERE 子句指定的视图子集或视图中的一组或多组行。在应用聚合函数时，从每一组行生成一个值。

重要

注意，聚合结果中不包括 NULL 值。例如，如果您有 100 个记录，其中 8 个记录对于您要计算的属性具有 NULL 列值，则计数将只返回 92 个结果。

下列查询显示了使用 COUNT(*) 聚合函数的示例（来自“统计每个站点的客户端”预定义报表）和示例结果集。

SELECT v_Site.SiteCode, v_Site.SiteName, v_Site.ReportingSiteCode,

Count(SMS_Installed_Sites0) AS 'Count'

FROM v_Site, v_RA_System_SMSInstalledSites InsSite

WHERE v_Site.SiteCode = InsSite.SMS_Installed_Sites0

GROUP BY SiteCode, SiteName, ReportingSiteCode

ORDER BY SiteCode

站点代码	站点名称	报表站点代码	计数
ABC	ABC 站点		928
123	123 站点	ABC	1010

日期和时间函数

多个内置报表使用日期和时间函数。最常用的函数是 GETDATE、DATEADD、DATEDIFF 和 DATEPART。

GETDATE ()

GETDATE 函数生成当前日期和时间，以 SQL Server datetime 值的内部格式表示。GETDATE 允许使用 NULL 参数 ()。

下列示例返回当前的系统日期和时间：

SELECT GETDATE()

（无列名）
2005-05-29 10:10:03.001

DATEADD (datepart, number, date)

DATEADD 函数根据向指定日期添加时间间隔来返回新的 datetime 值。

Datepart 参数指定在日期的哪个部分返回新值（例如年、月、日、小时、分钟等等），number 值用作 datepart 的增量，date 是开始日期。

下列示例将返回距 2005 年 5 月 29 日两天的日期：

SELECT DATEADD([day], 2, '2005-05-29 10:10:03.001')

（无列名）
2005-05-31 10:10:03.001

DATEDIFF (datepart , startdate , enddate)

DATEDIFF 函数返回跨两个指定日期的日期和时间边界数。

Datepart 参数指定在日期的哪个部分返回新值（例如年、月、日、小时、分钟等等），startdate 是开始日期，enddate 是结束日期。

下列示例将返回第一个和第二个日期之间的分钟数：

SELECT DATEDIFF (minute, '2005-05-29 10:10:03.001',

'2005-06-12 09:28:11.111')

（无列名）
20118

DATEPART (datepart , date)

DATEPART 函数返回表示指定日期的指定 datepart 的整数。

Datepart 参数指定在日期的哪个部分返回值，date 是指定的日期。

下列示例将返回指定日期的月份：

SELECT DATEPART (month, '2005-05-29 10:10:03.001')

（无列名）
5

组合日期和时间函数

通常在 Configuration Manager 报表中会组合使用日期和时间函数。

下列示例将返回当前日期和时间（此例中为 2005-05-29 10:10:03.001）减去 100 天：

SELECT DATEADD([day], - 100, GETDATE())

（无列名）
2005-02-18 10:10:03.001

使用日期和时间函数的查询示例

下列示例将返回一天时间内的状态消息总数。在此查询中使用了 COUNT、GETDATE 和 DATEADD 函数以及 BETWEEN 逻辑运算符和 GROUP BY 与 ORDER BY 子句。

SELECT SiteCode, MessageID, COUNT(MessageID) AS [count],

GETDATE() AS [End Date]

FROM vStatusMessages

WHERE ([Time] BETWEEN DATEADD([day], -1, GETDATE()) AND GETDATE())

AND (MessageID BETWEEN '0' AND '10000')

GROUP BY SiteCode, MessageID

ORDER BY SiteCode, MessageID

站点代码	消息 ID	计数	结束日期
ABC	500	190	2005-05-29 10:10:03.001
ABC	501	130	2005-05-29 10:10:03.001
ABC	502	190	2005-05-29 10:10:03.001
ABC	1105	85	2005-05-29 10:10:03.001
ABC	1106	5	2005-05-29 10:10:03.001
…	…	…	…

联接

要在 Configuration Manager 中创建有效的报表，您需要了解如何联接不同的视图以获取预期的数据。有三种类型的联接：内部、外部和交叉联接。此外还有三种类型的外部联接：左、右和完全外部联接。自联接利用了上述所有联接，但是会联接来自同一视图的记录。

内部联接

在内部联接中，只有当联接字段的值满足指定的条件时才会将来自两个视图的记录进行组合并添加到查询结果中。如果要通过使用 ResourceID 联接 v_R_System 和 v_GS_WORKSTATION_STATUS 视图来使用内部联接，结果将是所有系统及其上次硬件扫描日期的列表。

SELECT v_R_System.Netbios_Name0 AS MachineName,

v_GS_WORKSTATION_STATUS.LastHWScan AS [Last HW Scan]

FROM v_R_System INNER JOIN v_GS_WORKSTATION_STATUS

ON v_R_System.ResourceID = v_GS_WORKSTATION_STATUS.ResourceID

计算机名称	上次硬件扫描
客户端 1	2005-05-29 10:10:03.001
客户端 3	2005-06-12 09:28:11.110

外部联接

外部联接从联接的视图返回所有行，无论两者之间是否有匹配的行。ON 子句将补充数据，而不是对其进行筛选。外部联接的三种类型（左、右和完全联接）表示数据的主要来源。当视图中有 NULL 值时，外部联接特别有帮助。

左外部联接

在使用左外部联接组合两个视图时，结果中将包括左视图中的所有行。在下列查询中，使用左外部联接来联接 v_R_System 和 v_GS_WORKSTATION_STATUS 视图。v_R_System 视图是查询中列出的第一个视图，使其成为左视图。结果将包括所有系统及其上次硬件扫描日期的列表。与内部联接不同，没有扫描硬件的系统仍将以 NULL 值列出（如结果集中所示）。

SELECT v_R_System.Netbios_Name0 AS MachineName,

v_GS_WORKSTATION_STATUS.LastHWScan AS [Last HW Scan]

FROM v_R_System LEFT OUTER JOIN v_GS_WORKSTATION_STATUS

ON v_R_System.ResourceID = v_GS_WORKSTATION_STATUS.ResourceID

计算机名称	上次硬件扫描
客户端 1	2005-05-29 10:10:03.001
客户端 2	NULL
客户端 3	2005-06-12 09:28:11.110

右外部联接

右外部联接基本上与左外部联接相同，区别在于结果中将包括来自右视图的所有行。

完全外部联接

完全外部联接从联接的两个视图检索所有行。它会返回联接条件为 True 的所有成对的行，以及与其他视图中的 NULL 行串联的每个视图中不成对的行。通常不会希望使用此类型的外部联接。

交叉联接

交叉联接返回两个视图的结果，而不是总和。左视图中的每一行与右视图中的每一行匹配。它是一组所有可能的行组合，不进行任何筛选。但是，如果添加了 WHERE 子句，则交叉联接会充当内部联接，它使用条件来筛选所有可能的行组合，从中选出您希望的结果。

自联接

自联接使用上述任何联接类型，但是它是联接到自身的一个视图。在数据库图示中，自联接称为反身关系。

NOT IN 关键字短语

带有关键字短语 NOT IN 的子查询在查找有关一组不满足特定条件的数据的信息时非常有用。在下列示例中，查询返回所有未安装 Notepad.exe 的计算机的 NetBIOS 名称。您必须首先创建一个查询来检测安装了所选文件的所有计算机，如下所示：

SELECT DISTINCT v_R_System.Netbios_Name0

FROM v_R_System INNER JOIN v_GS_SoftwareFile

ON (v_GS_SoftwareFile.ResourceID = v_R_System.ResourceId)

WHERE v_GS_SoftwareFile.FileName = 'Notepad.exe'

在确认第一个查询显示所有安装了 Notepad.exe 的计算机之后，下列子查询语句将使用 NOT IN 关键字短语来查找未安装 Notepad.exe 文件的所有计算机的名称：

SELECT DISTINCT Netbios_Name0

FROM v_R_System

WHERE Netbios_Name0 NOT IN

(SELECT DISTINCT v_R_System.Netbios_Name0