Recent Movable case Project use ActiveReports Report designer When designing a report template , There is a problem of multi-level classification : It is necessary to summarize the sales amount of a certain department and its subordinate departments , Because the hierarchy of subordinates is uncertain , So the way of splicing sub query obviously can not meet the requirements , After some experiments , Take advantage of CTE(Common Table Expression) It's easy to solve this problem !

give an example : There is a list of departments as follows

And employee list

If you want to check all the employees in the Northwest ( Including northwest 、 Xi'an 、 lanzhou ), As shown in the figure below :

How to use CTE How to achieve it ?

Talk is cheap. Show me the code

-- The following code USES SQLite 3.18.0 The test passed 
WITH
[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] = ' The northwest - The 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]);

Maybe some students are right CTE(Common Table Expression) Not familiar with , Let's talk about it briefly , Interested students can google Or Baidu , Introduce a lot ( Here we use SQLite give an example ):

I prefer to weigh CTE(Common Table Expression) by “ Common table variables ” instead of “ Common expression ”, Because in terms of behavior and usage scenarios ,CTE More often than not, it's the production of ( Iterate or not ) Result set , For subsequent statements ( Inquire about 、 Insert 、 Delete or update ), For example, the above example is a typical example of iteratively traversing tree structured data .

CTE The advantages of :

  • Recursion makes it necessary to use temporary tables 、 Logic that can only be completed by stored procedures , adopt SQL You can do it , Especially for the data model of some trees or graphs
  • Because it's a temporary result set in the session , There is no need to display the statement or destroy
  • After the rewrite SQL Sentence readability is improved ( Only when you can see clearly can you revise it )
  • Give the database engine the possibility to optimize the execution plan ( This is not for sure , It needs to be specific CTE The realization of ), Optimized execution plan , Naturally, performance can go up

For better explanation CTE The ability of , Here are two examples ( from SQLite Official document )

Mandelbrot assembly (Mandelbrot set)

-- The following code USES SQLite 3.18.0 The test passed 
WITH 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 (
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;

Results after operation , Here's the picture :( Use SQLite Expert Personal 4.2 x64)

Sudoku question (Sudoku)

Suppose there is a problem similar to the figure below :

-- The following code USES SQLite 3.18.0 The test passed 
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)
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 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;

Execution results ( The number in the result is the answer in the corresponding grid )

attach :SQLite in CTE(WITH keyword ) Grammar illustration :

WITH

cte-table-name

Select-stmt:

summary

CTE It's a powerful tool to solve some specific problems , But understanding and proper use is the premise , In deciding to put some of the existing SQL Refactor to CTE Before , Make sure you have a clear understanding of the existing sentences and have a clear understanding of CTE Enough learning !Good Luck~~~

The attachment Use of SQL Script

Related reading :

【 Report benefits broadcast 】100 More than one set of report templates are free to download

Novice counter attack ! Remember my first system development

Rookie is how to complete the system development in three days ?

A word of SQL Complete the dynamic hierarchical query more related articles

  1. LINQ to SQL Build query conditions dynamically at run time

    During data query , We often encounter the need to dynamically build query conditions . Use LINQ Realizing this requirement may be more difficult than before SQL The sentence is more troublesome . This paper introduces 3 This is a method of dynamically building query conditions at runtime . The examples in this article all realize the same function , from Nor ...

  2. SQL Server A word of Sql Find out all the table structures

    -- A word of Sql Find out all the table structures SELECT Table name = Case When A.colorder=1 Then D.name Else '' End, Table description = Case When A.colo ...

  3. The persistence layer MyBatis: Second articles : dynamic SQL And Multi-table query

    MyBatis Beginner to master complete CRUD UserDaoImpl To write UserDao Corresponding UserDaoMapper.xml add to UserDao Test cases for To write UserDao Test cases for Solve the database field name ...

  4. MyBatis structure sql The table name and field name are passed in dynamically

    Today's projects need to use dynamic table names , Find this article , Close test available It was used mybatis For a long time , But I feel like I use basic functions , quite a lot mybatis relative ibatis I haven't used any of my new functions . Such as its built-in annotation function and so on ...

  5. mybatis+maven+ The father and the son carry on many modules crud And dynamic condition query

    Use IDEA establish maven project ,File→New→Project→maven→Next→ Fill in GroupId( example :com.zyl) and ArtifactId(mybatis-demo-parent)→Nex ...

  6. SQL Server Dynamic row rollover ( Parameterized table name 、 Group columns 、 Row to column fields 、 field value )

    One . The contents of this article (Contents) The contents of this article (Contents) background (Contexts) Implementation code (SQL Codes) Method 1 : Use splices SQL, Static column fields : Method 2 : Use splices SQL, ...

  7. mybatis Use records ( Two ) Dynamic splicing query conditions

    2016-12-16 When reading the project code , In the project xml It is found in the document as follows : SELECT student_user_id FROM tbr_student_class WHERE 1=1 <if ...

  8. Use Expression Tree Build dynamic LINQ Inquire about

    This article introduces an interesting topic , It's also often asked : How to build dynamic LINQ Inquire about ? The so-called dynamic , The main meaning is that the query conditions can be randomly combined , Dynamic addition , Instead of writing in a fixed way . This is very useful in many system development processes . I'll give it to ...

  9. SQL Server DBA Daily query view _ Database object view

    1. database use master; exec sp_helpdb 1.1 Query database size 1.2 Query database status use msdb select name, user_access_desc, -- User visit ...

Random recommendation

  1. docker build ros-indigo-arm Cross compile environment

    ROS Running environment :ARM ubuntu14.04 + ROS indigo stay arm Compile... In environment ros Applications , Very slow , I can't stand it , Try to x86 Build a machine docker+ros Cross compile environment . Cross compiling environment ...

  2. rsyslog Configuration error resolution

    During configuration , see /var/log/meassage Error message : action '*' treated as ':omusrmsg:*' - please use ':omusrmsg:*' synt ...

  3. doctrine2 What the hell is it

    Previous and recent projects used Doctrine, Because someone else built it , I don't know much about , When I started to do it again recently, I found that I had a problem , So I took a look doctrine course , This article is to add my own understanding doctrine Tutorial documentation notes . D ...

  4. Sort of Hill sort of Hill sort of Hill sort of Hill sort of Hill sort of Hill sort of Hill sort of Hill sort of Hill sort of Hill sort of Hill sort of Hill sort of Hill sort of Hill sort of Hill sort of Hill sort of Hill sort of Hill sort (shell sort)

    Preface This blog is based on brother Wu's blog , Click its blog address to get in , There are many good blogs in it , My sorting algorithms all come from this : I will not elaborate on some data structure concepts , There are detailed explanations in brother Wu's blog , And I'm just blogging from ...

  5. ISO9001、ISO14001、OHSAS18000 What do you mean

    ISO9001 yes ISO9000 Family standard is one of the core standards of quality management system .ISO9000 The family standard is the international organization for standardization (ISO) stay 1994 The concept put forward in , Refer to “ from ISO/TC176( ISO quality management and quality ...

  6. 【 D3.js Selection set and data explanation — 5 】 Handle the application of templates

    stay [ Selection sets and data - 4] In the article , Introduced a update.enter.exit Processing template for , This template is very common , This article will explain how to use it through an example . 1. Templates Review the template mentioned in the previous chapter . // After binding data , ...

  7. Yii Convert objects to arrays

    Objects that will be looked up from the database , Convert to array , And to set the attribute key name , be used ArrayHelper::toArray $posts = Post::find()->limit(10)->all(); $d ...

  8. 【 turn 】Ubuntu The structures, SVN Environmental Science -Apache

    Original website :http://www.cnblogs.com/candle806/archive/2012/12/20/2826280.html Description of the environment :ubuntu server 12.04  / sv ...

  9. python3.x perform post The request is wrong “POST data should be bytes or an iterable of bytes...” Solutions for

    Use python3.5.1 perform post When asked , Has been an error "POST data should be bytes or an iterable of bytes. It cannot be of ...

  10. P1744 Buy special offers Answer key ( Explain graph theory )

    The super elementary problem of graph theory ( The template questions ) The shortest path of the template problem What's the picture ?( Symbols on white paper ?) For a possession n An undirected connected graph with two vertices , It must have more sides than n-1 strip . If you choose n-1 side , So that the undirected graph is still connected , By n Two vertices and this n- ...