with生成子查询

1、创建数据

CREATE TABLE tb_mysql
(
    `id` Int8,
    `name` String,
    `age` Int8
)
ENGINE = Memory

insert into tb_mysql values(1,'zss',23),(2,'lss',33),(3,'ww',44);

select * from tb_mysql

2、测试

with可以定义子查询 ,但是一定还要注意的是,子查询只能返回一行结果 ,否则会跑出异常，如下：

 WITH
     (
   select * from tb_mysql
     ) AS sub
 SELECT
     *,sub
 FROM  tb_mysql

 当把代码改成如下时则成功：

 WITH
     (
   select * from tb_mysql where id=1
     ) AS sub
 SELECT
     *,sub
 FROM  tb_mysql