mysql with recursive 递归调用
时间: 2024-01-25 12:34:57 浏览: 30
MySQL的WITH RECURSIVE语法可以实现递归查询。具体用法如下所示:
```sqlWITH RECURSIVE cte_name (col_name [, col_name ...) AS (
subquery [, cte_name (col_name [, col_name ...) AS (subquery)]
...
)
SELECT * FROM cte_name [ORDER BY ...];
```
其中,cte_name是递归查询的名称,col_name是要查询的列名,subquery是子查询的语句。
如果需要向上递归查询,可以参考以下示例:
```sqlWITH RECURSIVE t1 AS (
SELECT * FROM course_category p WHERE id = '1-1-1'
UNION ALL SELECT t.* FROM course_category t INNER JOIN t1 ON t1.parentid = t.id)
SELECT * FROM t1 ORDER BY t1.id, t1.orderby;
```
这个例子中,通过递归查询父级课程分类,从'id'为'1-1-1'开始,一直向上查询,直到顶级分类。
如果需要实现课程分类的查询,可以参考以下示例:
```sqlWITH RECURSIVE t1 AS (
SELECT * FROM course_category p WHERE id = '1'
UNION ALL SELECT t.* FROM course_category t INNER JOIN t1 ON t1.id = t.parentid)
SELECT * FROM t1 ORDER BY t1.id, t1.orderby;
```
这个例子中,通过递归查询子级课程分类,从'id'为'1'开始,一直向下查询,直到最底层分类。
请根据实际需求,适当修改语句中的表名、列名和条件。