SQL Server 2012 Query - Query

Question

SQL Server 2012 Query - Query

Navigation

#1 by (1 votes)

1

I'm having a hard time. In the query below, you're bringing it that way.

Tarefa_Inclusao      Tarefa
167909               167920

SELECT TarIDInclusao [Tarefa_Inclusao],tarid [Tarefa] FROM Tarefa WHERE TarID = 167920.

In this query is another task. What I want is the following: I want it in the above query to perform a handle so that the task_inclusion field instead of 167909, come 167907 same as the query below.

Tarefa_Inclusao      Tarefa
167907               167909

SELECT TarIDInclusao [Tarefa_Inclusao],tarid [Tarefa] FROM Tarefa WHERE TarID = 167909

sql sql-server query

asked by anonymous 26.09.2017 / 16:02

1 answer

Tie "for" inside another "for" (nested repetition) Get PHP Modal Window Data

score 1 · Accepted Answer

Use a recursive search to find the primary record for the desired task:

WITH historico AS (
  SELECT t.taridinclusao AS tarefa_inclusao,
         t.tarid AS tarefa,
         1 AS nivel
    FROM tarefa t
   WHERE t.tarid = 167920
   UNION ALL
  SELECT t.taridinclusao AS tarefa_inclusao,
         h.tarid AS tarefa, -- Pega do historico para garantir que o primeiro valor será mostrado
         h.nivel + 1 AS nivel
    FROM historico h
         INNER JOIN tarefa t ON t.tarid = h.taridinclusao
   WHERE h.taridinclusao IS NOT NULL
)
SELECT TOP(1) h.*
  FROM historico h
 ORDER BY h.nivel DESC
OPTION(MAXRECURSION 0);

The first SELECT within WITH will define the anchoring of the records, that is, the record from which we will start, in this case the task of which we have the number. After that, we will always reference the historico itself to check the taridinclusao registry for it.

WITH

A CTE (common table expression) provides the significant advantage of being able to self-reference, thus creating a recursive CTE. A recursive CTE is one in which an initial CTE is executed repeatedly to return subsets of data until the complete result is obtained.