场景:
先说下我遇到的使用场景:查询一组统计数据,在此基础上做个日均,求均值,由于字段多,直接在原先基础上改动比较大,想的是直接使用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的优点:
一个是重复的查询内容可以只调用一次,一是增加了代码的阅读性
如果遇到多个同一子查询时,采用此方式效率比一般子查询高,而且代码结构更清晰易懂。