一、mysql公共表达式
mysql的公共表达式跟select字句一样,不同的是公共表达式需要定义并且可以重复使用,而select字句一般都是现用现写,第二次想用则需要再次去写。公共表达式可以理解为对select字句的定义
二、使用实例
1.引入
代码如下(示例):
with cte as (select title, class_id
from imc_course where study_cnt > 200)
select * from cte
union all
select * from cte
order by title;
执行结果如下
2.生成序列
with recursive test as (
select 1 as n
union all
select 1+n from test where n<10
)
select * from test;
执行结果如下
3.递归查询回复信息
with recursive reply(quest_id, quest_title, user_id, replyid, path) as (
select quest_id, quest_title, user_id, replyid, cast(quest_id as char(200)) as path
from imc_question where course_id = 59 and replyid = 0
union all
select a.quest_id, a.quest_title, a.user_id, a.replyid, concat(b.path, '>>', a.quest_id) as path
from imc_question a
join reply b on a.replyid = b.quest_id
)
select * from reply;
执行结果如下