Справочник по операторам 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

SiteCode	SiteName	ReportingSiteCode	Количество
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 — начальная дата.

В следующем примере результатом является дата, спустя два дня после 29 мая 2005 г.:

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

Код сайта	MessageID	Количество	Конечная дата
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

Имя компьютера	Последнее сканирование оборудования
Client1	2005-05-29 10:10:03.001
Client3	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

Имя компьютера	Последнее сканирование оборудования
Client1	2005-05-29 10:10:03.001
Client2	NULL
Client3	2005-06-12 09:28:11.110

Правые внешние соединения

Правое внешнее соединение принципиально является таким же, как и левое внешнее соединение, за исключением того, что все строки из правого представления включаются в результаты.

Полное внешнее соединение

Полное внешнее соединение извлекает все строки из обоих соединяемых представлений. Оно возвращает все парные строки, где условие соединения выполнено (истина), плюс непарные строки из каждого представления, сцепленные со строками NULL из другого представления. Обычно нет потребности использовать этот тип внешнего соединения.

Перекрестное соединение

Перекрестное соединение возвращает произведение двух представлений, а не сумму. Каждая строка левого представления сопоставляется с каждой строкой правого представления. Получается набор всех возможных комбинаций строк без какой-либо фильтрации. Однако, если добавить предложение WHERE, перекрестное соединение будет функционировать как внутреннее соединение, используя условие для фильтрации и выбора из всех возможных комбинаций строк тех, которые требуются.

Самосоединение

Самосоединение использует любой из вышеупомянутых типов соединения, но является представлением, которое соединяется само с собой. В схемах базы данных самосоединение называется рефлексивной связью.

Ключевая фраза NOT IN

Вложенные запросы с ключевой фразой NOT IN очень полезны при поиске сведений о наборе данных, которые не отвечают определенным критериям. В следующем примере запрос возвращает имя NetBIOS всех компьютеров, на которых НЕ установлен Notepad.exe. Сначала нужно создать запрос, который может обнаружить все компьютеры с установленным выбранным файлом, как показано ниже:

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