怎么用 CTE 呢?我们用小巧数据库 SQLite,它就支持!别看他体积不大,却也能支持最新 SQL99 的 with 语句,例子如下。
WITH w1( id, parentId, name) AS
( SELECT
category.id,
category.parentId,
category.name
FROM
category
WHERE
id = 1
UNION ALL
SELECT
category.id,
category.parentId,
category.name
FROM
category JOIN w1 ON category.parentId= w1.id
)
SELECT * FROM w1;
其中 WHERE id = 1 是那个父节点之 id,你可以改为你的变量。简单说,递归 CTE 最少包含两个查询(也被称为成员)。第一个查询为定点成员,定点成员只是一个返回有效表的查询,用于递归的基础或定位点。第二个查询被称为递归成员,使该查询称为递归成员的是对 CTE 名称的递归引用是触发。在逻辑上可以将 CTE 名称的内部应用理解为前一个查询的结果集。递归查询没有显式的递归终止条件,只有当第二个递归查询返回空结果集或是超出了递归次数的最大限制时才停止递归。递归次数上限的方法是使用 MAXRECURION。
相应地给出查找所有父节点的方法(获取祖先 Ancestors,就是把 id 和 parentId 反过来)
WITH w1( id, parentId, name, level) AS
( SELECT
id,
parentId,
name,
0 AS level
FROM
category
WHERE
id = 6
UNION ALL
SELECT
category.id,
category.parentId,
category.name ,
level + 1
FROM
category JOIN w1 ON category.id= w1.parentId
)
SELECT * FROM w1;
无奈的 MySQL
SQLite ok 了,而 MySQL 呢?
在另一边厢,大家都爱用的 MySQL 却无视 with 语句,官网博客上明确说明是压根不支持,十分不方便,明明可以很简单事情为什么不能用呢?——而且 MySQL 也好像没有计划在将来的新版本中添加 with 的 cte 功能。于是大家想出了很多办法。其实不就是一个递归程序么——应该不难——写函数或者存储过程总该行吧?没错,的确如此,——写递归不是问题,问题是用 SQL 写就是个问题——还是那句话,“隔行如隔山”,虽然有点夸张的说法,但我想既懂数据库又懂各种数据库方言写法(存储过程)的人应该不是很多吧~,——不细究了,反正就是代码帖来贴去呗~
SELECT t1.name AS lev1, t2.name as lev2, t3.name as lev3, t4.name as lev4
FROM category AS t1
LEFT JOIN category AS t2 ON t2.parentId = t1.id
LEFT JOIN category AS t3 ON t3.parentId = t2.id
LEFT JOIN category AS t4 ON t4.parentId = t3.id
WHERE t1.id= 1
相应地给出查找所有父节点的方法(获取祖先 Ancestors,就是把 id 和 parentId 反过来)
SELECT t1.name AS lev1, t2.name as lev2, t3.name as lev3, t4.name as lev4
FROM category AS t1
LEFT JOIN category AS t2 ON t2.id= t1.parentId
LEFT JOIN category AS t3 ON t3.id= t2.parentId
LEFT JOIN category AS t4 ON t4.id= t3.parentId
WHERE t1.id= 10
优化版本
但是生成的结果和第一个例子相比起来有点奇怪,而且不好给 Java 用,——那就再找找其他例子
SELECT
p1.id,
p1.name,
p1.parentId as parentId,
p2.parentId as parent2_id,
p3.parentId as parent3_id,
p4.parentId as parent4_id,
p5.parentId as parent5_id,
p6.parentId as parent6_id
FROM category p1
LEFT JOIN category p2 on p2.id = p1.parentId
LEFT JOIN category p3 on p3.id = p2.parentId
LEFT JOIN category p4 on p4.id = p3.parentId
LEFT JOIN category p5 on p5.id = p4.parentId
LEFT JOIN category p6 on p6.id = p5.parentId
WHERE 1 IN (p1.parentId,
p2.parentId,
p3.parentId,
p4.parentId,
p5.parentId,
p6.parentId)
ORDER BY 1, 2, 3, 4, 5, 6, 7;
这个总算像点样子了,结果是这样子的。
相应地给出查找所有父节点的方法(获取祖先 Ancestors,就是把 id 和 parentId 反过来, 还有改改 IN 里面的字段名)
SELECT
p1.id,
p1.name,
p1.parentId as parentId,
p2.parentId as parent2_id,
p3.parentId as parent3_id
FROM category p1
LEFT JOIN category p2 on p2.parentId = p1.id
LEFT JOIN category p3 on p3.parentId = p2.id
WHERE 9 IN (p1.id,
p2.id,
p3.id)
ORDER BY 1, 2, 3;
这样就很通用啦~无论你 SQLite 还是 MySQL。