A cascade query for solving multilevel classification using CTE in SQLite

When you use the ActiveReports Report Designer to design a report template in a recent movable grid project , you encounter a multilevel classification challenge: You need to summarize the sales amounts for all sales and subordinates in a department. Because the level of subordinates is not certain, so by stitching sub-query way is obviously not meet the requirements, after some experiments, using the CTE (Common Table Expression) very easy to solve the problem!

Examples: There are the following sectoral tables

650) this.width=650; "Src=" http://images2015.cnblogs.com/blog/139239/201704/139239-20170424110210319-109514451. PNG "style=" border:0px; "/>

and employee table

650) this.width=650; "Src=" http://images2015.cnblogs.com/blog/139239/201704/139239-20170424110218490-1500413472. PNG "style=" border:0px; "/>

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

650) this.width=650; "Src=" http://images2015.cnblogs.com/blog/139239/201704/139239-20170424110226584-995996188. PNG "style=" border:0px; "/>

How is the CTE implemented?

Talk is cheap. Show me the Code

-The following code passed WITH using SQLite 3.18.0 test
     [depts] ([dept_id]) AS (SELECT [d]. [dept_id]
         FROM [dept] [d]
                JOIN [employees] [e] ON [d]. [Dept_id] = [e]. [Dept_id]
         WHERE [e]. [Emp_name] = ‘Northwest-Manager’
         UNION ALL
         SELECT [d]. [Dept_id]
         FROM [dept] [d]
                JOIN [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 passed WITH RECURSIVE using SQLite 3.18.0 test
   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 (SELECT 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
      WHERE (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)

650) this.width=650; "Src=" http://images2015.cnblogs.com/blog/139239/201704/139239-20170424110516819-2114551280. PNG "style=" border:0px; "/>

Sudoku problem (Sudoku)

Suppose there is a similar problem:

650) this.width=650; "Src=" http://images2015.cnblogs.com/blog/139239/201704/139239-20170424110532537-2050799675. PNG "style=" border:0px; "/>

-The following code passed WITH RECURSIVE using SQLite 3.18.0 test
   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 (sud, ‘.‘) 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 zz = substr (s, ((ind-1) / 9) * 9 + lp, 1) OR zz = substr (s, ((ind-1)% 9) + (lp-1) * 9 + 1, 1) OR zz = substr (s, (((ind-1) / 3)% 3) * 3
                         + ((ind-1) / 27) * 27 + lp + ((lp-1) / 3) * 6, 1)
          )
   SELECT s FROM x WHERE ind = 0; 

Execution result (the number in the result is the answer in the corresponding lattice)

650) this.width=650; "Src=" http://images2015.cnblogs.com/blog/139239/201704/139239-20170424110613928-1307210909. PNG "style=" border:0px; "/>

Attached: a CTE (with keyword) syntax diagram in SQLite:

With

650) this.width=650; "Src=" http://images2015.cnblogs.com/blog/139239/201704/139239-20170424110623569-798660279. GIF "style=" border:0px; "/>

Cte-table-name

650) this.width=650; "Src=" http://images2015.cnblogs.com/blog/139239/201704/139239-20170424110631225-2120357525. GIF "style=" border:0px; "/>

SELECT-STMT:

650) this.width=650; "Src=" http://images2015.cnblogs.com/blog/139239/201704/139239-20170424110646553-378105355. GIF "style=" border:0px; "/>

Summary

A CTE is a powerful tool for solving specific problems, but understanding and proper use are prerequisites, ensuring a clear understanding of existing statements and sufficient learning of the CTE before deciding to refactor some of the existing SQL into a CTE! Good luck~~~

attachments : SQL scripts used

This article is from the "Grape City Control Technology Team Blog" blog, declined to reprint!

A cascade query for solving multilevel classification using CTE in SQLite