with用法（oracle和mysql均支持，mysql版本8以上）

场景：

        先说下我遇到的使用场景：查询一组统计数据，在此基础上做个日均，求均值，由于字段多，直接在原先基础上改动比较大，想的是直接使用union连接一条均值不就行了，看下图

SELECT
	to_char( to_date( tjrq, 'yyyymmdd' ), 'yyyy"年"mm"月"dd"日"' ) tjrq,
	nvl( yjjj, 0 ) yjjj,
	nvl( qyxf, 0 ) qyxf,
	nvl( dgmhs, 0 ) dgmhs 
FROM
	(
        SELECT sjly,tjrq,sum( ztrs ) ztrs 
        FROM
	        ( SELECT zrq, sjly, tjrq, min( ztrs ) ztrs FROM SKMTC_RYSJ_TJ GROUP BY zrq,             
              sjly, tjrq ) 
        GROUP BY sjly,tjrq 
	) 
pivot (sum( ztrs ) FOR sjly IN ( '应检尽检' AS yjjj, '区域协防' AS qyxf, '大规模核酸检测'         
     AS dgmhs )) 
WHERE
	tjrq BETWEEN '20220814' 
	AND '20220831'

union

select '日均',round(avg(yjjj)), round(avg(qyxf)), round(avg(dgmhs)) from 
(
	SELECT
	    row_number () over ( ORDER BY tjrq DESC ) AS xh,
	    to_char( to_date( tjrq, 'yyyymmdd' ), 'yyyy"年"mm"月"dd"日"' ) tjrq,
	    nvl( yjjj, 0 ) yjjj,
	    nvl( qyxf, 0 ) qyxf,
	    nvl( dgmhs, 0 ) dgmhs 
    FROM
	(
        SELECT sjly,tjrq,sum( ztrs ) ztrs 
        FROM
	    ( SELECT zrq, sjly, tjrq, min( ztrs ) ztrs FROM SKMTC_RYSJ_TJ GROUP BY zrq, sjly,         
          tjrq ) 
        GROUP BY sjly,tjrq 
	) 
    pivot (sum( ztrs ) FOR sjly IN ( '应检尽检' AS yjjj, '区域协防' AS qyxf, '大规模核酸检 
           测' AS dgmhs )) 
WHERE
	tjrq BETWEEN '20220814' 
	AND '20220831'
) 

 

 

但此时sql重复了，union前的sql在求均值被重复使用，sql冗余，可读性差，对此sql进行改造就用到了with

语法：

♡单个别名

WITH <别名-后续使用的表名> AS (查询的sql)  SELECT <查询字段> FROM <别名>;

♡多个别名

WITH <别名1>  AS (sql1),<别名2>  AS (sql2)
SELECT <查询字段>  FROM <别名1>, <别名2>  WHERE <条件>;

上面例子中union前的改造：temp是创建的公共临时表 as()内是查询的sql，就是数据来源

                后面操作查询就直接从临时表里查询即可

with temp as  (
	SELECT
		to_char( to_date( tjrq, 'yyyymmdd' ), 'yyyy"年"mm"月"dd"日"' ) tjrq,
		nvl( yjjj, 0 ) yjjj,
		nvl( qyxf, 0 ) qyxf,
		nvl( dgmhs, 0 ) dgmhs 
	FROM
	    (SELECT sjly,tjrq,sum( ztrs ) ztrs 
         FROM
			( SELECT zrq, sjly, tjrq, min( ztrs ) ztrs FROM SKMTC_RYSJ_TJ GROUP BY zrq,         
              sjly, tjrq ) 
		 GROUP BY sjly,tjrq 
		) 
        pivot 
        (sum( ztrs ) FOR sjly IN ( '应检尽检' AS yjjj, '区域协防' AS qyxf, '大规模核酸检测' 
         AS dgmhs )) 
	WHERE tjrq BETWEEN '20220814' AND '20220831'
) select * from  temp 

针对如上结果只需在temp后union即可

with temp as  (
	SELECT
		to_char( to_date( tjrq, 'yyyymmdd' ), 'yyyy"年"mm"月"dd"日"' ) tjrq,
		nvl( yjjj, 0 ) yjjj,
		nvl( qyxf, 0 ) qyxf,
		nvl( dgmhs, 0 ) dgmhs 
	FROM
	    (SELECT sjly,tjrq,sum( ztrs ) ztrs 
         FROM
			( SELECT zrq, sjly, tjrq, min( ztrs ) ztrs FROM SKMTC_RYSJ_TJ GROUP BY zrq,         
              sjly, tjrq ) 
		 GROUP BY sjly,tjrq 
		) 
        pivot 
        (sum( ztrs ) FOR sjly IN ( '应检尽检' AS yjjj, '区域协防' AS qyxf, '大规模核酸检测' 
         AS dgmhs )) 
	WHERE tjrq BETWEEN '20220814' AND '20220831'
) select * from  temp 
   union select '日均',round(avg(yjjj)), round(avg(qyxf)), round(avg(dgmhs)) from temp

查询结果跟上面一致

 with的优点:
    

一个是重复的查询内容可以只调用一次，一是增加了代码的阅读性

 如果遇到多个同一子查询时，采用此方式效率比一般子查询高，而且代码结构更清晰易懂。