一个少妇满足三个老头在线观看,锕锕锕锕锕锕锕好痛视频,亚洲人成网站在线播放2019

<center id="pt2l2"></center>
<tr id="pt2l2"></tr>
  • <big id="pt2l2"></big>

        MySQL?with語句講解

        備注:測試數據庫版本為MySQL 8.0

        今天我們來聊聊MySQL的with語句
        對于邏輯復雜的sql,with可以大大減少臨時表的數量,提升代碼的可讀性、可維護性

        MySQL 8.0終于開始支持with語句了,對于復雜查詢,可以不用寫那么多的臨時表了。

        如需要scott用戶下建表及錄入數據語句,可參考:
        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遞歸構造級聯關系

        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)

        到此這篇關于MySQL?with語句小結的文章就介紹到這了,更多相關mysql?with語句內容請搜索以前的文章或繼續瀏覽下面的相關文章希望大家以后多多支持!

        若文章對您有幫助,幫忙點個贊!

        0
        0
        發布時間 2022-11-14 06:08:36
        0 條回復(回復會通過微信通知作者)
        點擊加載更多評論
        登錄 后再進行評論
        (微信掃碼即可登錄,無需注冊)
        一个少妇满足三个老头在线观看,锕锕锕锕锕锕锕好痛视频,亚洲人成网站在线播放2019