MYSQL公共表达式的应用

---

一、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;

执行结果如下