попробуй это

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

Ответы на вопрос(9)

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

особенно сложных объединений и подзапросов. Я обнаружил, что все больше и больше использую их как «псевдо-представления», чтобы помочь мне понять смысл запроса.

Моя единственная жалоба на них - они не могут быть повторно использованы. Например, у меня может быть сохраненный процесс с двумя операторами обновления, которые могут использовать один и тот же CTE. Но «область действия» CTE - это только первый запрос.

Проблема в том, что «простые примеры», вероятно, на самом деле не нуждаются в CTE!

Тем не менее, очень удобно.

 Fandango6827 янв. 2016 г., 06:32
Когда мне нужен один и тот же CTE более одного раза, я подаю его во временную таблицу, а затем использую временную таблицу столько раз, сколько захочу.
 onedaywhen20 янв. 2011 г., 11:38
«Моя единственная жалоба на них заключается в том, что они не могут быть повторно использованы» - CTE, который вы хотите использовать повторно, должен рассматриваться как кандидат наVIEW :)
 imak19 янв. 2011 г., 22:10
Хорошо. Можете ли вы привести пример с довольно сложным примером, который может помочь мне осмыслить эту концепцию?
 n8wrl05 июн. 2013 г., 15:02
@onedaywhen: Понятно, но это подразумевает глобальный охват, который мне не всегда удобен. Иногда в рамках процесса я хотел бы определить CTE и использовать его для выбора и обновления или выбора похожих данных из разных таблиц.

Использовать вычисленное значение в предложении where. Это кажется немного чище, чем производная таблица.

Предположим, есть две таблицы - Вопросы и Ответы, объединенные при помощи Вопросов.ID = Ответы.Question_Id (и идентификатор теста)

WITH CTE AS
(
    Select Question_Text,
           (SELECT Count(*) FROM Answers A WHERE A.Question_ID = Q.ID) AS Number_Of_Answers
    FROM Questions Q
)
SELECT * FROM CTE
WHERE Number_Of_Answers > 0

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

WITH cte AS
(
    SELECT [Quiz_ID] 
      ,[ID] AS Question_Id
      ,null AS Answer_Id
          ,[Question_Text]
          ,null AS Answer
          ,1 AS Is_Question
    FROM [Questions]

    UNION ALL

    SELECT Q.[Quiz_ID]
      ,[Question_ID]
      ,A.[ID] AS  Answer_Id
      ,Q.Question_Text
          ,[Answer]
          ,0 AS Is_Question
        FROM [Answers] A INNER JOIN [Questions] Q ON Q.Quiz_ID = A.Quiz_ID AND Q.Id = A.Question_Id
)
SELECT 
    Quiz_Id,
    Question_Id,
    Is_Question,
    (CASE WHEN Answer IS NULL THEN Question_Text ELSE Answer END) as Name
FROM cte    
GROUP BY Quiz_Id, Question_Id, Answer_id, Question_Text, Answer, Is_Question 
order by Quiz_Id, Question_Id, Is_Question Desc, Name
 Sam17 сент. 2014 г., 08:50
Разве ваш первый пример не может быть упрощен до использования вложенного запроса вместо CTE?
 Ufos19 янв. 2017 г., 14:36
Вы должны были добавить первый без CTE, тогда сразу становится понятно, почему последний полезен.
 Manachi12 мая 2016 г., 09:44
Оба примера могут быть.
Решение Вопроса

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

Примером самореференции является рекурсия:Рекурсивные запросы с использованием CTE

Для захватывающих определений MicrosoftВзято из Книги Онлайн:

CTE может использоваться для:

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

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

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

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

 Martin Smith19 янв. 2011 г., 22:28
@cyberkiwi - Какой бит? Что самостоятельное соединение приведет к 2 различным вызовам? Смотрите пример в этом ответеstackoverflow.com/questions/3362043/...
 RichardTheKiwi19 янв. 2011 г., 22:25
@ Мартин - я удивлен. Вы можете подтвердить это заявление?
 RichardTheKiwi19 янв. 2011 г., 22:37
Интересный факт о CTE. Мне всегда было интересно, почему NEWID () в CTE изменяется, когда на CTE ссылаются более одного раза.select top 100 * into #tmp from master..spt_values order by 1,2,3,4 select A.number, COUNT(*) from #tmp A inner join #tmp B ON A.number = B.number+1 group by A.number противwith CTE AS (select top 100 * from master..spt_values order by 1,2,3,4) select A.number, COUNT(*) from CTE A inner join CTE B ON A.number = B.number+1 group by A.number
 imak19 янв. 2011 г., 22:25
@ Джон Спасибо, я нахожу4guysfromrolla.com/webtech/071906-1.shtml тоже очень полезно
 Martin Smith19 янв. 2011 г., 22:11
Ага. Вы не можете самостоятельно присоединиться к производной таблице. Стоит подчеркнуть, что самостоятельное присоединение к CTE все равно оставит вас с двумя отдельными вызовами.

которое является новой функцией, которая была введена в SQL Server 2005 и также доступна в более поздних версиях.

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

Синтаксис объявления CTE (общее табличное выражение): -

with [Name of CTE]
as
(
Body of common table expression
)

Давайте возьмем пример:

CREATE TABLE Employee([EID] [int] IDENTITY(10,5) NOT NULL,[Name] [varchar](50) NULL)

insert into Employee(Name) values('Neeraj')
insert into Employee(Name) values('dheeraj')
insert into Employee(Name) values('shayam')
insert into Employee(Name) values('vikas')
insert into Employee(Name) values('raj')

CREATE TABLE DEPT(EID INT,DEPTNAME VARCHAR(100))
insert into dept values(10,'IT')
insert into dept values(15,'Finance')
insert into dept values(20,'Admin')
insert into dept values(25,'HR')
insert into dept values(10,'Payroll')

Я создал две таблицы сотрудника и отдела и вставил 5 строк в каждую таблицу. Теперь я хотел бы объединить эти таблицы и создать временный набор результатов для дальнейшего использования.

With CTE_Example(EID,Name,DeptName)
as
(
select Employee.EID,Name,DeptName from Employee 
inner join DEPT on Employee.EID =DEPT.EID
)
select * from CTE_Example

Давайте возьмем каждую строку утверждения одну за другой и поймем.

Чтобы определить CTE, мы пишем предложение «with», затем мы даем имя табличному выражению, здесь я дал имя как «CTE_Example»

Затем мы пишем «как» и заключаем наш код в две скобки (---), мы можем объединить несколько таблиц в заключенных в скобки.

В последней строке я использовал «Select * from CTE_Example», мы ссылаемся на выражение «Общая таблица» в последней строке кода, поэтому мы можем сказать, что это как представление, где мы определяем и используем представление в одном Пакет и CTE не хранятся в базе данных как постоянный объект. Но это ведет себя как представление. мы можем выполнить оператор удаления и обновления для CTE, и это будет иметь прямое влияние на таблицу, на которую ссылаются, которая используется в CTE. Давайте рассмотрим пример, чтобы понять этот факт.

With CTE_Example(EID,DeptName)
as
(
select EID,DeptName from DEPT 
)
delete from CTE_Example where EID=10 and DeptName ='Payroll'

В приведенном выше заявлении мы удаляем строку из CTE_Example, и она удаляет данные из ссылочной таблицы «DEPT», которая используется в CTE.

 Holger Jakobs07 мая 2014 г., 21:27
Я до сих пор не понимаю, в чем суть. Какая разница между этим и простым удалением из DEPT с точно таким же условием? Кажется, это не облегчает жизнь.
 ;with cte as
  (
  Select Department, Max(salary) as MaxSalary
  from test
  group by department
  )  
  select t.* from test t join cte c on c.department=t.department 
  where t.salary=c.MaxSalary;

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

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

Вот пример вырезания и вставки для игры:

WITH [cte_example] AS (
SELECT 1 AS [myNum], 'a num' as [label]
UNION ALL
SELECT [myNum]+1,[label]
FROM [cte_example]
WHERE [myNum] <=  10
)
SELECT * FROM [cte_example]
UNION
SELECT SUM([myNum]), 'sum_all' FROM [cte_example]
UNION
SELECT SUM([myNum]), 'sum_odd' FROM [cte_example] WHERE [myNum] % 2 = 1
UNION
SELECT SUM([myNum]), 'sum_even' FROM [cte_example] WHERE [myNum] % 2 = 0;

наслаждаться

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

Но с помощью CTE (как ответил Тим Шмельтер наВыберите первый экземпляр записи)

WITH CTE AS(
    SELECT myTable.*
    , RN = ROW_NUMBER()OVER(PARTITION BY patientID ORDER BY ID)
    FROM myTable 
)
SELECT * FROM CTE
WHERE RN = 1

Как видите, это намного легче читать и поддерживать. И по сравнению с другими запросами, намного лучше в производительности.

MS SQL не позволяет использовать ORDER BY с UPDATE, но с помощью CTE вы можете сделать это следующим образом:

WITH cte AS
    (
        SELECT TOP(5000) message_compressed, message, exception_compressed, exception
        FROM logs
        WHERE Id >= 5519694 
        ORDER BY Id
    )
UPDATE  cte
SET     message_compressed = COMPRESS(message), exception_compressed = COMPRESS(exception)

Смотрите здесь для получения дополнительной информации:Как обновить и заказать с помощью MS SQL

Ваш ответ на вопрос