sql语言之cte语法-平芜编程栈

WITH TableData AS使用的是CTE（Common Table Expressions，公共表表达式）语法，也称为"子查询因子化"或"WITH子句"。这是SQL标准的一部分，在很多主流数据库中都支持。

1. CTE基本语法

WITH cte_name (column1, column2, ...) AS ( -- 子查询定义 SELECT ... ) -- 主查询 SELECT * FROM cte_name;

2. 你的查询中CTE的结构

WITH TableData AS ( -- 第一个CTE：获取表的基本信息和字段信息 SELECT ... ), PrimaryKeys AS ( -- 第二个CTE：获取主键信息（可以定义多个CTE） SELECT ... ) -- 主查询，可以使用上面定义的所有CTE SELECT ... FROM ...

3. CTE的主要特点

优点：

可读性好：将复杂查询分解为多个逻辑部分
可重用：在主查询中可以多次引用同一个CTE
支持递归：实现递归查询（如树形结构查询）

在你的查询中的应用：

-- 第一部分：定义TableData CTE WITH TableData AS ( SELECT t.TABLE_NAME, tc.COMMENTS, c.COLUMN_NAME, ... FROM DBA_TABLES t JOIN ... -- 复杂的多表连接和计算 ), -- 第二部分：定义PrimaryKeys CTE（虽然你定义了但后面没使用） PrimaryKeys AS ( SELECT ... ) -- 第三部分：主查询，基于TableData进行进一步处理 SELECT ... FROM ( -- 使用UNION ALL组合多种类型的数据行 SELECT 0 AS GROUP_ID, ... FROM TableData UNION ALL SELECT 1 AS GROUP_ID, ... FROM TableData UNION ALL SELECT 2 AS GROUP_ID, ... FROM TableData UNION ALL SELECT 3 AS GROUP_ID, ... FROM TableData ) Combined ORDER BY ...;

4. 等价的传统写法（不使用CTE）

-- 不使用CTE，需要嵌套子查询 SELECT CASE WHEN GROUP_ID = 0 THEN '【表】' || TABLE_NAME || ... ELSE '' END AS 表信息, ... FROM ( -- 表头行 SELECT 0 AS GROUP_ID, TABLE_NAME, TABLE_COMMENT, ... FROM ( -- 重复TableData的逻辑 SELECT DISTINCT t.TABLE_NAME, tc.COMMENTS AS TABLE_COMMENT FROM DBA_TABLES t LEFT JOIN DBA_TAB_COMMENTS tc ON ... LEFT JOIN DBA_TAB_COLUMNS c ON ... WHERE t.OWNER = 'CIRCLE' ) distinct_tables UNION ALL -- 字段标题行（也需要重复TableData的逻辑） SELECT 1 AS GROUP_ID, TABLE_NAME, TABLE_COMMENT, ... FROM ( SELECT DISTINCT t.TABLE_NAME, tc.COMMENTS AS TABLE_COMMENT FROM DBA_TABLES t LEFT JOIN DBA_TAB_COMMENTS tc ON ... LEFT JOIN DBA_TAB_COLUMNS c ON ... WHERE t.OWNER = 'CIRCLE' ) distinct_tables UNION ALL -- 字段数据行（完整重复TableData逻辑） SELECT 2 AS GROUP_ID, TABLE_NAME, TABLE_COMMENT, COLUMN_NAME, DATA_TYPE, ... FROM ( -- 这是TableData的完整内容，需要在这里重复写一遍 SELECT t.TABLE_NAME, tc.COMMENTS AS TABLE_COMMENT, c.COLUMN_NAME, c.DATA_TYPE, ... FROM DBA_TABLES t LEFT JOIN DBA_TAB_COMMENTS tc ON ... LEFT JOIN DBA_TAB_COLUMNS c ON ... LEFT JOIN DBA_COL_COMMENTS cc ON ... WHERE t.OWNER = 'CIRCLE' ) table_data UNION ALL -- 空行分隔（也需要重复） SELECT 3 AS GROUP_ID, TABLE_NAME, TABLE_COMMENT, ... FROM ( SELECT DISTINCT t.TABLE_NAME, tc.COMMENTS AS TABLE_COMMENT FROM DBA_TABLES t LEFT JOIN DBA_TAB_COMMENTS tc ON ... LEFT JOIN DBA_TAB_COLUMNS c ON ... WHERE t.OWNER = 'CIRCLE' ) distinct_tables ) Combined ORDER BY ...;

5. CTE在不同数据库中的支持情况

数据库	支持情况	备注
Oracle	✅ 支持	Oracle 9i及以上
SQL Server	✅ 支持	SQL Server 2005及以上
PostgreSQL	✅ 支持
MySQL	✅ 支持	MySQL 8.0及以上
达梦数据库	✅ 支持	达梦支持CTE语法
SQLite	✅ 支持	SQLite 3.8.3及以上

6. CTE的实用示例

示例1：简单的CTE

-- 查询部门平均工资高于公司平均工资的部门 WITH DeptAvg AS ( SELECT dept_id, AVG(salary) as avg_salary FROM employees GROUP BY dept_id ), CompanyAvg AS ( SELECT AVG(salary) as company_avg FROM employees ) SELECT d.dept_id, d.avg_salary, c.company_avg FROM DeptAvg d, CompanyAvg c WHERE d.avg_salary > c.company_avg;

示例2：递归CTE（查询树形结构）

-- 查询组织架构树 WITH RECURSIVE OrgTree AS ( -- 初始查询：根节点 SELECT id, name, parent_id, 1 as level FROM organization WHERE parent_id IS NULL UNION ALL -- 递归查询：子节点 SELECT o.id, o.name, o.parent_id, ot.level + 1 FROM organization o INNER JOIN OrgTree ot ON o.parent_id = ot.id ) SELECT * FROM OrgTree ORDER BY level, id;