一篇文章教你学会使用Oracle 数据库中、WITH 临时表名 AS ()语法

WITH 临时表表名 AS (
    SELECT *
    FROM employees
    WHERE department = 'Sales'
)
SELECT *
FROM 临时表表名
WHERE salary > 50000;

--DM_CAPACITY_ANAL_IND_STAT-产能分析指标统计
WITH D AS (SELECT 
a.年份 AS 年份,
NVL(b.模件电裝单人工作耗时,0) AS 模件电裝单人工作耗时,
NVL(b.整机电裝单人工作耗时,0) AS 整机电裝单人工作耗时,
NVL(b.单机调试单人工作耗时,0) AS 单机调试单人工作耗时,
NVL(b.单板调试单人工作耗时,0) AS  单板调试单人工作耗时,
NVL(b.测发控系统联调单人工作耗时,0) AS 测发控系统联调单人工作耗时,
NVL(b.控制系统联调单人工作耗时,0) AS 控制系统联调单人工作耗时,
NVL(b.AVERAGE_PROC_TIME,0) AS AVERAGE_PROC_TIME
FROM 
(select to_char(years) 年份 from(select TO_CHAR(Sysdate, 'YYYY') - level + 1 years from dual connect by level <= 3)) a
LEFT JOIN (
SELECT 
ROUND(NVL(MJDZ_SINGLE_WORK_HOUR,0),2) AS 模件电裝单人工作耗时,
ROUND(NVL(ZJDZ_SINGLE_WORK_HOUR,0),2) AS 整机电裝单人工作耗时,
ROUND(NVL(DJTS_SINGLE_WORK_HOUR,0),2) AS 单机调试单人工作耗时,
ROUND(NVL(DBTS_SINGLE_WORK_HOUR,0),2) AS 单板调试单人工作耗时,
ROUND(NVL(CFKXTLT_SINGLE_WORK_HOUR,0),2) AS 测发控系统联调单人工作耗时,
ROUND(NVL(KZXTLT_SINGLE_WORK_HOUR,0),2) AS 控制系统联调单人工作耗时,
STAT_TIME AS 统计日期,
AVERAGE_PROC_TIME 
FROM BRAIN.DM_CAPACITY_ANAL_IND_STAT
) b
ON a.年份 = b.统计日期
)
SELECT 年份,
  '模件电裝' AS 工作类型,
  模件电裝单人工作耗时 AS 工作耗时,
  AVERAGE_PROC_TIME AS 平均工序耗时
FROM D  WHERE 年份 = '2021'
UNION ALL
SELECT 年份,
  '整机电裝' AS 工作类型,
  整机电裝单人工作耗时 AS 工作耗时,
  AVERAGE_PROC_TIME AS 平均工序耗时
FROM D  WHERE 年份 = '2021'
UNION ALL
SELECT 年份,
  '单机调试' AS 工作类型,
  单机调试单人工作耗时 AS 工作耗时,
  AVERAGE_PROC_TIME AS 平均工序耗时
FROM D  WHERE 年份 = '2021'
UNION ALL
SELECT 年份,
  '单板调试' AS 工作类型,
  单板调试单人工作耗时 AS 工作耗时,
  AVERAGE_PROC_TIME AS 平均工序耗时
FROM D  WHERE 年份 = '2021'
UNION ALL
SELECT 年份,
  '测发控系统调控' AS 工作类型,
  测发控系统联调单人工作耗时 AS 工作耗时,
  AVERAGE_PROC_TIME AS 平均工序耗时
FROM D  WHERE 年份 = '2021'
UNION ALL
SELECT 年份,
  '控制系统联调' AS 工作类型,
  控制系统联调单人工作耗时 AS 工作耗时,
  AVERAGE_PROC_TIME AS 平均工序耗时
FROM D  WHERE 年份 = '2021'

WITH recursive cte AS (
    SELECT id, parent_id, name
    FROM categories
    WHERE parent_id IS NULL
    UNION ALL
    SELECT c.id, c.parent_id, c.name
    FROM categories c
    INNER JOIN cte ON c.parent_id = cte.id
)
SELECT *
FROM cte;