with函数使用

with rollup函数使用

WITH ROLLUP 可以实现在分组统计数据基础上再进行相同的统计（SUM,AVG,COUNT…）。

例如我们将以上的数据表按名字进行分组，再统计每个人登录的次数：

SELECT 
    name, 
    SUM(signin) as signin_count 
FROM  employee_tbl 
GROUP BY name WITH ROLLUP;
+--------+--------------+
| name   | signin_count |
+--------+--------------+
| 小丽 |            2 |
| 小明 |            7 |
| 小王 |            7 |
| NULL   |           16 |
+--------+--------------+
4 rows in set (0.00 sec)

with函数

mysql版本在8.0之前不能使用with的写法。是用来定义一个SQL片断

写法一：
with t as (
select * from consumer
)
select * from t

写法二：
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;

该语句的作用是在, 大量的报表查询时, 使用 with as 可以提取出大量的子查询, 更加简洁.

示例1

WITH tmp AS (
	SELECT
		DATE_FORMAT( submit_time, '%Y%m' ) AS submit_month,
		count( question_id ) AS month_q_cnt 
	FROM
		practice_record 
	WHERE
		YEAR ( submit_time )= 2021 
	GROUP BY
		submit_month 
	ORDER BY
		submit_month 
	) 
	SELECT
	submit_month,
	month_q_cnt,
	round( month_q_cnt / 
          datediff( concat( submit_month, '01' )+ INTERVAL 1 MONTH, 
                   concat( submit_month, '01' )),3) AS avg_day_q_cnt 
	FROM tmp UNION
	( 
     SELECT 
     '2021汇总' AS submit_month, 
     sum( month_q_cnt ) AS month_q_cnt, 
     round( sum( month_q_cnt )/ 31, 3 ) AS avg_day_q_cnt 
     FROM tmp 
    );

入门实战(3.平均活跃天数和月活人数)：

https://blog.csdn.net/weixin_44464850/article/details/124547372?spm=1001.2014.3001.5501

示例2

CREATE TABLE tb(id VARCHAR(3), pid VARCHAR(3), name VARCHAR(64));
 
INSERT INTO tb VALUES('002', 0, '浙江省');
INSERT INTO tb VALUES('001', 0, '广东省');
INSERT INTO tb VALUES('003', '002', '衢州市');
INSERT INTO tb VALUES('004', '002', '杭州市');
INSERT INTO tb VALUES('005', '002', '湖州市');
INSERT INTO tb VALUES('006', '002', '嘉兴市');
INSERT INTO tb VALUES('007', '002', '宁波市');
INSERT INTO tb VALUES('008', '002', '绍兴市');
INSERT INTO tb VALUES('009', '002', '台州市');
INSERT INTO tb VALUES('010', '002', '温州市');
INSERT INTO tb VALUES('011', '002', '丽水市');
INSERT INTO tb VALUES('012', '002', '金华市');
INSERT INTO tb VALUES('013', '002', '舟山市');
INSERT INTO tb VALUES('014', '004', '上城区');
INSERT INTO tb VALUES('015', '004', '下城区');
INSERT INTO tb VALUES('016', '004', '拱墅区');
INSERT INTO tb VALUES('017', '004', '余杭区');
INSERT INTO tb VALUES('018', '011', '金东区');
INSERT INTO tb VALUES('019', '001', '广州市');
INSERT INTO tb VALUES('020', '001', '深圳市');
 
WITH RECURSIVE cte AS (
 SELECT id,name 
    FROM tb WHERE id='002' -- cte的集合
 UNION ALL
 SELECT k.id, CONCAT(c.name,'->',k.name) AS name
    FROM tb k 
    INNER JOIN cte c 
    ON c.id = k.pid -- recursive 递归拼接tb表数据
) SELECT * FROM cte;

官方文档

官方第一个示例，可以看出该查询语句创建了cte1,cte2,cte3,cte4这4个临时表，后面的临时表依赖前面的临时表数据。
最后一行为最终查询结果，实际ct4因为ct3结果包含3行数据，但是使用MAX,MIN得到一行结果。

WITH cte1(txt) AS (SELECT "This "),
     cte2(txt) AS (SELECT CONCAT(cte1.txt,"is a ") FROM cte1),
     cte3(txt) AS (SELECT "nice query" UNION
                   SELECT "query that rocks" UNION
                   SELECT "query"),
     cte4(txt) AS (SELECT concat(cte2.txt, cte3.txt) FROM cte2, cte3)
SELECT MAX(txt), MIN(txt) FROM cte4;

+----------------------------+----------------------+
| MAX(txt)                   | MIN(txt)             |
+----------------------------+----------------------+
| This is a query that rocks | This is a nice query |
+----------------------------+----------------------+
1 row in set (0,00 sec)

官方第二个示例是递归的用法，

WITH RECURSIVE cte_name AS
(
  SELECT ...      <-- specifies initial set
  UNION ALL
  SELECT ...      <-- specifies how to derive new rows
)

在你的SELECT, INSERT, UPDATE, DELETE语句中，或者在任何SELECT子查询中。

让我们浏览第一个示例，生成从1到10的整数:

WITH recursive my_cte AS
(
  SELECT 1 AS n
  UNION ALL
  SELECT 1+n FROM my_cte WHERE n<10
)
SELECT * FROM my_cte;

+------+
| n    |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
|    6 |
|    7 |
|    8 |
|    9 |
|   10 |
+------+
10 rows in set (0,00 sec)

让我们以1比10为例，将其减少到1比6以节省屏幕空间，使用my_cte(n)语法为列命名，并使用my_cte的结果创建一个表:

CREATE TABLE numbers
  WITH RECURSIVE my_cte(n) AS
  (
    SELECT 1
    UNION ALL
    SELECT 1+n FROM my_cte WHERE n<6
  )
  SELECT * FROM my_cte;
  
  
  SELECT * FROM numbers;
+------+
| n    |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
|    6 |
+------+

也可以在INSERT(和REPLACE)中使用:

INSERT INTO numbers
WITH RECURSIVE my_cte(n) AS
(
  SELECT 1
  UNION ALL
  SELECT 1+n FROM my_cte WHERE n<6
)
SELECT * FROM my_cte;



SELECT * FROM numbers;
+------+
| n    |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
|    6 |
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
|    6 |
+------+
12 rows in set (0,00 sec)

在UPDATE(单表和多表)中:

-- 更新
WITH RECURSIVE my_cte(n) AS
(
  SELECT 1
  UNION ALL
  SELECT 1+n FROM my_cte WHERE n<6
)
UPDATE numbers, my_cte
# Change to 0...
SET numbers.n=0
# ... the numbers which are squares, i.e. 1 and 4
WHERE numbers.n=my_cte.n*my_cte.n;


SELECT * FROM numbers;
+------+
| n    |
+------+
|    0 |
|    2 |
|    3 |
|    0 |
|    5 |
|    6 |
|    0 |
|    2 |
|    3 |
|    0 |
|    5 |
|    6 |
+------+

在类似的DELETE中，CTE定义在子查询本身中，而不是在DELETE前面:

DELETE FROM numbers 
WHERE numbers.n >
  (
    WITH RECURSIVE my_cte(n) AS
    (
      SELECT 1
      UNION ALL
      SELECT 1+n FROM my_cte WHERE n<6
    )SELECT * FROM my_cte
    # Half the average is 3.5/2=1.75
    SELECT AVG(n)/2 FROM my_cte -- 获取1到6的平均数/2
  );