使用Select WITH 子句简化复杂查询

长河的笔记 2020-09-07

3052

本文介绍如何使用WITH子句简化复杂的SQL，及如何用WITH提高SQL执行效率。
WITH子句在Oracle数据库中称为子查询分解(subquery factoring)。在ANSI 1999 SQL 标准中称为通用表达式（Common Table Expressions），所以在PG，MySQL等数据库中都有with子句，使用方法基本相同，本文仅以oracle数据库举例。

实验环境：

CentOS Linux release 7.6.1810 (Core)

Oracle Database 18c Express

使用select with子句的好处

使SQL的可读性更好，特别是查询中涉及很多张表和列时，使sql的结构层次更清晰。
with子句的查询结果可以被多次重复使用，而只被扫描一次，减少了数据库的读取操作，提高查询效率。
使用with子句可以很容易进行递归查询。

创建测试数据

-- DROP TABLE EMP PURGE;
-- DROP TABLE DEPT PURGE;


CREATE TABLE DEPT (
  DEPTNO NUMBER(2) CONSTRAINT PK_DEPT PRIMARY KEY,
  DNAME VARCHAR2(14),
  LOC VARCHAR2(13)
) ;


CREATE TABLE EMP (
  EMPNO NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY,
  ENAME VARCHAR2(10),
  JOB VARCHAR2(9),
  MGR NUMBER(4),
  HIREDATE DATE,
  SAL NUMBER(7,2),
  COMM NUMBER(7,2),
  DEPTNO NUMBER(2) CONSTRAINT FK_DEPTNO REFERENCES DEPT
);


INSERT INTO DEPT VALUES (10,'ACCOUNTING','NEW YORK');
INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS');
INSERT INTO DEPT VALUES (30,'SALES','CHICAGO');
INSERT INTO DEPT VALUES (40,'OPERATIONS','BOSTON');


INSERT INTO EMP VALUES (7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);
INSERT INTO EMP VALUES (7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);
INSERT INTO EMP VALUES (7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);
INSERT INTO EMP VALUES (7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);
INSERT INTO EMP VALUES (7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30);
INSERT INTO EMP VALUES (7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30);
INSERT INTO EMP VALUES (7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10);
INSERT INTO EMP VALUES (7788,'SCOTT','ANALYST',7566,to_date('13-JUL-87','dd-mm-rr')-85,3000,NULL,20);
INSERT INTO EMP VALUES (7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10);
INSERT INTO EMP VALUES (7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30);
INSERT INTO EMP VALUES (7876,'ADAMS','CLERK',7788,to_date('13-JUL-87', 'dd-mm-rr')-51,1100,NULL,20);
INSERT INTO EMP VALUES (7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30);
INSERT INTO EMP VALUES (7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20);
INSERT INTO EMP VALUES (7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10);
COMMIT;

例如我们想显示每个员工的姓名以及所在部门的人数。使用内联视图。

--不使用WITH子句，此sql写法仅做演示，可能不是最优化写法
SELECT e.ename AS employee_name,
       dc.dept_count AS emp_dept_count
FROM   emp e,
       (SELECT deptno, COUNT(*) AS dept_count
        FROM   emp
        GROUP BY deptno) dc
WHERE  e.deptno = dc.deptno;


--使用WITH子句
--将部门和部门人数的数据读取出来作为视图dept_count
--然后将员工表和dept_count视图作join,得出最终结果
WITH dept_count AS (
  SELECT deptno, COUNT(*) AS dept_count
  FROM   emp
  GROUP BY deptno)
SELECT e.ename AS employee_name,
       dc.dept_count AS emp_dept_count
FROM   emp e, dept_count dc
WHERE  e.deptno = dc.deptno;

如果我们还想显示每个员工的经理姓名和经理部门的人数呢？使用内联视图，看起来像这样。

--不使用WITH子句
SELECT e.ename AS employee_name,
       dc1.dept_count AS emp_dept_count,
       m.ename AS manager_name,
       dc2.dept_count AS mgr_dept_count
FROM   emp e,
       (SELECT deptno, COUNT(*) AS dept_count
             FROM   emp
             GROUP BY deptno) dc1,
       emp m,
       (SELECT deptno, COUNT(*) AS dept_count
        FROM   emp
        GROUP BY deptno) dc2
WHERE  e.deptno = dc1.deptno
AND    e.mgr = m.empno
AND    m.deptno = dc2.deptno;


--使用WITH子句
WITH dept_count AS (
  SELECT deptno, COUNT(*) AS dept_count
  FROM   emp
  GROUP BY deptno)
SELECT e.ename AS employee_name,
       dc1.dept_count AS emp_dept_count,
       m.ename AS manager_name,
       dc2.dept_count AS mgr_dept_count
FROM   emp e,
       dept_count dc1,
       emp m,
       dept_count dc2
WHERE  e.deptno = dc1.deptno
AND    e.mgr = m.empno
AND    m.deptno = dc2.deptno;

可以看到，我们不需要多次重新定义相同的子查询。相反，我们只使用在WITH子句中定义的查询名称，这样使查询更易于阅读。

如果WITH子句的内容足够复杂，则Oracle可能决定将子查询的结果解析为全局临时表。这可以使对子查询的多个引用更加有效。MATERIALIZE和INLINE优化器hint可用于影响决策。hint /*+ MATERIALIZE */ 告诉优化器将子查询解析为全局临时表，而INLINE提示告诉优化器以内联方式处理查询。

WITH dept_count AS (
  SELECT /*+ MATERIALIZE */ deptno, COUNT(*) AS dept_count
  FROM   emp
  GROUP BY deptno)
SELECT ...


WITH dept_count AS (
  SELECT /*+ INLINE */ deptno, COUNT(*) AS dept_count
  FROM   emp
  GROUP BY deptno)
SELECT ...

即便是没有重复的子查询，WITH子句也可以简化复杂的查询，例如以下示例列出了工资高于平均水平的部门，这个示例中，查询的主体非常简单，复杂的sql定义在WITH子句中。

WITH 
  dept_costs AS (
    SELECT dname, SUM(sal) dept_total
    FROM   emp e, dept d
    WHERE  e.deptno = d.deptno
    GROUP BY dname),
  avg_cost AS (
    SELECT SUM(dept_total)/COUNT(*) avg
    FROM   dept_costs)
SELECT *
FROM   dept_costs
WHERE  dept_total > (SELECT avg FROM avg_cost)
ORDER BY dname;

分享一个近期改写的sql, 作用是查询2020年的自动化处理的工单率，即IT运维工单自动化率=月度自动化工单数/月度总工单数。

--原始sql, 未进行分组，使用union连接多条sql结果, 执行时间33秒，需要对同一张表进行多次扫描，效率低。

SELECT ROUND( A1.A / A2.A, 4 ) , '2020-09' AS month
FROM (
SELECT count( * ) AS A
FROM WOI_WORKORDER
WHERE CCHR_FULFILLMENT_METHOD = 'Automatic' AND STATUS IN ( '0', '1', '2', '3', '4', '5', '8' ) AND ASGRP IN ( 'FG383-Excellent Operation Center', 'FG381-IT network service', 'FG321-EUC Support', 'FG322-Server', 'FG323-Middleware', 'FG324-Database', 'FG401-IT Area Infrastructure Service', 'FG64-Monitoring', 'FG688-Digital Application', 'FG188-Backup Service Operation Team', 'FG403-GSII NAS Storage' ) AND TO_CHAR( To_Date( '19700101', 'yyyymmdd' ) + NEXT_TARGET_DATE / 86400+To_Number ( Substr( Tz_Offset( Sessiontimezone ), 1, 3 )) / 24, 'YYYY-MM' ) LIKE '2020-09' ) A1 INNER JOIN (
SELECT count( * ) AS A
FROM WOI_WORKORDER
WHERE STATUS IN ( '0', '1', '2', '3', '4', '5', '8' ) AND ASGRP IN ( 'FG383-Excellent Operation Center', 'FG381-IT network service', 'FG321-EUC Support', 'FG322-Server', 'FG323-Middleware', 'FG324-Database', 'FG401-IT Area Infrastructure Service', 'FG64-Monitoring', 'FG688-Digital Application', 'FG188-Backup Service Operation Team', 'FG403-GSII NAS Storage' ) AND TO_CHAR( To_Date( '19700101', 'yyyymmdd' ) + NEXT_TARGET_DATE / 86400+ To_Number ( Substr( Tz_Offset( Sessiontimezone ), 1, 3 )) / 24, 'YYYY-MM' ) LIKE '2020-09' ) A2 ON 1 = 1 union
SELECT ROUND( A1.A / A2.A, 4 ) , '2020-08' AS month
FROM (
SELECT count( * ) AS A
FROM WOI_WORKORDER
WHERE CCHR_FULFILLMENT_METHOD = 'Automatic' AND STATUS IN ( '0', '1', '2', '3', '4', '5', '8' ) AND ASGRP IN ( 'FG383-Excellent Operation Center', 'FG381-IT network service', 'FG321-EUC Support', 'FG322-Server', 'FG323-Middleware', 'FG324-Database', 'FG401-IT Area Infrastructure Service', 'FG64-Monitoring', 'FG688-Digital Application', 'FG188-Backup Service Operation Team', 'FG403-GSII NAS Storage' ) AND TO_CHAR( To_Date( '19700101', 'yyyymmdd' ) + NEXT_TARGET_DATE / 86400+To_Number ( Substr( Tz_Offset( Sessiontimezone ), 1, 3 )) / 24, 'YYYY-MM' ) LIKE '2020-08' ) A1 INNER JOIN (
SELECT count( * ) AS A
FROM WOI_WORKORDER
WHERE STATUS IN ( '0', '1', '2', '3', '4', '5', '8' ) AND ASGRP IN ( 'FG383-Excellent Operation Center', 'FG381-IT network service', 'FG321-EUC Support', 'FG322-Server', 'FG323-Middleware', 'FG324-Database', 'FG401-IT Area Infrastructure Service', 'FG64-Monitoring', 'FG688-Digital Application', 'FG188-Backup Service Operation Team', 'FG403-GSII NAS Storage' ) AND TO_CHAR( To_Date( '19700101', 'yyyymmdd' ) + NEXT_TARGET_DATE / 86400+ To_Number ( Substr( Tz_Offset( Sessiontimezone ), 1, 3 )) / 24, 'YYYY-MM' ) LIKE '2020-08' ) A2 ON 1 = 1 union
SELECT ROUND( A1.A / A2.A, 4 ) , '2020-07' AS month
FROM (
SELECT count( * ) AS A
FROM WOI_WORKORDER
WHERE CCHR_FULFILLMENT_METHOD = 'Automatic' AND STATUS IN ( '0', '1', '2', '3', '4', '5', '8' ) AND ASGRP IN ( 'FG383-Excellent Operation Center', 'FG381-IT network service', 'FG321-EUC Support', 'FG322-Server', 'FG323-Middleware', 'FG324-Database', 'FG401-IT Area Infrastructure Service', 'FG64-Monitoring', 'FG688-Digital Application', 'FG188-Backup Service Operation Team', 'FG403-GSII NAS Storage' ) AND TO_CHAR( To_Date( '19700101', 'yyyymmdd' ) + NEXT_TARGET_DATE / 86400+To_Number ( Substr( Tz_Offset( Sessiontimezone ), 1, 3 )) / 24, 'YYYY-MM' ) LIKE '2020-07' ) A1 INNER JOIN (
SELECT count( * ) AS A
FROM WOI_WORKORDER
WHERE STATUS IN ( '0', '1', '2', '3', '4', '5', '8' ) AND ASGRP IN ( 'FG383-Excellent Operation Center', 'FG381-IT network service', 'FG321-EUC Support', 'FG322-Server', 'FG323-Middleware', 'FG324-Database', 'FG401-IT Area Infrastructure Service', 'FG64-Monitoring', 'FG688-Digital Application', 'FG188-Backup Service Operation Team', 'FG403-GSII NAS Storage' ) AND TO_CHAR( To_Date( '19700101', 'yyyymmdd' ) + NEXT_TARGET_DATE / 86400+ To_Number ( Substr( Tz_Offset( Sessiontimezone ), 1, 3 )) / 24, 'YYYY-MM' ) LIKE '2020-07' ) A2 ON 1 = 1 union
SELECT ROUND( A1.A / A2.A, 4 ) , '2020-06' AS month
FROM (
SELECT count( * ) AS A
FROM WOI_WORKORDER
WHERE CCHR_FULFILLMENT_METHOD = 'Automatic' AND STATUS IN ( '0', '1', '2', '3', '4', '5', '8' ) AND ASGRP IN ( 'FG383-Excellent Operation Center', 'FG381-IT network service', 'FG321-EUC Support', 'FG322-Server', 'FG323-Middleware', 'FG324-Database', 'FG401-IT Area Infrastructure Service', 'FG64-Monitoring', 'FG688-Digital Application', 'FG188-Backup Service Operation Team', 'FG403-GSII NAS Storage' ) AND TO_CHAR( To_Date( '19700101', 'yyyymmdd' ) + NEXT_TARGET_DATE / 86400+To_Number ( Substr( Tz_Offset( Sessiontimezone ), 1, 3 )) / 24, 'YYYY-MM' ) LIKE '2020-06' ) A1 INNER JOIN (
SELECT count( * ) AS A
FROM WOI_WORKORDER
WHERE STATUS IN ( '0', '1', '2', '3', '4', '5', '8' ) AND ASGRP IN ( 'FG383-Excellent Operation Center', 'FG381-IT network service', 'FG321-EUC Support', 'FG322-Server', 'FG323-Middleware', 'FG324-Database', 'FG401-IT Area Infrastructure Service', 'FG64-Monitoring', 'FG688-Digital Application', 'FG188-Backup Service Operation Team', 'FG403-GSII NAS Storage' ) AND TO_CHAR( To_Date( '19700101', 'yyyymmdd' ) + NEXT_TARGET_DATE / 86400+ To_Number ( Substr( Tz_Offset( Sessiontimezone ), 1, 3 )) / 24, 'YYYY-MM' ) LIKE '2020-06' ) A2 ON 1 = 1 union
SELECT ROUND( A1.A / A2.A, 4 ) , '2020-05' AS month
FROM (
SELECT count( * ) AS A
FROM WOI_WORKORDER
WHERE CCHR_FULFILLMENT_METHOD = 'Automatic' AND STATUS IN ( '0', '1', '2', '3', '4', '5', '8' ) AND ASGRP IN ( 'FG383-Excellent Operation Center', 'FG381-IT network service', 'FG321-EUC Support', 'FG322-Server', 'FG323-Middleware', 'FG324-Database', 'FG401-IT Area Infrastructure Service', 'FG64-Monitoring', 'FG688-Digital Application', 'FG188-Backup Service Operation Team', 'FG403-GSII NAS Storage' ) AND TO_CHAR( To_Date( '19700101', 'yyyymmdd' ) + NEXT_TARGET_DATE / 86400+To_Number ( Substr( Tz_Offset( Sessiontimezone ), 1, 3 )) / 24, 'YYYY-MM' ) LIKE '2020-05' ) A1 INNER JOIN (
SELECT count( * ) AS A
FROM WOI_WORKORDER
WHERE STATUS IN ( '0', '1', '2', '3', '4', '5', '8' ) AND ASGRP IN ( 'FG383-Excellent Operation Center', 'FG381-IT network service', 'FG321-EUC Support', 'FG322-Server', 'FG323-Middleware', 'FG324-Database', 'FG401-IT Area Infrastructure Service', 'FG64-Monitoring', 'FG688-Digital Application', 'FG188-Backup Service Operation Team', 'FG403-GSII NAS Storage' ) AND TO_CHAR( To_Date( '19700101', 'yyyymmdd' ) + NEXT_TARGET_DATE / 86400+ To_Number ( Substr( Tz_Offset( Sessiontimezone ), 1, 3 )) / 24, 'YYYY-MM' ) LIKE '2020-05' ) A2 ON 1 = 1 union
SELECT ROUND( A1.A / A2.A, 4 ) , '2020-04' AS month
FROM (
SELECT count( * ) AS A
FROM WOI_WORKORDER
WHERE CCHR_FULFILLMENT_METHOD = 'Automatic' AND STATUS IN ( '0', '1', '2', '3', '4', '5', '8' ) AND ASGRP IN ( 'FG383-Excellent Operation Center', 'FG381-IT network service', 'FG321-EUC Support', 'FG322-Server', 'FG323-Middleware', 'FG324-Database', 'FG401-IT Area Infrastructure Service', 'FG64-Monitoring', 'FG688-Digital Application', 'FG188-Backup Service Operation Team', 'FG403-GSII NAS Storage' ) AND TO_CHAR( To_Date( '19700101', 'yyyymmdd' ) + NEXT_TARGET_DATE / 86400+To_Number ( Substr( Tz_Offset( Sessiontimezone ), 1, 3 )) / 24, 'YYYY-MM' ) LIKE '2020-04' ) A1 INNER JOIN (
SELECT count( * ) AS A
FROM WOI_WORKORDER
WHERE STATUS IN ( '0', '1', '2', '3', '4', '5', '8' ) AND ASGRP IN ( 'FG383-Excellent Operation Center', 'FG381-IT network service', 'FG321-EUC Support', 'FG322-Server', 'FG323-Middleware', 'FG324-Database', 'FG401-IT Area Infrastructure Service', 'FG64-Monitoring', 'FG688-Digital Application', 'FG188-Backup Service Operation Team', 'FG403-GSII NAS Storage' ) AND TO_CHAR( To_Date( '19700101', 'yyyymmdd' ) + NEXT_TARGET_DATE / 86400+ To_Number ( Substr( Tz_Offset( Sessiontimezone ), 1, 3 )) / 24, 'YYYY-MM' ) LIKE '2020-04' ) A2 ON 1 = 1 union
SELECT ROUND( A1.A / A2.A, 4 ) , '2020-03' AS month
FROM (
SELECT count( * ) AS A
FROM WOI_WORKORDER
WHERE CCHR_FULFILLMENT_METHOD = 'Automatic' AND STATUS IN ( '0', '1', '2', '3', '4', '5', '8' ) AND ASGRP IN ( 'FG383-Excellent Operation Center', 'FG381-IT network service', 'FG321-EUC Support', 'FG322-Server', 'FG323-Middleware', 'FG324-Database', 'FG401-IT Area Infrastructure Service', 'FG64-Monitoring', 'FG688-Digital Application', 'FG188-Backup Service Operation Team', 'FG403-GSII NAS Storage' ) AND TO_CHAR( To_Date( '19700101', 'yyyymmdd' ) + NEXT_TARGET_DATE / 86400+To_Number ( Substr( Tz_Offset( Sessiontimezone ), 1, 3 )) / 24, 'YYYY-MM' ) LIKE '2020-03' ) A1 INNER JOIN (
SELECT count( * ) AS A
FROM WOI_WORKORDER
WHERE STATUS IN ( '0', '1', '2', '3', '4', '5', '8' ) AND ASGRP IN ( 'FG383-Excellent Operation Center', 'FG381-IT network service', 'FG321-EUC Support', 'FG322-Server', 'FG323-Middleware', 'FG324-Database', 'FG401-IT Area Infrastructure Service', 'FG64-Monitoring', 'FG688-Digital Application', 'FG188-Backup Service Operation Team', 'FG403-GSII NAS Storage' ) AND TO_CHAR( To_Date( '19700101', 'yyyymmdd' ) + NEXT_TARGET_DATE / 86400+ To_Number ( Substr( Tz_Offset( Sessiontimezone ), 1, 3 )) / 24, 'YYYY-MM' ) LIKE '2020-03' ) A2 ON 1 = 1 union
SELECT ROUND( A1.A / A2.A, 4 ) , '2020-02' AS month
FROM (
SELECT count( * ) AS A
FROM WOI_WORKORDER
WHERE CCHR_FULFILLMENT_METHOD = 'Automatic' AND STATUS IN ( '0', '1', '2', '3', '4', '5', '8' ) AND ASGRP IN ( 'FG383-Excellent Operation Center', 'FG381-IT network service', 'FG321-EUC Support', 'FG322-Server', 'FG323-Middleware', 'FG324-Database', 'FG401-IT Area Infrastructure Service', 'FG64-Monitoring', 'FG688-Digital Application', 'FG188-Backup Service Operation Team', 'FG403-GSII NAS Storage' ) AND TO_CHAR( To_Date( '19700101', 'yyyymmdd' ) + NEXT_TARGET_DATE / 86400+To_Number ( Substr( Tz_Offset( Sessiontimezone ), 1, 3 )) / 24, 'YYYY-MM' ) LIKE '2020-02' ) A1 INNER JOIN (
SELECT count( * ) AS A
FROM WOI_WORKORDER
WHERE STATUS IN ( '0', '1', '2', '3', '4', '5', '8' ) AND ASGRP IN ( 'FG383-Excellent Operation Center', 'FG381-IT network service', 'FG321-EUC Support', 'FG322-Server', 'FG323-Middleware', 'FG324-Database', 'FG401-IT Area Infrastructure Service', 'FG64-Monitoring', 'FG688-Digital Application', 'FG188-Backup Service Operation Team', 'FG403-GSII NAS Storage' ) AND TO_CHAR( To_Date( '19700101', 'yyyymmdd' ) + NEXT_TARGET_DATE / 86400+ To_Number ( Substr( Tz_Offset( Sessiontimezone ), 1, 3 )) / 24, 'YYYY-MM' ) LIKE '2020-02' ) A2 ON 1 = 1 union
SELECT ROUND( A1.A / A2.A, 4 ) , '2020-01' AS month
FROM (
SELECT count( * ) AS A
FROM WOI_WORKORDER
WHERE CCHR_FULFILLMENT_METHOD = 'Automatic' AND STATUS IN ( '0', '1', '2', '3', '4', '5', '8' ) AND ASGRP IN ( 'FG383-Excellent Operation Center', 'FG381-IT network service', 'FG321-EUC Support', 'FG322-Server', 'FG323-Middleware', 'FG324-Database', 'FG401-IT Area Infrastructure Service', 'FG64-Monitoring', 'FG688-Digital Application', 'FG188-Backup Service Operation Team', 'FG403-GSII NAS Storage' ) AND TO_CHAR( To_Date( '19700101', 'yyyymmdd' ) + NEXT_TARGET_DATE / 86400+To_Number ( Substr( Tz_Offset( Sessiontimezone ), 1, 3 )) / 24, 'YYYY-MM' ) LIKE '2020-01' ) A1 INNER JOIN (
SELECT count( * ) AS A
FROM WOI_WORKORDER
WHERE STATUS IN ( '0', '1', '2', '3', '4', '5', '8' ) AND ASGRP IN ( 'FG383-Excellent Operation Center', 'FG381-IT network service', 'FG321-EUC Support', 'FG322-Server', 'FG323-Middleware', 'FG324-Database', 'FG401-IT Area Infrastructure Service', 'FG64-Monitoring', 'FG688-Digital Application', 'FG188-Backup Service Operation Team', 'FG403-GSII NAS Storage' ) AND TO_CHAR( To_Date( '19700101', 'yyyymmdd' ) + NEXT_TARGET_DATE / 86400+ To_Number ( Substr( Tz_Offset( Sessiontimezone ), 1, 3 )) / 24, 'YYYY-MM' ) LIKE '2020-01' ) A2 ON 1 = 1 order by month;

改用WITH子句的方式，执行时间3秒，对该表只扫描一次，执行效率提高。

sql更简洁，可读性好。

with wo as
(
select target_date as datemonth,nvl(cchr_fulfillment_method,0) as cchr_fulfillment_method from t_workorder
        WHERE
            status IN ('0','1','2','3','4','5','8')
            AND asgrp IN (
                'Excellent Operation Center',
                'IT network service',
                'EUC Support',
                'Server',
                'Middleware',
                'Database',
                'IT Area Infrastructure Service',
                'Monitoring',
                'Digital Application',
                'Backup Service Operation Team',
                'GSII NAS Storage')
            and target_date >= to_date('2020-01','yyyy-mm')
),
auto_wo as
(
    select trunc(datemonth,'month') as mm,count(cchr_fulfillment_method) as nums from wo where cchr_fulfillment_method = 'Automatic' group by trunc(datemonth,'month')
),
total_wo as 
(
    select trunc(datemonth,'month') as mm,count(cchr_fulfillment_method) as nums from wo group by trunc(datemonth,'month')
)
select auto_wo.mm,auto_wo.nums,total_wo.nums,trunc(auto_wo.nums/total_wo.nums*100,2) as autorate_percent from auto_wo,total_wo
where auto_wo.mm=total_wo.mm order by 1;

再分享个多年来我一直在使用的sql, 用于查询表空间使用率。

set lines 150
set pages 30
with t1 as 
(
SELECT a.tablespace_name "Name",
       c.CONTENTS "Type",
       c.extent_management "Managment",
       count(a.file_id) as "Files",
       Round(SUM(a.bytes)/(1024*1024),1) "Size(M)",
       Round((SUM(a.bytes)/(1024*1024)-SUM(nvl(b.bytes,0))/(1024*1024)),1) "Used(M)",
       Round(SUM(nvl(b.bytes,0))/(1024*1024),1) "Free(M)",
       Round((1- SUM(nvl(b.bytes,0))/SUM(a.bytes))*100,2) "used(%)"
FROM   dba_data_files a,
       (  SELECT file_id,
                 sum(bytes) bytes
           FROM  dba_free_space
           GROUP BY file_id
        ) b,
        Dba_Tablespaces c
WHERE  a.file_id=b.file_id(+)
AND    a.tablespace_name=c.tablespace_name
GROUP BY a.tablespace_name,c.contents,c.extent_management,sysdate
order by 1 
)
select "Name","Type","Managment","Files","Size(M)","Used(M)","Free(M)","used(%)"
        from t1
union all 
select '' "Name",'' "Type",'' "Managment",sum("Files") "Files",sum("Size(M)") as "Size(M)",
        sum("Used(M)") as "Used(M)",sum("Free(M)") as "Free(M)",100*sum("Used(M)")/sum("Size(M)") as "used(%)"
        from t1
/

下周介绍如何使用WITH语句进行递归查询，轻松显示带有层级关系的数据。

数据库

文章转载自长河的笔记，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

使用Select WITH 子句简化复杂查询

使用select with子句的好处

创建测试数据

评论