recursivas - sql server recursive query

Cuándo usar Common Table Expression(CTE)

Question

Empecé a leer sobre Common Table Expression y no puedo pensar en un caso de uso en el que necesite usarlos. Parecen ser redundantes, ya que lo mismo se puede hacer con tablas derivadas. ¿Hay algo que me falta o que no entiendo bien? ¿Puede alguien darme un ejemplo simple de limitaciones con consultas de selección, derivadas o tablas temporales para hacer el caso de CTE? Cualquier ejemplo simple sería muy apreciado.

Answer 1

Generalmente utilizo CTE para crear vistas donde no puedo usar tablas temporales locales o globales.

Answer 2

Hay dos razones por las que veo usar cte''s.

Para usar un valor calculado en la cláusula where. Esto me parece un poco más limpio que una tabla derivada.

Supongamos que hay dos tablas: preguntas y respuestas unidas por Questions.ID = Answers.Question_Id (y id del examen)

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

Aquí hay otro ejemplo en el que quiero obtener una lista de preguntas y respuestas. Quiero que las Respuestas se agrupen con las preguntas en los resultados.

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

Answer 3

Hoy vamos a aprender sobre la expresión de tabla común que es una nueva característica que se introdujo en SQL Server 2005 y también está disponible en versiones posteriores.

Expresión de tabla común: la expresión de tabla común se puede definir como un conjunto de resultados temporal o, en otras palabras, es un sustituto de las vistas en SQL Server. La expresión de tabla común solo es válida en el lote de instrucción donde se definió y no se puede usar en otras sesiones.

Sintaxis de declarar CTE (expresión de tabla común): -

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

Tomemos un ejemplo:

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'')

Creé dos tablas para el empleado y el Departamento e inserté 5 filas en cada tabla. Ahora me gustaría unirme a estas tablas y crear un conjunto de resultados temporales para usarlo más.

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

Vamos a tomar cada línea de la declaración una por una y entender.

Para definir CTE escribimos la cláusula "with", luego le damos un nombre a la expresión de la tabla, aquí he dado el nombre de "CTE_Example"

Luego escribimos "Como" y adjuntamos nuestro código en dos corchetes (---), podemos unir varias tablas en los corchetes adjuntos.

En la última línea, he usado "Seleccionar * de CTE_Example", estamos refiriendo la expresión de tabla común en la última línea de código, así que podemos decir que es como una vista, donde estamos definiendo y usando la vista en un solo lote y CTE no se almacenan en la base de datos como un objeto permanente. Pero se comporta como una vista. podemos realizar una declaración de eliminación y actualización en CTE y eso tendrá un impacto directo en la tabla a la que se hace referencia que se están utilizando en CTE. Tomemos un ejemplo para entender este hecho.

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

En la declaración anterior, estamos eliminando una fila de CTE_Example y eliminará los datos de la tabla a la que se hace referencia "DEPT" que se está utilizando en el CTE.

Answer 4

Los uso para separar consultas complejas, especialmente combinaciones complejas y sub-consultas. Me parece que los estoy usando cada vez más como ''pseudo-views'' para ayudarme a entender el propósito de la consulta.

Mi única queja sobre ellos es que no pueden ser reutilizados. Por ejemplo, puedo tener un proceso almacenado con dos instrucciones de actualización que podrían usar el mismo CTE. Pero el ''alcance'' del CTE es la primera consulta solamente.

El problema es que los "ejemplos simples" probablemente no necesiten CTE.

Aún así, muy útil.

Answer 5

Tal vez sea más significativo pensar en un CTE como un sustituto de una vista utilizada para una sola consulta. Pero no requiere la sobrecarga, los metadatos o la persistencia de una vista formal. Muy útil cuando necesitas:

• Crea una consulta recursiva.
• Use los resultados de CTE más de una vez en su consulta.
• Promueva la claridad en su consulta reduciendo trozos grandes de subconsultas idénticas.
• Habilite la agrupación por columna derivada en el conjunto de resultados del CTE

Aquí hay un ejemplo de cortar y pegar para jugar:

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;

Disfrutar

Answer 6

Un ejemplo, si necesita hacer referencia / unirse al mismo conjunto de datos varias veces, puede hacerlo definiendo un CTE. Por lo tanto, puede ser una forma de reutilización de código.

Un ejemplo de autorreferencia es la recursión: consultas recursivas con CTE

Por emocionantes definiciones de Microsoft tomadas de Books Online:

Un CTE se puede usar para:

• Crea una consulta recursiva. Para obtener más información, vea Consultas recursivas con expresiones de tabla comunes.

• Sustituir por una vista cuando no se requiere el uso general de una vista; es decir, no es necesario almacenar la definición en metadatos.

• Habilite la agrupación por una columna derivada de una subselección escalar o una función que no sea determinista o que tenga acceso externo.

• Haga referencia a la tabla resultante varias veces en la misma declaración.

Answer 7

Uno de los escenarios que encontré útil para usar CTE es cuando desea obtener filas de datos DISTINCT basadas en una o más columnas, pero devuelve todas las columnas en la tabla. Con una consulta estándar, primero debe volcar los valores distintos en una tabla temporal e intentar volver a unirlos a la tabla original para recuperar el resto de las columnas o puede escribir una consulta de partición extremadamente compleja que pueda devolver los resultados en una ejecución, pero con la mayor probabilidad, será ilegible y causará problemas de rendimiento.

Pero al usar CTE (como respondió Tim Schmelter en Seleccionar la primera instancia de un registro )

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

Como puede ver, esto es mucho más fácil de leer y mantener. Y en comparación con otras consultas, es mucho mejor en rendimiento.

Answer 8

 ;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;

prueba esto