Общие табличные выражения SQL для пользователей LuckyTemplates

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

С помощью CTE вы можете разбить любую задачу на небольшие и управляемые запросы для решения сложных проблем.

Теперь давайте рассмотрим, как CTE может сделать вашу работу более эффективной при устранении неполадок в коде.

Оглавление

• Синтаксис для создания общих табличных выражений SQL 
• Несколько общих табличных выражений SQL 
  • Использование нескольких CTE для решения сложных кодов
  • Использование CTE с оператором Case
• Заключение

Синтаксис для создания общих табличных выражений SQL 

Во-первых, вы должны ввести « WITH », затем имя вашего CTE Expression , а затем « AS ».

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

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

В нашем примере мы будем использовать « USACusts » в качестве имени выражения .

И для запроса мы будем использовать этот код. 

Этот запрос создаст виртуальную таблицу. Он покажет общую или совокупную сумму, сгруппированную по идентификатору клиента, где страна, к которой он принадлежит, — « США ». Данные будут извлечены из таблицы Sales.Customers .

Будут видны два столбца: custid и Agg_Amount . Обратите внимание, что таблица USACusts — это не физическая таблица , а только виртуальная таблица. Это означает, что он не будет виден в нашей базе данных.

После запроса мы можем добавить оператор SELECT .

Этот оператор выберет все значения Agg_Amount , превышающие 1000, из таблицы USACusts .

Несколько общих табличных выражений SQL 

Несколько CTE могут помочь вам устранить сложные ошибки в вашем коде, разбив их на более мелкие фрагменты. Кроме того, вы можете определить столько CTE , сколько хотите. Ниже приведен пример нескольких CTE .

В этом примере у нас есть первый CTE — C1 . Оттуда мы выбрали YEAR(orderdate) и создали 2 столбца: orderyear и custid из таблицы Sales.Orders .

Для второго CTE с именем C2 мы выбрали orderyear и собрали номер custid с помощью команды COUNT . Затем мы назвали его numcusts from C1 . Условие WHERE также было добавлено в C2 , чтобы выбрать только год заказа , который больше 2015 . Затем мы сгруппировали их по порядку года .

После этого мы выбрали orderyear и numcusts из C2 . Затем мы добавили условие WHERE , чтобы выбрать только количество клиентов, превышающее 500 .

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

Использование нескольких CTE для решения сложных кодов

Далее я продемонстрирую дополнительные примеры, чтобы еще больше подчеркнуть использование CTE при устранении неполадок с большим набором данных. Вот еще один пример нескольких CTE .

Обратите внимание, что вы можете называть CTE так, как хотите, но в этом блоге мы используем C1 , C2 , C3 и т. д., чтобы легко определить и понять, как мы их используем в наших примерах.

Возвращаясь к примеру, мы создали C1 , чтобы выбирать только те продажи, для которых количество заказов больше 5 , из таблицы Sales.SalesOrderDetails .

Затем в C2 мы использовали данные, которые мы сохранили в C1 , чтобы присоединиться к продукту на основе Product.ID и сгруппировать их по названию продукта (p.Name) и цвету продукта (p.Color) .

Поскольку мы объявили условие в C1 , мы сгруппируем название продукта и цвет продукта в C2 только для заказов, в которых количество заказа (OrderQty) больше 5 .

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

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

Для C1 отображаются все данные из таблицы Sales.SalesOrderDetail с количеством заказа больше 5 .

Затем C2 показывает вывод количества заказа, сгруппированного по названию продукта и цвету из C1 . В C2 мы можем видеть столбец orderqty , но на самом деле его нет в C2 , потому что это просто вычисляемый столбец, который мы создали из C1 . Отсюда мы можем начать использовать любое условие в столбце orderqty и выполнить устранение неполадок.

Итак, теперь вы видите, как несколько CTE могут помочь нам в устранении ошибок в сложных кодах, сохраняя их в CTE , и приступать к устранению неполадок, не повреждая фактический код.

Использование CTE с оператором Case

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

В этом случае мы собираемся использовать оператор CASE , который создаст столбец Product Category с результатами, основанными на агрегированном количестве orderqty из C2 .

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

В следующем примере мы создадим агрегированную сумму на основе категории продуктов , которую мы создали в предыдущем примере. Но для этого мы поместим оператор CASE для категории продукта в третье CTE — C3 .

Прежде чем продолжить, давайте проверим вывод C3 , добавив « SELECT * FROM C3 » ниже C3 .

В C3 мы видим столбцы ProductName , Color , orderqty и Product Category . Отсюда мы можем начать агрегирование на основе категории продукта . Давайте сделаем это, используя следующую команду ниже C3 .

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

Изначально у нас не было столбцов Product Category и orderqty , но мы можем генерировать примеры выходных данных на основе этих столбцов, используя несколько CTE .

На данный момент вы можете видеть, что мы можем делать что угодно с помощью CTE для более эффективной работы с нашими кодами.

Временные таблицы и представления SQL для пользовательского фильтра LuckyTemplates
в SQL с использованием IN, NOT IN, LIKE и NOT LIKE
Извлечение данных SQL с использованием OFFSET и FETCH

Заключение

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

Изучив это, вы теперь сможете более эффективно отлаживать свои коды. Помните, что вы можете использовать любое имя для CTE , и вы не можете использовать или вызывать любое CTE , если оно не создано. Как только вы познакомитесь с CTE , вы сможете считать себя пользователем SQL среднего уровня .

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

Всего наилучшего,

Хафиз