原理:使用公用表表明式的递归查询

序言:开辟常用的关系型数据库MySQL,mssql,postgre,Oracle,简单的增删改查的SQL语句都与行业内部SQL包容,那个不用讲,那么对于迭代查询(不严俊的叫法:递归查询)每一个数据库都不相像,对王宛平规SQL语句支持最佳的是mssql和postgre,这几个不需多讲,大家只讲讲单表情形下的postgre如何通过迭代查询获得有层级关系的数据。

威尼斯城真人赌钱网站 1威尼斯城真人赌钱网站 2代码

MySQL查询分为内接连查询和外接连查询,他们的区分在于:内再三再四查询的多少个代表对等关系,依据典型实行相称;外接连是以某一个表为主,两多个表依据条件进行关联。外连接分为左外连接、右外连接和全外连接。本文器重介绍各外连接的切磋,以至怎样落实全外连接,并举个例子。

传送门:

 

  1 create table Employee
  2 (
  3 Id INT IDENTITY(1,1) PRIMARY KEY, 
  4 [Name] varchar(30) null, 
  5 JobTitle varchar(30) null, 
  6 Manager int null
  7 )
  8 
  9 insert Employee
 10 select ‘incf’,     ‘IT Director’,null union all
 11 select ‘inc3’,     ‘Finance Director’,null union all
 12 select ‘geovindu’,     ‘assces’,1 union all
 13 select ‘du’,     ‘assces’,1 union all
 14 select ‘fa’,’account’,2 union all
 15 select ‘d’,’account’,2  
 16 
 17 WITH OrganisationChart (Id, [Name], JobTitle, Manager) AS
 18 (
 19       SELECT
 20             Id, [Name], JobTitle, Manager
 21       FROM dbo.Employee
 22       WHERE
 23             Manager IS NULL
 24       UNION ALL
 25       SELECT emp.Id, emp.[Name], emp.JobTitle, emp.Manager
 26       FROM dbo.Employee emp
 27       INNER JOIN OrganisationChart ON
 28             emp.Manager = OrganisationChart.Id
 29 )
 30 SELECT * FROM OrganisationChart
 31 
 32 
 33 WITH OrganisationChart (Id,  [Name], JobTitle, [Level], Manager) AS
 34 (
 35       SELECT
 36             Id, [Name], JobTitle, 0, Manager
 37       FROM  dbo.Employee
 38       WHERE
 39            Manager IS NULL
 40       UNION ALL
 41       SELECT emp.Id, emp.[Name], emp.JobTitle, [Level] + 1, emp.Manager
 42       FROM dbo.Employee emp
 43       INNER JOIN OrganisationChart ON
 44             emp.Manager = OrganisationChart.Id
 45 )
 46 SELECT * FROM OrganisationChart
 47 ORDER BY [Level]
 48 
 49 
 50 WITH OrganisationChart (Id, [Name], JobTitle, [Level], Manager, [Root]) AS
 51 (
 52       SELECT  Id, [Name], JobTitle, 0, Manager, Id
 53       FROM  dbo.Employee
 54       WHERE  Manager IS NULL
 55       UNION ALL
 56       SELECT  emp.Id,emp.[Name],emp.JobTitle,[Level] + 1,emp.Manager,[Root]
 57       FROM dbo.Employee emp
 58       INNER JOIN OrganisationChart ON
 59             emp.Manager = OrganisationChart.Id
 60 )
 61 SELECT * FROM OrganisationChart
 62 –WHERE [Name] = ‘incf’
 63 WHERE [Root] = 1
 64 
 65  
 66 
 67 –示例数据库
 68 
 69 /*
 70 递归查询对于同三个表父子关系的酌量提供了比相当的大的便利,这些示例使用了SQL server 二零零六中的递归查询,使用的表是CarParts,那么些表存款和储蓄了意气风发辆汽车的兼具零件以至协会,part为构件单位,subpart为子零零部件,Qty为数量。
 71 
 72 具体示比如下:
 73 
 74 */ 
 75 CREATE TABLE CarParts
 76 
 77 (
 78 
 79 CarID INT NOT NULL,
 80 
 81 Part VARCHAR(15),
 82 
 83 SubPart VARCHAR(15),
 84 
 85 Qty INT
 86 
 87 )
 88 
 89 GO
 90 
 91 INSERT CarParts VALUES (1, ‘Body’, ‘Door’, 4)
 92 
 93 INSERT CarParts VALUES (1, ‘Body’, ‘Trunk Lid’, 1)
 94 
 95 INSERT CarParts VALUES (1, ‘Body’, ‘Car Hood’, 1)
 96 
 97 INSERT CarParts VALUES (1, ‘Door’, ‘Handle’, 1)
 98 
 99 INSERT CarParts VALUES (1, ‘Door’, ‘Lock’, 1)
100 
101 INSERT CarParts VALUES (1, ‘Door’, ‘Window’, 1)
102 
103 INSERT CarParts VALUES (1, ‘Body’, ‘Rivets’, 1000)
104 
105 INSERT CarParts VALUES (1, ‘Door’, ‘Rivets’, 100)
106 
107 INSERT CarParts VALUES (1, ‘Door’, ‘Mirror’, 1)
108 
109 INSERT CarParts VALUES (1, ‘Mirror’, ‘small_Mirror’, 4)
110 
111 GO
112 
113 SELECT * FROM CarParts
114 
115 GO
116 
117 /*
118 
119 意气风发辆小车供给各类构件的多寡
120 
121 1个Body 需要4个Door
122 
123 1个Door 需要1个Mirror
124 
125 那么
126 
127 1个body需要4个Mirror
128 
129 构造很简短吗
130 
131 */
132 
133 WITH CarPartsCTE(SubPart, Qty)
134 
135 AS
136 
137 (
138 
139 — 一定成员 (AM卡塔尔(قطر‎:
140 
141 — SELECT查询无需参谋CarPartsCTE
142 
143 — 递归从那边领头
144 
145 SELECT SubPart, Qty
146 
147 FROM CarParts
148 
149 WHERE Part = ‘Body’
150 
151 UNION ALL
152 
153 — 递归成员 (RM卡塔尔国:
154 
155 — SELECT查询参谋CarPartsCTE
156 
157 — 使用现存数量往下风流浪漫层开展
158 
159 SELECT CarParts.SubPart, CarPartsCTE.Qty * CarParts.Qty
160 
161 FROM CarPartsCTE
162 
163 INNER JOIN CarParts ON CarPartsCTE.SubPart = CarParts.Part
164 
165 WHERE CarParts.CarID = 1
166 
167 )
168 
169 SELECT SubPart,Qty AS TotalNUM
170 
171 FROM CarPartsCTE
172 
173 /*
174 
175 注意看最下层的small_Mirror 坐落于 表最终的岗位,
176 
177 由此能够看来改递归不是始于就开展递归查询而是在1层完全张开后在依照该层打开下生龙活虎层不是深度优先的递归
178 
179 */
180 
181 drop table CarParts 
182 
183  
184 
185 WITH DirectReports(groupid, member, EmployeeLevel,type) AS
186 
187 (
188 
189 SELECT groupid, member, 0,type AS EmployeeLevel
190 
191 FROM groupinfo
192 
193 WHERE groupid = ‘finance_company’
194 
195 UNION ALL
196 
197 SELECT e.groupid, e.member, EmployeeLevel + 1,e.type
198 
199 FROM groupinfo e
200 
201 INNER JOIN DirectReports d
202 
203 ON e.groupid = d.member
204 
205 )
206 
207 SELECT b.nickname,groupid, member, EmployeeLevel,type
208 
209 FROM DirectReports,userbasicinfo b
210 
211 where DirectReports.member=b.id
212 
213 and type = 1 
214 
215 
216 USE AdventureWorks;
217 GO
218 WITH DirReps(ManagerID, DirectReports) AS 
219 (
220     SELECT ManagerID, COUNT(*) 
221     FROM HumanResources.Employee AS e
222     WHERE ManagerID IS NOT NULL
223     GROUP BY ManagerID
224 )
225 SELECT ManagerID, DirectReports 
226 FROM DirReps 
227 ORDER BY ManagerID;
228 GO
229 
230 
231 WITH DirReps (Manager, DirectReports) AS 
232 (
233     SELECT ManagerID, COUNT(*) AS DirectReports
234     FROM HumanResources.Employee
235     GROUP BY ManagerID
236 ) 
237 SELECT AVG(DirectReports) AS [Average Number of Direct Reports]
238 FROM DirReps 
239 WHERE DirectReports>= 2 ;
240 GO
241 
242  
243 
244 
245 USE AdventureWorks;
246 GO
247 WITH Sales_CTE (SalesPersonID, NumberOfOrders, MaxDate)
248 AS
249 (
250     SELECT SalesPersonID, COUNT(*), MAX(OrderDate)
251     FROM Sales.SalesOrderHeader
252     GROUP BY SalesPersonID
253 )
254 SELECT E.EmployeeID, OS.NumberOfOrders, OS.MaxDate,
255     E.ManagerID, OM.NumberOfOrders, OM.MaxDate
256 FROM HumanResources.Employee AS E
257     JOIN Sales_CTE AS OS
258     ON E.EmployeeID = OS.SalesPersonID
259     LEFT OUTER JOIN Sales_CTE AS OM
260     ON E.ManagerID = OM.SalesPersonID
261 ORDER BY E.EmployeeID;
262 GO
263 
264 —管理員下的下屬
265 USE AdventureWorks;
266 GO
267 WITH DirectReports(ManagerID, EmployeeID, EmployeeLevel) AS 
268 (
269     SELECT ManagerID, EmployeeID, 0 AS EmployeeLevel
270     FROM HumanResources.Employee
271     WHERE ManagerID IS NULL
272     UNION ALL
273     SELECT e.ManagerID, e.EmployeeID, EmployeeLevel + 1
274     FROM HumanResources.Employee e
275         INNER JOIN DirectReports d
276         ON e.ManagerID = d.EmployeeID 
277 )
278 SELECT ManagerID, EmployeeID, EmployeeLevel 
279 FROM DirectReports 
280 WHERE EmployeeLevel <= 2 ;
281 GO
282 
283 USE AdventureWorks;
284 GO
285 WITH DirectReports(Name, Title, EmployeeID, EmployeeLevel, Sort)
286 AS (SELECT CONVERT(varchar(255), c.FirstName + ‘ ‘ + c.LastName),
287         e.Title,
288         e.EmployeeID,
289         1,
290         CONVERT(varchar(255), c.FirstName + ‘ ‘ + c.LastName)
291     FROM HumanResources.Employee AS e
292     JOIN Person.Contact AS c ON e.ContactID = c.ContactID 
293     WHERE e.ManagerID IS NULL
294     UNION ALL
295     SELECT CONVERT(varchar(255), REPLICATE (‘| ‘ , EmployeeLevel) +
296         c.FirstName + ‘ ‘ + c.LastName),
297         e.Title,
298         e.EmployeeID,
299         EmployeeLevel + 1,
300         CONVERT (varchar(255), RTRIM(Sort) + ‘| ‘ + FirstName + ‘ ‘ + 
301                  LastName)
302     FROM HumanResources.Employee as e
303     JOIN Person.Contact AS c ON e.ContactID = c.ContactID
304     JOIN DirectReports AS d ON e.ManagerID = d.EmployeeID
305     )
306 SELECT EmployeeID, Name, Title, EmployeeLevel
307 FROM DirectReports 
308 ORDER BY Sort;
309 GO
310 
311 USE AdventureWorks;
312 GO
313 –Creates an infinite loop
314 WITH cte (EmployeeID, ManagerID, Title) as
315 (
316     SELECT EmployeeID, ManagerID, Title
317     FROM HumanResources.Employee
318     WHERE ManagerID IS NOT NULL
319   UNION ALL
320     SELECT cte.EmployeeID, cte.ManagerID, cte.Title
321     FROM cte 
322     JOIN  HumanResources.Employee AS e 
323         ON cte.ManagerID = e.EmployeeID
324 )
325 –Uses MAXRECURSION to limit the recursive levels to 2
326 SELECT EmployeeID, ManagerID, Title
327 FROM cte
328 OPTION (MAXRECURSION 2);
329 GO
330 
331  
332 
333 USE AdventureWorks;
334 GO
335 WITH cte (EmployeeID, ManagerID, Title)
336 AS
337 (
338     SELECT EmployeeID, ManagerID, Title
339     FROM HumanResources.Employee
340     WHERE ManagerID IS NOT NULL
341   UNION ALL
342     SELECT  e.EmployeeID, e.ManagerID, e.Title
343     FROM HumanResources.Employee AS e
344     JOIN cte ON e.ManagerID = cte.EmployeeID
345 )
346 SELECT EmployeeID, ManagerID, Title
347 FROM cte;
348 GO
349 
350 
351 USE AdventureWorks;
352 GO
353 WITH Parts(AssemblyID, ComponentID, PerAssemblyQty, EndDate, ComponentLevel) AS
354 (
355     SELECT b.ProductAssemblyID, b.ComponentID, b.PerAssemblyQty,
356         b.EndDate, 0 AS ComponentLevel
357     FROM Production.BillOfMaterials AS b
358     WHERE b.ProductAssemblyID = 800
359           AND b.EndDate IS NULL
360     UNION ALL
361     SELECT bom.ProductAssemblyID, bom.ComponentID, p.PerAssemblyQty,
362         bom.EndDate, ComponentLevel + 1
363     FROM Production.BillOfMaterials AS bom 
364         INNER JOIN Parts AS p
365         ON bom.ProductAssemblyID = p.ComponentID
366         AND bom.EndDate IS NULL
367 )
368 SELECT AssemblyID, ComponentID, Name, PerAssemblyQty, EndDate,
369         ComponentLevel 
370 FROM Parts AS p
371     INNER JOIN Production.Product AS pr
372     ON p.ComponentID = pr.ProductID
373 ORDER BY ComponentLevel, AssemblyID, ComponentID;
374 GO
375 

左外连接

左外连接以右手表为底工,依照标准,将侧边表从属到左边表,语法:SELECT *
FROM A LEFT JOIN B ON
condition。几何图形关系如下图,即查询结果集除了A表全体数据外,还包蕴餍足条件的B表数据:

威尼斯城真人赌钱网站 3

主题材料:钦赐四个父级,查询出该父级下边包车型大巴持有节点。

朝气蓬勃、表构造比方

 

 

右外连接

右外连接以侧边表为底子,依照标准,将左臂表从属到右原子钟,语法:SELECT *
FROM A 福特ExplorerIGHT JOIN B ON
condition。几何图形关系如下图,即查询结果集除了B表全部数据外,还蕴涵满意条件的A表数据:

威尼斯城真人赌钱网站 4

举例表达:存在表

MENU表

 

ID VARCHAR2(32)     N   sys_guid()    节点id
FENXID VARCHAR2(32)      Y                       分项id
FENXMC VARCHAEvoque2(100卡塔尔    Y                       分项名称
FUJID VARCHAR2(32)      Y                       父级id
FUJMC VARCHAQX562(100State of Qatar    Y                       父级名称

 

全外连接

全外连接是除了能够基于法则卓殊拿到的数据,还蕴涵左右两表中都不合营的数量(暗中认可应该为null),应用全外连接的情状相近都有贰个牵连左右两表的主线。几何关系如下图所示,对应A和B的并集(去重):

威尼斯城真人赌钱网站 5

但不幸的是MySQL不支持全外连接,那在急需全外连接查询的动静下,怎么着落到实处吗?最广大的是左连接与右连接合并。

威尼斯城真人赌钱网站 6

二、关联关系

 

子级菜单的FUJID等于父级菜单的FENXID,根节点未有FUJID,每种节点都有一个节点ID用于方便单个节点增加和删除改查

即((根节点,根节点无FUJID卡塔尔国ID,FENXID–>FUJID,FENXID(具备子级的父节点,该FUJD等于上顶级的FENXID卡塔尔–>FUJID,FENXID–>FUJID,FENXID–>FUJID,FENXID….就那样类推)

 

实例

品种中留存那样的现象:某项职责task具备2种分裂的状态todo和done,分别存款和储蓄在todolist和donelist表中,职责存款和储蓄在task表中,以往急需总括每一个task的已管理和未管理景况。首先先到了全外连接,那么什么样促成呢?

譬释迦牟尼佛讲完毕表构造如下:

威尼斯城真人赌钱网站 7

实现多种艺术:

ComponentI为子,PP_ComponentID为父。

三、迭代查询

1、左连接,右连接,合併;(需保持多个结实集合构同样)

  • 第一是左连接:

    1 SELECT
    2 A.id AS Aid,
    3 B.id AS Bid,
    4 A.taskid tid
    5 FROM
    6 (
    7 SELECT
    8
    9 FROM
    10 todolist
    11 WHERE
    12 todolist.user = ‘张三’
    13 ) A
    14 LEFT JOIN (
    15 SELECT
    16

    17 FROM
    18 donelist
    19 WHERE
    20 donelist.user = ‘张三’
    21 ) B ON A.taskid = B.taskid

 查询结果:

威尼斯城真人赌钱网站 8

  • 附带是右连接(注意由于须求联合,故左右一连的结果集合构需风度翩翩致):

    1 SELECT
    2 A.id AS Aid,
    3 B.id AS Bid,
    4 A.taskid tid
    5 FROM
    6 (
    7 SELECT
    8
    9 FROM
    10 todolist
    11 WHERE
    12 todolist.user = ‘张三’
    13 ) A
    14 RIGHT JOIN (
    15 SELECT
    16

    17 FROM
    18 donelist
    19 WHERE
    20 donelist.user = ‘张三’
    21 ) B ON A.taskid = B.taskid

 查询结果:

威尼斯城真人赌钱网站 9

  • 最终举行统大器晚成,并与task表实行内接连:

    1 SELECT
    2 SUM(IF(Aid IS NOT NULL, 1, 0)) todo,
    3 SUM(IF(Bid IS NOT NULL, 1, 0)) done,
    4 task.name
    5 FROM
    6 (
    7 SELECT
    8 A.id AS Aid,
    9 B.id AS Bid,
    10 A.taskid tid
    11 FROM
    12 (
    13 SELECT
    14
    15 FROM
    16 todolist
    17 WHERE
    18 todolist.user = ‘张三’
    19 ) A
    20 LEFT JOIN (
    21 SELECT
    22

    23 FROM
    24 donelist
    25 WHERE
    26 donelist.user = ‘张三’
    27 ) B ON A.taskid = B.taskid
    28 UNION
    29 SELECT
    30 A.id AS Aid,
    31 B.id AS Bid,
    32 B.taskid tid
    33 FROM
    34 (
    35 SELECT
    36
    37 FROM
    38 todolist
    39 WHERE
    40 todolist.user = ‘张三’
    41 ) A
    42 RIGHT JOIN (
    43 SELECT
    44

    45 FROM
    46 donelist
    47 WHERE
    48 donelist.user = ‘张三’
    49 ) B ON A.taskid = B.taskid
    50 ) AS AB
    51 INNER JOIN task ON task.id = AB.tid
    52 GROUP BY
    53 task.name

 运营结果如下表,完毕全外连接:

威尼斯城真人赌钱网站 10

相关文章