A sentence of SQL complete dynamic hierarchical query

When you use the ActiveReports Report Designer to design a report template in a recent project, you encounter a multilevel classification challenge: You need to summarize the sales amounts for all sales and subordinates in a department, because the hierarchy of subordinate levels is uncertain, so the way of stitching sub-queries is obviously not satisfying the requirements. After some experimentation, it is easy to solve this problem by using the CTE (Common Table Expression)!

Examples: There are the following sectoral tables

and employee table

If you want to query all North West District employees (including Northwest, Xian, Lanzhou), as shown:

How is the CTE implemented?

Talk is cheap. Show me the Code

--The following code uses the SQLite 3.18.0 test via with    [Depts] ([dept_id]) as (        SELECT [d].[ DEPT_ID]        from   [dept] [D]               joins [Employees] [E] on [d].[ DEPT_ID] = [E]. [dept_id]        WHERE  [e].[ Emp_name] = ' Northwest-manager '        UNION all        SELECT [d].[ DEPT_ID]        from   [dept] [D]               joins [depts] [s] on [d].[ PARENT_ID] = [s]. [dept_id]    ) Select *from   [Employees]where  [dept_id] In (SELECT [dept_id]       from   [depts]);

Perhaps some students on the CTE (Common Table Expression) is not familiar with, here briefly, interested students can Google or Baidu, introduce a lot (here with SQLite example):

I prefer to call a CTE (Common table Expression) as a "common table variable" rather than a "common expression", because the CTE is more of a result set (either iterative or incessantly generation) from the behavior and usage scenarios, for subsequent statements to use (query, INSERT, Delete or update), as the above example is a typical iterative traversal of tree structure data.

Advantages of CTE:

• Recursive features make it possible to use temporary tables, stored procedures to complete the logic, through the SQL can be done, especially for some trees or graphs of the data model
• Because it is a temporary result set within a session, there is no need to go to the display of the declaration or destroy
• Improved readability of the rewritten SQL statement (see To modify)
• The possibility of optimizing the execution plan for the database engine (this is not certain, it needs to be based on the implementation of the specific CTE), optimizing the execution plan, and naturally increasing the performance

To better illustrate the ability of the CTE, here are two examples (transferred from the SQLite website document)

Mandelbrot collection (Mandelbrot set)

--The following code uses the SQLite 3.18.0 test through with the RECURSIVE  Xaxis (x) as (VALUES ( -2.0) UNION all SELECT x+0.05 from Xaxis WHERE x<1.2) ,  YAxis (y) as (VALUES ( -1.0) UNION all SELECT y+0.1 from YAxis WHERE y<1.0),  m (ITER, CX, CY, X, y) as (    Sele CT 0, X, y, 0.0, 0.0 from Xaxis, YAxis    UNION all    SELECT iter+1, CX, CY, X*x-y*y + CX, 2.0*x*y + cy from M      wher E (x*x + y*y) < 4.0 and iter<28  ),  m2 (ITER, CX, CY) as (    SELECT Max (ITER), CX, CY from M GROUP by CX, CY  ),  A (t) as (    SELECT group_concat (substr ('. +*# ', 1+min (iter/7,4), 1), ') from     m2 Group by Cy  ) SELECT Group_concat (RTrim (t), X ' 0a ') from A;

Post-run results, such as: (Using SQLite Expert Personal 4.2 x64)

Sudoku problem (Sudoku)

Suppose there is a similar problem:

--The following code uses the SQLite 3.18.0 test through with RECURSIVE  input (sud) as (    VALUES (' 53..7....6..195....98....6.8...6...34..8.3..17...2...6.6....28....419..5....8..79 ')  ),  digits (z, LP) as (    VALUES (' 1 ', 1)    UNION all SELECT    CAST (lp+1 as TEXT), lp+1 from digits WHERE lp<9  ),  x (S, Ind) as (    SELECT sud, InStr (su D, '. ') From input    UNION all    SELECT      substr (S, 1, ind-1) | | z | | substr (S, ind+1),      InStr (substr (S, 1, ind-1) | | Z | | SUBSTR (S, ind+1), '. ')     from x, digits as Z    where ind>0 and not      EXISTS (            SELECT 1 from              digits as LP             WHERE z.z = substr (S, ((ind-1)/9) *9 + LP, 1)                or z.z = substr (S, ((ind-1)%9) + (lp-1) *9 + 1, 1)                OR z.z = substr (S, ((((ind-1)/3)% 3) * 3                        + ((ind-1)/27) * 27 + LP                        + ((lp-1)/3) * 6, 1)         )  SELECT s from x WHERE ind=0;

A sentence of SQL complete dynamic hierarchical query