Sql with as: WITH обобщенное_табличное_выражение (Transact-SQL) — SQL Server

Содержание

временный — альтернативный способ SQL «With As»

спросил
12 лет, 10 месяцев назад

Изменено
1 месяц назад

Просмотрено
15 тысяч раз

В предыдущем вопросе вы, ребята, помогли мне получить данные из другой строки. Заявление, которое я использую, отлично работает в MS SQL Server Managment Studio. Я могу выполнить оператор без ошибок и вернуть нужные мне данные. Однако мне нужно запустить эти данные в нашей программе внешнего интерфейса. Когда я пытаюсь запустить свое выражение в этой программе, оно просто зависает. У меня такое ощущение, что часть этого утверждения «С как» вызывает проблемы. Можно ли как-то переписать этот оператор, поместив эту временную таблицу в подзапрос?

 С Temp1 AS (ВЫБРАТЬ
SkillTargetID = Agent_Logout. SkillTargetID,
LogoutDateTime = Agent_Logout.LogoutDateTime,
LogonDate = DateAdd(s,-1 * Agent_Logout.LoginDuration, Agent_Logout.LogoutDateTime),
ROW_NUMBER() OVER(PARTITION BY Agent_Logout.SkillTargetID ORDER BY Agent_Logout.LogoutDateTime ASC) RowVersion,
LoginDuration = Agent_Logout.LoginDuration
ОТ Agent_Logout)
ВЫБИРАТЬ
AgentID = Base.SkillTargetID,
ДатаВхода = Базовая.ДатаВхода,
BaseLogout = Base.LogoutDateTime,
BaseDuration = Base.LoginDuration,
NextLogon = Temp1.LogonDate,
LogoutDuration = DateDiff(s,Base.LogoutDateTime, Temp1.LogonDate)
ОТ базы Temp1
LEFT JOIN Temp1 ON Base.SkillTargetID = Temp1.SkillTargetID
И Base.RowVersion = Temp1.RowVersion-1

sql
временный

Если вы просто хотите материализовать его, вы можете сделать

 ;С Temp1 AS (
ВЫБИРАТЬ
SkillTargetID = Agent_Logout.SkillTargetID,
LogoutDateTime = Agent_Logout.LogoutDateTime,
LogonDate = DateAdd(s,-1 * Agent_Logout.LoginDuration, Agent_Logout. LogoutDateTime),
ROW_NUMBER() OVER(PARTITION BY Agent_Logout.SkillTargetID ORDER BY Agent_Logout.LogoutDateTime ASC) RowVersion,
LoginDuration = Agent_Logout.LoginDuration
ОТ Agent_Logout)
ВЫБЕРИТЕ * INTO #Temp1 ИЗ Temp1
ВЫБИРАТЬ
AgentID = Base.SkillTargetID,
ДатаВхода = Базовая.ДатаВхода,
BaseLogout = Base.LogoutDateTime,
BaseDuration = Base.LoginDuration,
NextLogon = #Temp1.LogonDate,
LogoutDuration = DateDiff(s,Base.LogoutDateTime,#Temp1.LogonDate)
ОТ базы #Temp1
LEFT JOIN #Temp1 ON Base.SkillTargetID = #Temp1.SkillTargetID
И Base.RowVersion = #Temp1.RowVersion-1

Однако он не совсем понимает, что вы имеете в виду под зависанием при работе в вашей внешней программе. Вы используете запрос точно так, как написано, или вы каким-то образом его параметризуете?

Вы оба раза запускаете его с одними и теми же данными?

Ниже то, что у меня получилось. Это работает с интерфейсной программой Cisco, которую мы используем.

 DECLARE @dtStartDateTime DATETIME, @dtEndDateTime DATETIME, @agentid VARCHAR
SET @dtStartDateTime = :start_date SET @dtEndDateTime = :end_date
ВЫБИРАТЬ
    ПолноеИмя = Temp1. ПолноеИмя,
    AgentID = Temp1.SkillTargetID,
    ДатаВхода = Temp1.ДатаВхода,
    LogoutDate = Temp1.LogoutDateTime,
    LoginDuration = Temp1.LoginDuration,
    Версия_Строки# = Temp1.Версия_Строки,
    AgentID2 = Temp2.SkillTargetID,
    LogonDate2 = Temp2.LogonDate,
    LogoutDate2 = Temp2.LogoutDateTime,
    ВерсияСтроки#2 = Temp2.ВерсияСтроки,
    LogoutDuration = DateDiff(s, Temp1.LogoutDateTime, Temp2.LogonDate)
ОТ
(ВЫБИРАТЬ
    ПолноеИмя = Человек.Фамилия + ', ' + Человек.Имя,
    SkillTargetID = Agent_Logout.SkillTargetID,
    LogoutDateTime = Agent_Logout.LogoutDateTime,
    LogonDate = DateAdd(s,-1 * Agent_Logout.LoginDuration, Agent_Logout.LogoutDateTime),
    ROW_NUMBER() OVER(PARTITION BY Agent_Logout.SkillTargetID ORDER BY Agent_Logout.LogoutDateTime ASC) как RowVersion,
    LoginDuration = Agent_Logout.LoginDuration
    ОТ Agent_Logout, Агент, Лицо
    ГДЕ Agent_Logout.SkillTargetID = Agent.SkillTargetID и Agent.PersonID = Person.PersonID
) Темп1,
(ВЫБИРАТЬ
    SkillTargetID = Agent_Logout. SkillTargetID,
    LogoutDateTime = Agent_Logout.LogoutDateTime,
    LogonDate = DateAdd(s,-1 * Agent_Logout.LoginDuration, Agent_Logout.LogoutDateTime),
    ROW_NUMBER() OVER(PARTITION BY Agent_Logout.SkillTargetID ORDER BY Agent_Logout.LogoutDateTime ASC) as RowVersion
    ОТ Agent_Logout
    ГДЕ Agent_Logout.SkillTargetID = Agent_Logout.SkillTargetID
) Темп2
ГДЕ Temp1.SkillTargetID = Temp2.SkillTargetID и Temp1.RowVersion = (Temp2.RowVersion - 1) И
(Temp1.LogonDate >= :start_date
И Temp1.LogonDate <= :end_date) И
Temp1.SkillTargetID IN (:agentid)
ЗАКАЗАТЬ ПО Temp1.SkillTargetID, Temp1.RowVersion

Зарегистрируйтесь или войдите в систему

Зарегистрируйтесь с помощью Google

Зарегистрироваться через Facebook

Зарегистрируйтесь, используя адрес электронной почты и пароль

Опубликовать как гость

Электронная почта

Требуется, но никогда не отображается

Опубликовать как гость

Электронная почта

Требуется, но не отображается

Нажимая «Опубликовать свой ответ», вы соглашаетесь с нашими условиями обслуживания и подтверждаете, что прочитали и поняли нашу политику конфиденциальности и кодекс поведения.

CTE в примерах SQL Server

Автор: Ryan Kennedy   |
Обновлено: 13 мая 2021 г.   |
Комментарии (2)   | Связанный: Еще > Общие табличные выражения

Проблема

Как любой разработчик, использующий SQL,
написание длинных запросов, содержащих обширную логику, является обычным явлением.
Это может включать в себя множество объединений, различные уровни агрегации и фильтрацию по
любое количество столов.
В некоторых случаях набор результатов не может быть получен одним изолированным запросом (SELECT
x FROM y WHERE z) и требует объединения нескольких запросов. Кроме того,
для более сложных запросов лучший способ написать запрос часто состоит в том, чтобы разбить его на части.
в логические шаги и построить его шаг за шагом.

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

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

Решение

Приведенные ниже запросы будут использовать базу данных AdventureWorks от Microsoft в
дополнение к SQL Server Management Studio и Microsoft SQL Server Developer Edition.

Вы можете бесплатно загрузить последнюю версию этого программного обеспечения по следующему адресу:
ссылки:

Версия SQL Server для разработчиков
SQL
Студия управления сервером
База данных AdventureWorks DW

Что такое КТР?

По чистому определению CTE — это «временный именованный набор результатов».
На практике CTE — это результирующий набор, который остается в памяти на время одного действия.
выполнение оператора SELECT, INSERT, UPDATE, DELETE или MERGE.

Давайте разберем это и посмотрим на синтаксис, чтобы лучше понять
что это значит и почему это полезно.

Базовый синтаксис CTE следующий:

 WITH  ([имена столбцов])
КАК
(
   
)
<операция>

Разобрано — предложение WITH сообщает SQL Server, что мы собираемся объявить
CTE, а — это то, как мы называем набор результатов.
для справки позже. Следовательно, «именованный набор результатов».

[имена столбцов] — это то место, где вы можете использовать псевдонимы имен столбцов, которые появятся
КТЭ. Это необязательная часть синтаксиса, и я часто просто использую псевдоним
столбец в части синтаксиса.

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

Местозаполнитель — это то место, где мы будем выполнять наше фактическое выполнение и
ссылка на КТЭ. Как упоминалось ранее, это может быть SELECT, INSERT, UPDATE, DELETE,
или оператор MERGE T-SQL. Ниже мы рассмотрим некоторые из них.

Давайте посмотрим на пример CTE, чтобы понять это:

 С Simple_CTE
КАК (
   ВЫБЕРИТЕ дд.CalendarYear
      ,fs.OrderDateKey
      ,fs.ProductKey
      ,fs.OrderQuantity * fs.UnitPrice AS TotalSale
      ,dc.Имя
      ,dc.LastName
   ОТ [dbo].[FactInternetSales] fs
   ВНУТРЕННЕЕ СОЕДИНЕНИЕ [dbo].[DimCustomer] dc ON dc.CustomerKey = fs.CustomerKey
   ВНУТРЕННЕЕ СОЕДИНЕНИЕ [dbo].[DimDate] dd ON dd.DateKey = fs.OrderDateKey
   )
ВЫБИРАТЬ *
ОТ Simple_CTE

В приведенном выше примере мы
создали CTE, который содержит несколько объединений, специально выбранный список столбцов и
производный столбец TotalSale. Наша деятельность в
этот пример - просто еще один оператор SELECT, просто чтобы показать, как можно использовать CTE.
в простейшей форме.

CTE с SELECT

Разберем синтаксис немного дальше. Часто вам может понадобиться
сделать многоуровневую агрегацию. То есть совокупность совокупностей.
CTE могут быть отличным способом написать запрос такого типа в удобочитаемом виде.

 WITH Sum_OrderQuantity_CTE 
 AS ( 
 SELECT ProductKey 
 ,EnglishMonthName 
 , SUM(OrderQuantity) AS TotalOrdersByMonth 
 FROM [dbo].[FactInternetSales] fs 
 INNER JOIN [dbo].[DimDate] dd ON dd. DateKey = fs.OrderDateKey 
 GROUP BY ProductKey, EnglishMonthName  ) 
 SELECT ProductKey, AVG(TotalOrdersByMonth) AS 'Среднее общее количество заказов по месяцам' 
 FROM Sum_OrderQuantity_CTE 
 GROUP BY ProductKey 
 ORDER BY ProductKey

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

CTE со ВСТАВКОЙ

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

Сначала создайте эту таблицу, чтобы у нас была таблица для вставки:

 СОЗДАТЬ ТАБЛИЦУ [dbo].[TestTable](
   [CalendarYear] [smallint] NOT NULL,
   [OrderDateKey] [int] НЕ NULL,
   [ProductKey] [int] НЕ NULL,
   [TotalSale] [деньги] NULL,
   [Имя] [nvarchar](50) NULL,
   [Фамилия] [nvarchar](50) NULL
)

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

 С Simple_CTE
КАК (
   ВЫБЕРИТЕ дд.CalendarYear
      ,fs.OrderDateKey
      ,fs.ProductKey
      ,fs.OrderQuantity * fs.UnitPrice AS TotalSale
      ,dc.Имя
      ,dc.LastName
   ОТ [dbo].[FactInternetSales] fs
   ВНУТРЕННЕЕ СОЕДИНЕНИЕ [dbo]. [DimCustomer] dc ON dc.CustomerKey = fs.CustomerKey
   ВНУТРЕННЕЕ СОЕДИНЕНИЕ [dbo].[DimDate] dd ON dd.DateKey = fs.OrderDateKey
   )
 
ВСТАВИТЬ В dbo.TestTable
ВЫБЕРИТЕ * ОТ Simple_CTE

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

CTE с ОБНОВЛЕНИЕМ

Подобно CTE с DELETE, мы можем обновить таблицу, используя тот же метод.

Во-первых, давайте создадим резервную таблицу, чтобы не обновлять
фактическая таблица:

 ВЫБЕРИТЕ *
В dbo.DimCustomerBakUpdate
ОТ DimCustomer

Далее мы можем обновить эту таблицу. В приведенном ниже примере мы обновляем все
записей, где AddressLine2 имеет значение NULL, как указано в предложении WHERE, вместо этого должна быть пустой строкой.

 С Update_CTE
КАК (
   ВЫБИРАТЬ *
   ОТ dbo.DimCustomerBakUpdate
   ГДЕ AddressLine2 имеет значение NULL
   )
ОБНОВЛЕНИЕ Update_CTE
УСТАНОВИТЕ AddressLine2 = ''

Вы можете увидеть результат –
мы создали результирующий набор всех записей, где AddressLine2 имеет значение null, а затем
применил обновление к этому конкретному набору результатов. Опять же, это
упрощенный пример, призванный продемонстрировать возможности оператора.

CTE с УДАЛЕНИЕМ

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

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

Точно так же, как мы сделали обновление, давайте создадим еще одну резервную таблицу для удаления
записи от:

 ВЫБЕРИТЕ *
В dbo.DimCustomerBak
ОТ DimCustomer

Теперь мы можем удалить из этой таблицы с помощью CTE. В этом примере мы будем
удалить все записи, где AddressLine2 имеет значение null.

 С MissingAddress_CTE
КАК (
   ВЫБИРАТЬ *
   ОТ dbo.DimCustomerBak
   ГДЕ AddressLine2 имеет значение NULL
   )
УДАЛИТЬ
ОТ MissingAddress_CTE

Когда
мы снова выбираем из таблицы, вы можете видеть, что больше нет записей с
NULL в столбце AddressLine2.

Несколько CTE в одном запросе

Наконец, вы не ограничены простым написанием одного CTE в запросе. Ты
можете написать сколько угодно и разделить их запятой:

 С Sales_Cust_Join_CTE
КАК (
   ВЫБРАТЬ fs.OrderDateKey
      ,fs.ProductKey
      ,fs.OrderQuantity * fs.UnitPrice AS TotalSale
      ,dc.Имя
      ,dc.LastName
   ОТ [dbo].[FactInternetSales] fs
   ВНУТРЕННЕЕ СОЕДИНЕНИЕ [dbo].[DimCustomer] dc ON dc.CustomerKey = fs.CustomerKey
   )
   ,Дата_CTE
КАК (
   ВЫБЕРИТЕ ключ даты
      ,Календарный год
   ОТ [dbo].[DimDate]
   )
ВЫБЕРИТЕ КалендарьГод
   ,Ключ продукта
   ,SUM(TotalSale) AS TotalSales
ОТ Sales_Cust_Join_CTE
ВНУТРЕННЕЕ СОЕДИНЕНИЕ Date_CTE ON Date_CTE.DateKey = Sales_Cust_Join_CTE.OrderDateKey
СГРУППИРОВАТЬ ПО CalendarYear
   ,Ключ продукта
ЗАКАЗАТЬ ПО CalendarYear ASC
   ,Общий объем продаж DESC

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

Предостережение: если вы обнаружите, что у вас много CTE, код может стать более
сложно читать. Так что, хотя они могут быть отличным инструментом, постарайтесь не переусердствовать.
с ними.

Когда использовать CTE, а когда нет

Хотя CTE — отличный способ писать более чистый код, их не следует использовать в
каждый сценарий. Например, одним из основных ограничений является то, что CTE ограничен
в рамках одного исполнения. На практике это означает, что если
вам нужно сделать более одной операции с этим набором результатов, вам нужно объявить
КТР несколько раз. Это неэффективно, потому что мы делаем работу
CTE несколько раз.

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

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

Подробнее о временных таблицах читайте в этой статье.

Типы временных таблиц SQL Server.

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

Давайте посмотрим, что я имею в виду:

 С Simple_CTEAS (
 ВЫБЕРИТЕ дд.CalendarYear
 ,fs.OrderDateKey
 ,fs.ProductKey
 ,fs.OrderQuantity * fs.UnitPrice AS TotalSale
 ,dc.Имя
 ,dc.LastName
 ОТ [dbo].[FactInternetSales] fs
 ВНУТРЕННЕЕ СОЕДИНЕНИЕ [dbo].[DimCustomer] dc ON dc.CustomerKey = fs.