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的主要特点

优点：

1. 可读性好：将复杂查询分解为多个逻辑部分

2. 可重用：在主查询中可以多次引用同一个CTE

3. 支持递归：实现递归查询（如树形结构查询）

在你的查询中的应用：

-- 第一部分：定义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在不同数据库中的支持情况

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;

7. 在你的场景中的好处

使用CTE让你的查询：

1. 更清晰：将数据准备逻辑（TableData）与展示逻辑分离

2. 更易维护：如果需要修改数据获取逻辑，只需修改CTE部分

3. 性能可能更好：数据库优化器可以更好地优化CTE

8. 注意事项

1. CTE只在当前查询中有效：CTE定义的作用域仅限于当前查询

2. 可以引用前面的CTE：后面的CTE可以引用前面定义的CTE

3. 不能嵌套：不能在CTE内部再定义CTE

4. 分号位置：CTE定义结束后，主查询前不需要分号

你的查询使用了CTE来构建一个"表格化"的表结构输出，这是一种很好的实践，特别是当需要生成包含表头、标题行、数据行和分隔行的完整报表时。