MySQL with语句小结

缺乏、安全感 2022-11-19 04:23 226阅读 0赞

备注:测试数据库版本为MySQL 8.0

这个blog我们来聊聊MySQL的with语句  
对于逻辑复杂的sql，with可以大大减少临时表的数量，提升代码的可读性、可维护性

MySQL 8.0终于开始支持with语句了，对于复杂查询，可以不用写那么多的临时表了。

如需要scott用户下建表及录入数据语句，可参考:  
[scott建表及录入数据sql脚本][scott_sql]

语句结构:

with subquery_name1 as (subquery_body1),
            subquery_name2 as (subquery_body2)
    ...
    select * from subquery_name1 a, subquery_name2 b
    where a.col = b.col
    ...

优势  
– 代码模块化  
– 代码可读性增强  
– 相同查询唯一化

## 一.提升代码的可读性和可维护性 ##

需求:求每个部门的平均工资,以及剔除薪资低于1000的实习人员之后的平均工资

-- 求每个部门的平均工资,以及剔除薪资低于1000的实习人员之后的平均工资
    -- 主查询的from后面跟了2个临时表，程序可读性不佳
    select d.deptno, tmp1.avg_sal avg_sal1, tmp2.avg_sal avg_sal2
      from dept d
      left join (select e1.deptno, round(avg(ifnull(e1.sal, 0)), 2) avg_sal
                   from emp e1
                  group by e1.deptno) tmp1
        on d.deptno = tmp1.deptno
      left join (select e1.deptno, round(avg(ifnull(e1.sal, 0)), 2) avg_sal
                   from emp e1
                  where e1.sal > 1000
                  group by e1.deptno) tmp2
        on d.deptno = tmp2.deptno;
        
        
    -- 求每个部门的平均工资,以及剔除薪资低于1000的实习人员之后的平均工资
    -- 2个临时表的定时语句通过with封装成子查询了，程序可读性增强
    with tmp1 as
     (select e1.deptno, round(avg(ifnull(e1.sal, 0)), 2) avg_sal
        from emp e1
       group by e1.deptno),
    tmp2 as
     (select e1.deptno, round(avg(ifnull(e1.sal, 0)), 2) avg_sal
        from emp e1
       where e1.sal > 1000
       group by e1.deptno)
    select d.deptno, tmp1.avg_sal avg_sal1, tmp2.avg_sal avg_sal2
      from dept d
      left join tmp1
        on d.deptno = tmp1.deptno
      left join tmp2
        on d.deptno = tmp2.deptno;

mysql> -- 求每个部门的平均工资,以及剔除薪资低于1000的实习人员之后的平均工资
    mysql> -- 主查询的from后面跟了2个临时表，程序可读性不佳
    mysql> 
    
    select d.deptno, tmp1.avg_sal avg_sal1, tmp2.avg_sal avg_sal2
       from dept d
       left join (select e1.deptno, round(avg(ifnull(e1.sal, 0)), 2) avg_sal
                    from emp e1
                   group by e1.deptno) tmp1
         on d.deptno = tmp1.deptno
       left join (select e1.deptno, round(avg(ifnull(e1.sal, 0)), 2) avg_sal
                    from emp e1
                   where e1.sal > 1000
                   group by e1.deptno) tmp2
         on d.deptno = tmp2.deptno;
    
    +--------+----------+----------+
    | deptno | avg_sal1 | avg_sal2 |
    +--------+----------+----------+
    |     10 |  2916.67 |  2916.67 |
    |     20 |  2175.00 |  2518.75 |
    |     30 |  1566.67 |  1690.00 |
    |     40 |     NULL |     NULL |
    +--------+----------+----------+
    4 rows in set (0.00 sec)
    
    mysql>
    mysql>
    mysql> -- 求每个部门的平均工资,以及剔除薪资低于1000的实习人员之后的平均工资
    mysql> -- 2个临时表的定时语句通过with封装成子查询了，程序可读性增强
    mysql> 
    
    with tmp1 as
      (select e1.deptno, round(avg(ifnull(e1.sal, 0)), 2) avg_sal
         from emp e1
        group by e1.deptno),
     tmp2 as
      (select e1.deptno, round(avg(ifnull(e1.sal, 0)), 2) avg_sal
         from emp e1
        where e1.sal > 1000
        group by e1.deptno)
     select d.deptno, tmp1.avg_sal avg_sal1, tmp2.avg_sal avg_sal2
       from dept d
       left join tmp1
         on d.deptno = tmp1.deptno
       left join tmp2
         on d.deptno = tmp2.deptno;
    
    +--------+----------+----------+
    | deptno | avg_sal1 | avg_sal2 |
    +--------+----------+----------+
    |     10 |  2916.67 |  2916.67 |
    |     20 |  2175.00 |  2518.75 |
    |     30 |  1566.67 |  1690.00 |
    |     40 |     NULL |     NULL |
    +--------+----------+----------+
    4 rows in set (0.00 sec)
    
    mysql>

## 二.with递归 ##

用with递归构造数列

-- 用with递归构造1-10的数据
    with RECURSIVE c(n) as
     (select 1   union all select n + 1 from c where n < 10)
    select n from c;

-- 用with递归构造1-10的数据
    mysql> 
    
    with RECURSIVE c(n) as
      (select 1   union all select n + 1 from c where n < 10)
     select n from c;
    
    +------+
    | n    |
    +------+
    |    1 |
    |    2 |
    |    3 |
    |    4 |
    |    5 |
    |    6 |
    |    7 |
    |    8 |
    |    9 |
    |   10 |
    +------+
    10 rows in set (0.00 sec)

with RECURSIVE emp2(ename,empno,mgr,lvl)
      as
       (select ename, empno, mgr, 1 lvl from emp where mgr is null
        union all
        select emp.ename, emp.empno, emp.mgr, e2.lvl+1
          from emp, emp2 e2
         where emp.mgr = e2.empno
       )
    select lvl,
          concat(repeat('**',lvl),ename) nm
      from emp2
     order by lvl,ename
    ;

mysql> 
    
    with RECURSIVE emp2(ename,empno,mgr,lvl)
        as
         (select ename, empno, mgr, 1 lvl from emp where mgr is null
          union all
          select emp.ename, emp.empno, emp.mgr, e2.lvl+1
            from emp, emp2 e2
           where emp.mgr = e2.empno
         )
      select lvl,
            concat(repeat('**',lvl),ename) nm
        from emp2
       order by lvl,ename
      ;
    
    +------+---------------+
    | lvl  | nm            |
    +------+---------------+
    |    1 | **KING        |
    |    2 | ****BLAKE     |
    |    2 | ****CLARK     |
    |    2 | ****JONES     |
    |    3 | ******ALLEN   |
    |    3 | ******FORD    |
    |    3 | ******JAMES   |
    |    3 | ******MARTIN  |
    |    3 | ******MILLER  |
    |    3 | ******SCOTT   |
    |    3 | ******TURNER  |
    |    3 | ******WARD    |
    |    4 | ********ADAMS |
    |    4 | ********SMITH |
    +------+---------------+
    14 rows in set (0.00 sec)

转载地址：[https://www.modb.pro/db/25773][https_www.modb.pro_db_25773]

[scott_sql]: https://www.jianshu.com/p/532fe68924cb
[https_www.modb.pro_db_25773]: https://www.modb.pro/db/25773