Передать таблицу как параметр в sql-сервер UDF. As в sql


Какова цель ключевого слова SQL «AS»? Безопасный SQL

Вы можете задать псевдонимы таблиц в SQL, набирая идентификатор сразу после имени таблицы.

Вы можете даже использовать ключевое слово AS для обозначения псевдонима.

В чем разница между ними, если таковая имеется?

Я вижу, что старые пользователи DBA склонны писать заявления без AS , но большинство новых руководств используют его.

Обновление: я знаю, в чем смысл псевдонимов таблиц и столбцов. Мне любопытно, в чем причина наличия отдельного ключевого слова для установки псевдонимов, пока он работает без него.

Нет никакой разницы между обоими утверждениями выше. AS – это более явный способ упоминания псевдонима

Все, кто отвечал передо мной, верны. Вы используете его как имя ярлыка псевдонима для таблицы, когда у вас есть длинные запросы или запросы, имеющие соединения. Вот несколько примеров.

Пример 1

SELECT P.ProductName, P.ProductGroup, P.ProductRetailPrice FROM Products AS P

Пример 2.

SELECT P.ProductName, P.ProductRetailPrice, O.Quantity FROM Products AS P LEFT OUTER JOIN Orders AS O ON O.ProductID = P.ProductID WHERE O.OrderID = 123456

Пример 3. Рекомендуется использовать ключевое слово AS и очень рекомендуется, но можно выполнить один и тот же запрос без одного (и часто).

SELECT P.ProductName, P.ProductRetailPrice, O.Quantity FROM Products P LEFT OUTER JOIN Orders O ON O.ProductID = P.ProductID WHERE O.OrderID = 123456

Как вы можете сказать, я оставил ключевое слово AS в последнем примере. И он может использоваться как псевдоним.

Пример 4.

SELECT P.ProductName AS "Product", P.ProductRetailPrice AS "Retail Price", O.Quantity AS "Quantity Ordered" FROM Products P LEFT OUTER JOIN Orders O ON O.ProductID = P.ProductID WHERE O.OrderID = 123456

Выход из примера 4

Product Retail Price Quantity Ordered Blue Raspberry Gum $10 pk/$50 Case 2 Cases Twizzler $5 pk/$25 Case 10 Cases

Когда вы не знаете, какой синтаксис выбрать, особенно если вам нечего отделять, проконсультируйтесь с книгой по эвристике. Насколько я знаю, единственная книга эвристики для SQL – это «Стиль программирования SQL» Джо Селко:

Имя корреляции чаще называют псевдонимом, но я буду формальным. В SQL-92 они могут иметь необязательный оператор AS , и его следует использовать, чтобы дать понять, что что-то дается новое имя. [Р16]

Таким образом, если вашей команде не нравится соглашение, вы можете обвинить Селко – я знаю, что знаю;)

ОБНОВЛЕНИЕ 1: IIRC в течение длительного времени, Oracle не поддерживала ключевое слово AS (предшествующее имя корреляции), которое может объяснить, почему некоторые старые таймеры не используют его обычно.

ОБНОВЛЕНИЕ 2: термин «имя корреляции», хотя используется стандартом SQL, является неуместным. Основная концепция – это « переменная диапазона ».

ОБНОВЛЕНИЕ 3: Я просто перечитываю то, что написал Целько, и он ошибается: таблица не переименовывается! Теперь я думаю:

Имя корреляции чаще называют псевдонимом, но я буду формальным. В стандартном SQL они могут иметь необязательное ключевое слово AS но не должны использоваться, потому что это может создать впечатление, что что-то переименовывается, когда это не так. На самом деле его следует исключить, чтобы указать, что это переменная диапазона.

Ключевое слово AS должно указывать имя ALIAS для вашей таблицы базы данных или столбца таблицы. В вашем примере оба утверждения верны, но есть обстоятельства, в которых требуется предложение AS (хотя сам оператор AS необязателен), например

SELECT salary * 2 AS "Double salary" FROM employee;

В этом случае таблица Employee имеет столбец salary и мы просто хотим получить двойную зарплату с новым названием Double Salary .

Извините, если мои объяснения не эффективны.

Обновление на основе вашего комментария, вы правы, мое предыдущее заявление было неверным. Единственная причина, по которой я могу думать, заключается в том, что предложение AS в течение долгого времени существовало в мире SQL, что оно было включено в настоящее время RDMS для обратной совместимости.

Использование более очевидно, если вы не используете «SELECT *» (что является плохой привычкой, из-за которой вы должны выйти):

SELECT t1.colA, t2.colB, t3.colC FROM alongtablename AS t1, anotherlongtablename AS t2, yetanotherlongtablename AS t3 WHERE t1.colD = t2.colE...

Это формальный способ указания имени корреляции для объекта, чтобы вы могли легко обращаться к нему в другой части запроса.

AS в этом случае является необязательным ключевым словом, определенным в ANSI SQL 92, чтобы определить <<correlation name> , обычно называемое псевдонимом для таблицы.

<table reference> ::= <table name> [ [ AS ] <correlation name> [ <left paren> <derived column list> <right paren> ] ] | <derived table> [ AS ] <correlation name> [ <left paren> <derived column list> <right paren> ] | <joined table> <derived table> ::= <table subquery> <derived column list> ::= <column name list> <column name list> ::= <column name> [ { <comma> <column name> }... ] Syntax Rules 1) A <correlation name> immediately contained in a <table refer- ence> TR is exposed by TR. A <table name> immediately contained in a <table reference> TR is exposed by TR if and only if TR does not specify a <correlation name>.

Кажется, лучшей практикой НЕ использовать ключевое слово AS для псевдонимов таблиц, поскольку оно не поддерживается рядом часто используемых баз данных.

Если вы спроектируете запрос с помощью редактора запросов в SQL Server 2012, например, вы получите следующее:

SELECT e.EmployeeID, s.CompanyName, o.ShipName FROM Employees AS e INNER JOIN Orders AS o ON e.EmployeeID = o.EmployeeID INNER JOIN Shippers AS s ON o.ShipVia = s.ShipperID WHERE (s.CompanyName = 'Federal Shipping')

Однако удаление AS не имеет никакого значения, как в следующем:

SELECT e.EmployeeID, s.CompanyName, o.ShipName FROM Employees e INNER JOIN Orders o ON e.EmployeeID = o.EmployeeID INNER JOIN Shippers s ON o.ShipVia = s.ShipperID WHERE (s.CompanyName = 'Federal Shipping')

В этом случае использование AS является излишним, но во многих других местах оно необходимо.

В ранние дни SQL он был выбран в качестве решения проблемы, связанной с дублированием имен столбцов.

Заимствовать запрос из другого ответа:

SELECT P.ProductName, P.ProductRetailPrice, O.Quantity FROM Products AS P INNER JOIN Orders AS O ON O.ProductID = P.ProductID WHERE O.OrderID = 123456

Столбец ProductID (и, возможно, другие) является общим для обеих таблиц, и, поскольку синтаксис условия соединения требует ссылки на оба, «точечная квалификация» обеспечивает неоднозначность.

Разумеется, лучшим решением было никогда не допускать дублирование имен столбцов в первую очередь! К счастью, если вы используете новый синтаксис NATURAL JOIN , потребность в переменных диапазона P и O уходит:

SELECT ProductName, ProductRetailPrice, Quantity FROM Products NATURAL JOIN Orders WHERE OrderID = 123456

sql.fliplinux.com

10 простых шагов к полному пониманию SQL (операторы SELECT)

SQL

Очень многие программисты смотрят на SQL, как на страшного зверя. SQL — один из немногих декларативных языков, и его поведение в корне отличается от того, как работают императивные, объектно-ориентированные и даже функциональные языки программирования. Хотя многие рассматривают SQL в некоторой степени функциональным.

Я, как инструктор SQL (советую посетить наш великолепный тренинг!) пишу на нем каждый день, включая работу над библиотекой jOOQ с открытым исходным кодом.

 

Поэтому я чувствую себя обязанным донести прелести SQL до умов тех, кто все еще испытывает трудности с его пониманием. Этот урок посвящен тем, кто:

  • Уже работает с SQL, но не до конца его понимает
  • Неплохо знает SQL, но никогда серьезно не задумывался над его структурой
  • Хочет обучать SQL других

В этом руководстве мы сосредоточимся только на операторах SELECT. Другие DML-операторы будут подробно рассмотрены в следующий раз.

Примечание. Этот урок раньше публиковался только на Tech.Pro (оригинальная версия находится здесь). К сожалению, материал Tech.Pro стал недоступным. С разрешения Tech.Pro мы снова публикуем его контент в блоге jOOQ.

И вот…

  1. SQL является декларативным

SELECT first_name, last_name FROM employees WHERE salary > 100000

Все понятно. И вас не волнует, откуда эти записи о сотруднике (employee) берутся. Вам лишь нужны те, у которых достойная зарплата (salary).

  • Что мы из этого узнаем?

Если все так просто, в чем же проблема? Проблема в том, что большинство из нас интуитивно мыслит рамками императивного программирования. Вроде: «машина, сделай это, затем то, но сначала проверь то и се.» А значит, нужно хранить временные результаты в переменных, составлять циклы, вызывать функции и т. д. и т. п.

Забудьте обо всем этом. Думайте о том, как объявлять вещи. Не о том, как сказать машине, вычислить что-либо.

2. Код SQL не является упорядоченным

Обычно путаница происходит из-за того, что элементы кода SQL не расположены в том порядке, в каком они выполняются. Лексический порядок выглядит так:

  • SELECT [ DISTINCT ]
  • FROM
  • WHERE
  • GROUP BY
  • HAVING
  • UNION
  • ORDER BY

Для простоты перечислены не все предложения SQL. Этот словесный порядок принципиально отличается от логического порядка (который в свою очередь может отличаться от порядка выполнения в зависимости от выбора оптимизатора):

  • FROM
  • WHERE
  • GROUP BY
  • HAVING
  • SELECT
  • DISTINCT
  • UNION
  • ORDER BY

Три пункта, которые следует отметить:

  1. Первое предложение здесь FROM, а не SELECT. Сначала происходит загрузка данных с диска в память, чтобы с ними можно было работать.
  2. SELECT выполняется после большинства других предложений. Главное, после FROM и GROUP BY. Это важно понимать, если думаете, что можете ссылаться на элементы, которые объявляете в предложении SELECT из предложения WHERE. Следующее невозможно:
SELECT A.x + A.y AS z FROM A WHERE z = 10 -- z здесь не доступна!

Есть два варианта повторно использовать z. Либо повторить выражение:

SELECT A.x + A.y AS z FROM A WHERE (A.x + A.y) = 10

… либо прибегнуть к производным таблицам, общим табличным выражениям или представлениям данных, чтобы избежать повторения кода. Смотрите примеры ниже.

3. UNION ставится перед ORDER BY в лексическом и логическом порядках. Многие думают, что каждый подзапрос UNION можно упорядочить, но по стандарту SQL и в большинстве диалектов SQL это не так. Хотя в некоторых диалектах позволяется упорядочивание подзапросов или производных таблиц, нет никакой гарантии, что такой порядок будет сохранен после выполнения операции UNION.

Обратите внимание, что не все базы данных реализуют вещи одинаковым образом. Правило номер 2, например, не применяется в точности, как описано выше, для MySQL, PostgreSQL, и SQLite.

Что мы из этого узнаем?

Чтобы избежать распространенных ошибок, всегда следует помнить о лексическом и логическом порядках предложений SQL. Если вы усвоили эти отличия, становится понятным, почему одни вещи работают, другие нет.

Конечно, неплохо, если бы язык был спроектирован таким образом, когда лексическая последовательность фактически отражает логический порядок, как это реализовано в Microsoft LINQ.

3. Вся суть SQL заключается в ссылках на таблицы

Из-за разницы между лексическим упорядочением и логическим упорядочением большинство новичков, вероятно, думают, что значения столбцов являются объектами первого класса в SQL. Но, нет. Наиболее важными являются ссылки на таблицы.

Стандарт SQL определяет предложение FROM следующим образом:

<from clause> ::= FROM <table reference> [ { <comma> <table reference> }... ]

Вывод предложения FROM — это объединенная ссылка на таблицу объединенного значения всех ссылок. Попробуем это переварить.

FROM a, b

Указанная выше запись производит комбинированную ссылку на таблицу значений a и b. Если a имеет 3 колонки и b 5 колонок, тогда выходная таблица “output table” будет состоять из 8 (3 + 5) столбцов.

Записи, содержащиеся в этой комбинированной табличной ссылке являются перекрестным / декартовым произведением a x b. Иными словами, каждая запись a образует пару с записью b. Если a имеет 3 записи, а b 5 записей, описанная выше ссылка на таблицу производит 15 записей (3 x 5).

Этот вывод переводится в предложение GROUP BY (после фильтрации в предложении WHERE), где преобразуется в новый вывод. Разберемся с этим позже.

Если мы посмотрим на эти примеры в свете реляционной алгебры / теории множества, то таблица SQL — это отношение или набор кортежей. Каждое предложение SQL преобразует одно или несколько отношений, чтобы создать новые отношения.

Что мы из этого узнаем?

Всегда думайте категориями ссылок на таблицы, чтобы понять, как данные передаются в предложениях SQL.

4. Ссылки на таблицы SQL могут быть довольно функциональными

Ссылка на таблицу — мощная штука. Примером их силы является ключевое слово JOIN, которое в действительности не является выражением SELECT, а частью специальной ссылки на таблицу. Объединенная таблица, как определено в стандарте SQL (упрощенный):

<table reference> ::= <table name> | <derived table> | <joined table>

Вернемся к нашему примеру:

FROM a, b

a может по сути быть объединенной таблицей:

a1 JOIN a2 ON a1.id = a2.id

Развивая предыдущее выражение, получим:

FROM a1 JOIN a2 ON a1.id = a2.id, b

Хотя не рекомендуется объединять синтаксис, где приводится разделенный запятыми список ссылок на таблицы, с синтаксисом объединенных таблиц, но так можно делать. В результате, объединенная ссылка будет содержать величины a1+a2+b.

Производные таблицы еще мощнее, чем объединенные таблицы. Мы до этого еще дойдем.

Что мы из этого узнаем?

Всегда нужно думать категориями ссылок на таблицы. Это поможет не только понять, как данные переходят в предложениях SQL, (смотрите предыдущий раздел), но и как создаются сложные ссылки на таблицы.

И, что важно, понять, JOIN является ключевым словом для построения соединенных таблиц. А не частью оператора SELECT. Некоторые базы данных позволяют использование JOIN в операторах INSERT, UPDATE, DELETE

5. В SQL следует использовать таблицы JOIN, вместо разделенных запятыми

Ранее мы видели это предложение:

FROM a, b

Продвинутые разработчики SQL, вероятно, скажут, что не следует вообще использовать список с разделителями-запятыми, и всегда отображать таблицы JOIN. Это улучшит читаемость инструкции SQL, и тем самым предотвратить ошибки.

Одна очень распространенная ошибка заключается в том, чтобы где-то забыть предикат JOIN. Подумайте о следующем:

FROM a, b, c, d, e, f, g, h WHERE a.a1 = b.bx AND a.a2 = c.c1 AND d.d1 = b.bc -- etc...

Синтаксис соединенных таблиц:

  • Безопаснее, так как предикаты join можно размещать вблизи соединенных таблиц, тем самым предотвращая ошибки.
  • Более выразительные, так как можно различать OUTER JOIN, INNER JOIN и т. д.

 

Что мы из этого узнаем?

Всегда применяйте JOIN. Никогда не используйте ссылки на таблицы, разделенные запятыми, в предложениях FROM.

6. Различные операции JOIN в SQL

Операции JOIN состоят, в основном, из следующих пяти видов:

  • EQUI JOIN
  • SEMI JOIN
  • ANTI JOIN
  • CROSS JOIN
  • DIVISION

Эти термины обычно используются в реляционной алгебре. SQL задействует другие термины для вышеуказанных понятий, если они вообще существуют. Давайте рассмотрим их поближе:

EQUI JOIN

Это наиболее распространенная операция JOIN. Содержит два подвида:

  • INNER JOIN (или просто JOIN)
  • OUTER JOIN (далее подразделяется на LEFT, RIGHT, FULL OUTER JOIN)

Разницу лучше объяснить на примере:

-- Ссылка на таблицу содержит авторов и их книги -- Есть одна запись для каждой книги и ее автора. -- Авторы без книг НЕ включены author JOIN book ON author.id = book.author_id -- Ссылка на таблицу содержит авторов и их книги -- Есть одна запись для каждой книги и ее автора. --... ИЛИ есть записи "empty" (пустые) для авторов без книг -- ("empty" означает, что все столбцы книги равны NULL) author LEFT OUTER JOIN book ON author.id = book.author_id

 

SEMI JOIN

Эта реляционная концепция в SQL может быть выражена двумя способами: С помощью предиката IN или с использованием предиката EXISTS. «Semi» на латыни означает «половина». Этот тип соединения используется для объединения только «половины» ссылки на таблицу. Что это значит? Рассмотрим вновь вышеуказанное объединение автора и книги. Представим, что нам не нужны комбинации автор — книга, а только авторы, у которых есть книги. Тогда можно написать:

-- Использование IN FROM author WHERE author.id IN (SELECT book.author_id FROM book) -- Использование EXISTS FROM author WHERE EXISTS (SELECT 1 FROM book WHERE book.author_id = author.id)

Хотя нет общего правила, следует предпочесть IN или EXISTS, но можно сказать примерно так:

Поскольку INNER JOIN также выдают результаты авторов, у которых есть книги, многие начинающие подумают, что можно удалить дубликаты с помощью ключевого слова DISTINCT. Им кажется, что SEMI JOIN можно выразить следующим образом:

-- Найти только тех авторов, которые имеют книги SELECT DISTINCT first_name, last_name FROM author JOIN book ON author.id = book.author_id

Так делать не рекомендуется по двум причинам:

  • Замедляется производительность, так как база данных только для того, чтобы удалить дубликаты, должна загрузить в память множество данных.
  • Такой способ решения не совсем правильный, хотя и приводит к верным результатам в этом примере. Но если вы соединяете больше ссылок на таблицы, удаление дубликатов превращается в реальную проблему.

Подробную информацию о злоупотреблении DISTINCT можно найти в этом посте.

ANTI JOIN

Эта реляционная концепция является полной противоположностью SEMI JOIN. Ее можно образовать, просто добавив ключевое слово NOT в предикатах IN или EXISTS. Пример, где мы выберем тех авторов, у которых нет книг:

-- Использование IN FROM author WHERE author.id NOT IN (SELECT book.author_id FROM book) -- Использование EXISTS FROM author WHERE NOT EXISTS (SELECT 1 FROM book WHERE book.author_id = author.id)

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

CROSS JOIN

Этот оператор создает перекрестное произведение двух соединенных ссылок на таблицу, комбинируя каждую запись первой ссылки с записью второй ссылки на таблицу. Мы уже видели раньше, что это может быть достигнуто посредством ссылок на таблицы с разделителями-запятыми в предложении FROM. В редких случаях, когда это действительно необходимо, в большинстве диалектов SQL перекрестное соединение CROSS JOIN можно написать явным образом:

-- Объединить каждого автора с книгой author CROSS JOIN book

DIVISION

Реляционное деление — вот кто настоящий зверь. В кратце, если JOIN это умножение, division (деление) — противоположность операции JOIN. Реляционные деления очень трудно выразить в SQL. Поскольку этот урок для начинающих, деление мы не рассматриваем. Для самых храбрых информация находится здесь, здесь и здесь.

Что мы из этого узнаем?

Много чего. Давайте запихнем это в наши головы. В SQL повсюду ссылки на таблицы. Соединяемые таблицы представляют собой довольно сложные ссылки на таблицы. Но между реляционными выражениями и выражениями SQL есть разница. Не все операции реляционного соединения также являются формальными операциями соединения SQL. Имея немного практики и знаний в реляционной теории, вы всегда сможете выбрать верный тип реляционного соединения JOIN и правильно представить его на языке SQL.

7. Производные таблицы SQL похожи на табличные переменные

Раньше мы узнали, что SQL является декларативным языком, и не имеет переменных (хотя в некоторых диалектах SQL они существуют). Но можно написать нечто похожее на переменные. Эти звери называются производными таблицами.

Производная таблица — это не что иное, как вложенный запрос, заключенный в круглые скобки.

-- Производная таблица FROM (SELECT * FROM author)

Обратите внимание, что некоторые диалекты SQL требуют, чтобы производные таблицы имели корреляционное имя (также называемое псевдонимом).

-- Производная таблица с псевдонимом FROM (SELECT * FROM author) a

Производные таблицы великолепны, если необходимо обойти проблемы, вызванные логическим упорядочением предложений SQL. Например, если вы хотите повторно использовать выражение столбца в предложении SELECT и WHERE, просто напишите (диалект Oracle):

-- Получить имя и фамилию авторов и их возраст в днях SELECT first_name, last_name, age FROM ( SELECT first_name, last_name, current_date - date_of_birth age FROM author ) -- Если возраст больше, чем 10000 дней WHERE age > 10000

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

WITH a AS ( SELECT first_name, last_name, current_date - date_of_birth age FROM author ) SELECT * FROM a WHERE age > 10000

Очевидно, что «a» можно также вывести в отдельное представление для более широкого использования общих подзапросов SQL. Подробнее о представлениях здесь.

Что мы из этого узнаем?

Снова, снова и снова. В SQL все завязано на ссылках, а не столбцах. Как этим воспользоваться. Не бойтесь написания производных таблиц или других сложных ссылок на таблицы.

8. SQL GROUP BY преобразует прежние ссылки на таблицу

Давайте вновь обсудим наше предложение FROM:

FROM a, b

А теперь к соединенной ссылке (см. выше) применим предложение GROUP BY

GROUP BY A.x, A.y, B.z

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

  • Обратите внимание, что другие столбцы могут по-прежнему быть доступными в качестве аргументов агрегатных функций:
SELECT A.x, A.y, SUM(A.z) FROM A GROUP BY A.x, A.y
  • Следует заметить, что MySQL, к сожалению, не придерживается этого стандарта, отчего получается только путаница. Не попадайтесь на уловки в MySQL. GROUP BY преобразует ссылки на таблицу Таким образом, можно ссылаться только на столбцы, также упоминаемые в предложении GROUP BY.

Что мы из этого узнаем?

GROUP BY, опять же, работает с табличными ссылками, преобразовывая их в новую форму.

9. SQL SELECT в реляционной алгебре называется проекцией

Мне лично нравится термин «проекция», так как он используется в реляционной алгебре. После того как вы создали ссылку на таблицу, отфильтровали и преобразовали ее, можете переходить к проецированию в другую форму. Предложение SELECT подобно проектору. Табличная функция использующет выражение значения строки для преобразования каждой записи из ранее созданной ссылки на таблицу в конечный результат.

В предложении SELECT можно работать со столбцами, создавая сложные выражения столбцов как части записи/строки.

Есть много специальных правил в отношении характера доступных выражений, функций и т.д. Главное, нужно помнить следующее:

  1. Можно использовать только ссылки на столбцы, полученные из ссылки на таблицу в «output».
  2. Если у вас есть предложение GROUP BY, вы можете ссылаться только на столбцы из этого предложения или агрегатные функции.
  3. Если нет предложения GROUP BY вместо агрегатных можно использовать оконные функции.
  4. Если нет предложения GROUP BY, нельзя сочетать агрегатные и неагрегатные функции.
  5. Существуют некоторые правила, касающиеся переноса регулярных функций в агрегатные функции и наоборот.
  6. Есть…

Много сложных правил. Которыми можно заполнить еще один урок. Например, причина почему нельзя комбинировать агрегатные функции с неагрегатными функциями в проекции инструкции SELECT без предложения GROUP BY (правило № 4), такова:

  1. Это не имеет смысла. Интуитивно.
  2. Если не помогает интуиция (например, новичкам в SQL), выручают синтаксические правила. В SQL:1999 реализован оператор GROUPING SETS, а в SQL:2003 — пустой оператор grouping sets: GROUP BY (). Всякий раз, когда присутствует агрегатная функция и нет явного предложения GROUP BY, применяется неявный пустой GROUPING SET (правило №2). Следовательно, исходные правила о логическом упорядочении больше не являются верными, и проекция (SELECT) влияет на результат логически предшествующего, но лексически последовательного предложения (GROUP BY).

Запутались? Да. Я тоже. Давайте вернемся к более простым вещам.

Что мы из этого узнаем?

Предложение SELECT может быть одним из самых сложных предложений в SQL, даже если оно выглядит просто. Все другие предложения только переносят ссылки на таблицы от одного к другому. Предложение SELECT портит всю красоту этих ссылок, полностью их преобразовывая путем применения к ним правил.

Чтобы понять SQL, перед использованием оператора SELECT нужно усвоить все остальное. Даже если SELECT является первым предложением в лексической упорядоченности, он должен быть последним.

10. SQL DISTINCT, UNION, ORDER BY и OFFSET — намного проще

После сложного SELECT мы можем снова вернуться к простым истинам:

  • Операции Set (DISTINCT и UNION)
  • Операции упорядочивания (ORDER BY, OFFSET .. FETCH)

Операции Set

Операции set работают с наборами, которые на самом деле являются таблицами. Почти. Теоретически, это несложно понять.

  • DISTINCT удаляет дубликаты после проекции.
  • UNION объединяет два подзапроса и удаляет дубликаты
  • UNION ALL объединяет два подзапроса, сохраняя дубликаты
  • EXCEPT удаляет записи из первого подзапроса, которые также содержатся во втором подзапросе (и затем удаляет дубликаты)
  • INTERSECT сохраняет только записи, содержащиеся в обоих поздапросах (а затем удаляет дубликаты)

Удаление дубликатов во всех этих случаях не имеет смысла. Для объединения подзапросов чаще всего следует применять UNION ALL.

Операции упорядочивания

Упорядочение не является реляционной функцией. Это функция, предназначенная только для SQL. Она применяется в самом конце лексического упорядочения и логического упорядочения инструкции SQL. Использование ORDER BY и OFFSET.. FETCH — это единственный способ гарантировать, что записи могут быть доступны по индексу надежным способом. Все остальные способы упорядочивания всегда произвольны и случайны, даже если они могут показаться воспроизводимыми.

OFFSET .. FETCH — это только один вариант синтаксиса. Другие варианты включают LIMIT, OFFSET в MySQL и PostgreSQL .. или TOP и START AT в SQL Server и Sybase. Хороший обзор различных способов реализации OFFSET.. FETCH можно увидеть здесь.

Приступаем к работе

Как и в каждом языке, чтобы освоить SQL, требуется практика. 10 вышеупомянутых простых шагов помогут вам понять обычные операции SQL. С другой стороны, неплохо учиться также на распространенных ошибках.

bookflow.ru

sql - Передать таблицу как параметр в sql-сервер UDF

У меня была очень похожая проблема, и я смог добиться того, что искал, даже несмотря на то, что я использую SQL Server 2000. Я знаю, что это старый вопрос, но думаю, что он действителен для публикации здесь. решение, так как там должны быть другие, подобные мне, которые используют старые версии и по-прежнему нуждаются в помощи.

Вот трюк: SQL Server не будет принимать передачу таблицы в UDF, и вы не можете передать запрос T-SQL, чтобы функция создавала временную таблицу или даже вызывала хранимую процедуру для этого. Итак, вместо этого я создал зарезервированную таблицу, которую я назвал xtList. Это будет содержать список значений (1 столбец, если необходимо) для работы с.

CREATE TABLE [dbo].[xtList]( [List] [varchar](1000) NULL ) ON [PRIMARY]

Затем хранится процедура для заполнения списка. Это не является абсолютно необходимым, но я думаю, что это очень полезно и наилучшая практика.

-- ============================================= -- Author: Zark Khullah -- Create date: 20/06/2014 -- ============================================= CREATE PROCEDURE [dbo].[xpCreateList] @ListQuery varchar(2000) AS BEGIN SET NOCOUNT ON; DELETE FROM xtList INSERT INTO xtList EXEC(@ListQuery) END

Теперь просто обработайте список любым способом, используя xtList. Вы можете использовать в процедуре (для выполнения нескольких команд T-SQL), скалярных функций (для извлечения нескольких строк) или многозадачных табличных функций (извлекает строки, но, как и внутри таблицы, по 1 строке в строке). Для любого из них вам понадобятся курсоры:

DECLARE @Item varchar(100) DECLARE cList CURSOR DYNAMIC FOR (SELECT * FROM xtList WHERE List is not NULL) OPEN cList FETCH FIRST FROM cList INTO @Item WHILE @@FETCH_STATUS = 0 BEGIN << desired action with values >> FETCH NEXT FROM cList INTO @Item END CLOSE cList DEALLOCATE cList

Желаемое действие будет следующим: в зависимости от того, какой тип созданного объекта:

Сохраненные процедуры

-- ============================================= -- Author: Zark Khullah -- Create date: 20/06/2014 -- ============================================= CREATE PROCEDURE [dbo].[xpProcreateExec] ( @Cmd varchar(8000), @ReplaceWith varchar(1000) ) AS BEGIN DECLARE @Query varchar(8000) << cursor start >> SET @Query = REPLACE(@Cmd,@ReplaceWith,@Item) EXEC(@Query) << cursor end >> END /* EXAMPLES (List A,B,C) Query = 'SELECT x FROM table' with EXEC xpProcreateExec(Query,'x') turns into SELECT A FROM table SELECT B FROM table SELECT C FROM table Cmd = 'EXEC procedure ''arg''' --whatchout for wrong quotes, since it executes as dynamic SQL with EXEC xpProcreateExec(Cmd,'arg') turns into EXEC procedure 'A' EXEC procedure 'B' EXEC procedure 'C' */

Скалярные функции

-- ============================================= -- Author: Zark Khullah -- Create date: 20/06/2014 -- ============================================= CREATE FUNCTION [dbo].[xfProcreateStr] ( @OriginalText varchar(8000), @ReplaceWith varchar(1000) ) RETURNS varchar(8000) AS BEGIN DECLARE @Result varchar(8000) SET @Result = '' << cursor start >> SET @Result = @Result + REPLACE(@OriginalText,@ReplaceWith,@Item) + char(13) + char(10) << cursor end >> RETURN @Result END /* EXAMPLE (List A,B,C) Text = 'Access provided for user x' with "SELECT dbo.xfProcreateStr(Text,'x')" turns into 'Access provided for user A Access provided for user B Access provided for user C' */

Многозначные табличные функции

-- ============================================= -- Author: Zark Khullah -- Create date: 20/06/2014 -- ============================================= CREATE FUNCTION [dbo].[xfProcreateInRows] ( @OriginalText varchar(8000), @ReplaceWith varchar(1000) ) RETURNS @Texts TABLE ( Text varchar(2000) ) AS BEGIN << cursor start >> INSERT INTO @Texts VALUES(REPLACE(@OriginalText,@ReplaceWith,@Item)) << cursor end >> END /* EXAMPLE (List A,B,C) Text = 'Access provided for user x' with "SELECT * FROM dbo.xfProcreateInRow(Text,'x')" returns rows 'Access provided for user A' 'Access provided for user B' 'Access provided for user C' */

qaru.site

Предикат псевдонима группы WITH -- AS в SQL-синтаксисе службы поиска Windows SharePoint Services

Эта документация перемещена в архив и не поддерживается.

Windows SharePoint Services 3

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

…WHERE[ WITH(<columns>) AS #<alias_name>] [,WITH(<columns>) AS #<alias_name>]

При необходимости можно указать несколько псевдонимов групп, разделяя предикаты WITH…AS запятыми.

При ссылке на псевдоним группы в предикате предложения WHERE условие применяется ко всем столбцам группы. Логические значения результатов сопоставления для каждого столбца объединяются с помощью логического оператора ИЛИ.

Перед использованием псевдонима его следует определить. Псевдоним используется только с предложением WHERE. В качестве имени псевдонима необходимо использовать стандартный идентификатор, которому предшествует знак решетки (#).

Примечание

Группировка столбцов и присвоение им псевдонимов с использованием предиката WITH…AS поддерживается только предикатом FREETEXT. Предикат CONTAINS не поддерживает группировку столбцов и присвоение им псевдонимов.

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

<column_identifier> [<weight_assignment>]

Дополнительные сведения об определении весов столбцов см. в описании предикатов FREETEXT и CONTAINS.

Допускается использование стандартных идентификаторов и идентификаторов с разделителями.

msdn.microsoft.com

Агрегатные функции в sql и переименование столбцов

На уроке будет рассмотрена тема sql переименование столбца (полей) при помощи служебного слова AS; также рассмотрена тема агрегатные функции в sql. Будут разобраны конкретные примеры запросов

Переименование полей AS

Имена столбцов в запросах можно переименовывать. Это придает результатам более читабельный вид.

В языке SQL переименование полей связано с использованием ключевого слова AS, которое и используется для переименования имен полей в результирующих наборах

Синтаксис:

SELECT <имя поля> AS <псевдоним> FROM …

SELECT <имя поля> AS <псевдоним> FROM …

Рассмотрим пример переименования в SQL:

Пример БД «Институт»: Вывести фамилии учителей и их зарплаты, для тех преподавателей, у которых зарплата ниже 15000, переименовать поле zarplata на «низкая_зарплата»

✍ Решение: 
1 2 3 SELECT name, zarplata AS низкая_зарплата FROM teachers WHERE zarplata<15000;

SELECT name, zarplata AS низкая_зарплата FROM teachers WHERE zarplata<15000;

Результат:

Переименование столбцов в SQL часто необходимо при вычислении значений, связанных с несколькими полями таблицы. Рассмотрим пример:

Пример БД «Институт»: Из таблицы teachers вывести поле name и вычислить сумму зарплаты и премии, назвав поле «зарплата_премия»

✍ Решение: 
1 2 SELECT name, (zarplata+premia) AS zarplata_premia FROM teachers;

SELECT name, (zarplata+premia) AS zarplata_premia FROM teachers;

Результат:

SQL As 2_1. БД Институт. Вывести фамилии учителей и разницу между их зарплатой и премией. Назвать вывод «зарплата_минус_премия» Задание 2_1. БД «Компьютерные курсы». Из таблицы Личные данные вывести значения полей Код студента, Word, Excel, Access и вычислить среднее значение по полям Word, Excel, Access, назвав поле «Среднее» SQL As 2_2. БД Компьютерный магазин. Вывести объем оперативной памяти в Килобайтах (из Мб получить Кб). Выводить и исходное и получившееся значение Задание 2_2. БД «Компьютерные курсы». Вывести номера телефонов и оценки по Word студентов, успеваемость по курсу Word которых ниже 4 баллов, переименовать поле Word на Низкая успеваемость

Агрегатные функции в SQL

Для получения итоговых значений и вычисления выражений используются агрегатные функции в sql:

Функция Описание
COUNT(*) Возвращает количество строк таблицы.
COUNT(имя поля) Возвращает количество значений в указанном столбце.
SUM(имя поля) Возвращает сумму значений в указанном столбце.
AVG(имя поля) Возвращает среднее значение в указанном столбце.
MIN(имя поля) Возвращает минимальное значение в указанном столбце.
MAX(имя поля) Возвращает максимальное значение в указанном столбце.

Все агрегатные функции возвращают единственное значение.

Функции COUNT, MIN и MAX применимы к любым типам данных.

Функции SUM и AVG используются только для числовых полей.Между функциями COUNT(*) и COUNT() есть разница: вторая при подсчете не учитывает NULL-значения.

 

Важно: при работе с агрегатными функциями в SQL используется служебное слово AS

Пример БД «Институт»: Получить значение самой большой зарплаты среди учителей, вывести итог как «макс_зп»

✍ Решение: 
SELECT MAX(zarplata) AS макс_зп FROM teachers;

SELECT MAX(zarplata) AS макс_зп FROM teachers;

Результаты:

Рассмотрим более сложный пример использования агрегатных функций в sql.

Пример: БД Компьютерный магазин. Найти имеющееся в наличии количество компьютеров, выпущенных производителем Америка✍ Решение: 
1 2 3 4 5 6 7 8 SELECT COUNT( * ) FROM `pc` WHERE `Номер` IN ( SELECT `Номер` FROM product WHERE Производитель = "Америка" )

SELECT COUNT( * ) FROM `pc` WHERE `Номер` IN ( SELECT `Номер` FROM product WHERE Производитель = "Америка" )

Agr func 2_2. БД Институт. Вывести минимальную и максимальную зарплату учителей Agr func 2_3. БД Институт. Выберите название курса, уроки по которому не проводились и не запланированы проводиться. Дополните код:
1 2 3 4 5 6 SELECT `title` FROM `courses` WHERE `title` NOT IN ( ... )

SELECT `title` FROM `courses` WHERE `title` NOT IN ( ... )

Agr func 2_4. БД Институт. Измените предыдущее задание: Посчитайте количество тех курсов, уроки по которым не проводились и не запланированы проводиться. Выводите результат с именем «нет_уроков»

Предложение GROUP BY в SQL

Оператор group by в sql обычно используется совместно с агрегатными функциями.

Агрегатные функции выполняются над всеми результирующими строками запроса. Если запрос содержит оператор GROUP BY, каждый набор строк, заданных в предложении GROUP BY, составляет группу, и агрегатные функции выполняются для каждой группы отдельно.

Рассмотрим пример с таблицей lessons:

Пример:
  1. Выдавать количество проведенных уроков учителем Иванов из таблицы lessons (порядковый номер Иванова 1 (tid)).
  2. SELECT COUNT(tid) AS Иванов FROM lessons WHERE tid=1

    SELECT count(tid) as Иванов from lessons WHERE tid=1

    Результат:

  3. Выдавать количество проведенных уроков учителем Иванов по разным курсам из таблицы lessons
  4. SELECT course, COUNT( tid ) AS Иванов FROM lessons WHERE tid =1 GROUP BY course

    SELECT course, COUNT( tid ) AS Иванов FROM lessons WHERE tid =1 GROUP BY course

    Результат:

Важно: Таким образом, в результате использования GROUP BY все выходные строки запроса разделяются на группы, характеризуемые одинаковыми комбинациями значений в этих столбцах (т.е. агрегатные функции выполняются для каждой группы отдельно).

При этом стоит учесть, что при группировке по полю, содержащему NULL-значения, все такие записи попадут в одну группу.

SQL group by 2_5. БД Компьютерный магазин. Для различных типов принтеров определить их среднюю стоимость и количество (т.е. отдельно по лазерным, струйным и матричным). Использовать агрегатные функции AVG(поле) и COUNT(поле). Результат должен выглядеть так:

SQL group by 2_5. БД Институт. Посчитать количество уроков, проведенных одним и тем же учителем. Результат должен выглядеть так: Задание 2_5. БД «Компьютерные курсы». Вывести количество человек в каждой группе и количество человек на каждом курсе из таблицы Список. Назвать вычисляемые поля «кол_во_в_гр» и «кол_во_на_курс»

Оператор Having SQL

Предложение HAVING в SQL необходимо для проверки значений, которые получены с помощью агрегатной функции после группировки (после использования GROUP BY). Такая проверка не может содержаться в предложении WHERE.

Пример: БД Компьютерный магазин. Посчитать среднюю цену компьютеров с одинаковой скоростью процессора. Выполнить подсчет только для тех групп, средняя цена которых меньше 30000.

✍ Решение: 
SELECT AVG(`Цена`) ,`Скорость` FROM `pc` GROUP BY `Скорость` HAVING AVG(`Цена`) <30000

SELECT AVG(`Цена`) ,`Скорость` FROM `pc` GROUP BY `Скорость` HAVING AVG(`Цена`) <30000

Результат:

Важно: В операторе Having нельзя использовать псевдоним (например, сред_цена), используемый для именования значений агрегатной функции.

Having SQL 2_6. БД Компьютерный магазин. Для различных типов принтеров определить их среднюю стоимость (т.е. отдельно по лазерным, струйным и матричным). Вести подсчет только если средняя стоимость Having SQL 2_6. БД Институт Посчитать количество уроков, проведенных одним и тем же учителем. Выдавать значение только для тех учителей, у которых уроков больше двух.

labs.org.ru