核心内容摘要
魅影直播：游客免登录的诱惑与潜藏的风险
WITH TableData AS使用的是CTECommon Table Expressions公共表表达式语法也称为子查询因子化或WITH子句。
这是SQL标准的一部分在很多主流数据库中都支持。
CTE基本语法WITH cte_name (column1, column2, ...) AS ( -- 子查询定义 SELECT ... ) -- 主查询 SELECT * FROM cte_name;
你的查询中CTE的结构WITH TableData AS ( -- 第一个CTE获取表的基本信息和字段信息 SELECT ... ), PrimaryKeys AS ( -- 第二个CTE获取主键信息可以定义多个CTE SELECT ... ) -- 主查询可以使用上面定义的所有CTE SELECT ... FROM ...
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 ...;
等价的传统写法不使用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 ...;
CTE在不同数据库中的支持情况数据库支持情况备注Oracle✅ 支持Oracle 9i及以上SQL Server✅ 支持SQL Server 2005及以上PostgreSQL✅ 支持MySQL✅ 支持MySQL
0及以上达梦数据库✅ 支持达梦支持CTE语法SQLite✅ 支持SQLite
3.
3及以上
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;
在你的场景中的好处使用CTE让你的查询更清晰将数据准备逻辑TableData与展示逻辑分离更易维护如果需要修改数据获取逻辑只需修改CTE部分性能可能更好数据库优化器可以更好地优化CTE
8.