2. 首页 > 国产数据库教程 > YashanDB教程 > 正文

yashandb教程FG026-YashanDB子查询与CTE

教程整理：风哥教程  |  更新时间：2026-04-09  |  教程分类：YashanDB教程  |  文档学习：30

本文档风哥主要介绍YashanDB子查询与CTE（公共表表达式）的相关知识，包括YashanDB子查询的概念、类型、实现，以及CTE的使用方法和性能优化，风哥教程参考YashanDB官方文档SQL语言参考手册内容，适合DBA和开发人员在学习和测试中使用。更多视频教程www.fgedu.net.cn

Part01-基础概念与理论知识

1.1 YashanDB子查询概念

YashanDB子查询是指在一个SQL语句中嵌套另一个SQL查询。子查询可以在SELECT、INSERT、UPDATE、DELETE语句中使用，用于获取中间结果集或过滤数据。学习交流加群风哥微信: itpux-com

YashanDB子查询的特点：

  • 可以嵌套在主查询中
  • 可以返回单个值、一行或多行数据
  • 可以用于过滤、计算或生成数据
  • 可以提高SQL语句的可读性和维护性
  • 可能影响查询性能

1.2 YashanDB子查询类型

YashanDB支持以下类型的子查询：

# 子查询类型
– 标量子查询：返回单个值
– 行子查询：返回一行数据
– 列子查询：返回一列数据
– 表子查询：返回多行多列数据
– 相关子查询：子查询引用主查询中的列
– 嵌套子查询：子查询中嵌套另一个子查询

1.3 YashanDB CTE概念

YashanDB CTE（Common Table Expression，公共表表达式）是一种临时结果集，在SQL语句执行过程中被定义和使用。CTE可以提高SQL语句的可读性和维护性，特别是在处理复杂查询时。

风哥提示：CTE是一种强大的SQL特性，可以简化复杂查询，提高代码可读性。建议在处理复杂查询时使用CTE，而不是嵌套子查询。学习交流加群风哥QQ113257174

Part02-生产环境规划与建议

2.1 YashanDB子查询规划

在生产环境中使用YashanDB子查询时，需要进行以下规划：

# 子查询需求分析
– 明确子查询的业务需求
– 确定子查询的返回类型和数据量
– 评估对子查询性能的影响

# 子查询设计
– 选择合适的子查询类型
– 优化子查询结构
– 避免嵌套过深
– 合理使用索引

# 子查询权限规划
– 确保用户拥有必要的查询权限
– 遵循最小权限原则
– 避免使用不必要的权限

# 子查询监控
– 监控子查询的执行性能
– 识别慢子查询
– 定期分析执行计划

2.2 YashanDB CTE规划

在生产环境中使用YashanDB CTE时，需要进行以下规划：

CTE规划要点：

  • 命名规范：使用有意义的CTE名称
  • 复杂度：合理控制CTE的复杂度
  • 递归：谨慎使用递归CTE
  • 性能：评估CTE对性能的影响
  • 维护：确保CTE的可维护性

2.3 YashanDB性能考虑

YashanDB子查询与CTE的性能考虑：

  • 子查询性能：嵌套子查询可能导致性能下降
  • CTE性能：CTE可以提高可读性，但可能影响性能
  • 索引使用：合理的索引可以提高子查询和CTE的性能
  • 执行计划：优化器会为子查询和CTE生成执行计划
  • 内存使用：子查询和CTE可能需要更多内存
风哥提示：子查询和CTE的性能优化是数据库性能调优的重要部分。建议在使用子查询和CTE时，充分考虑性能因素，优化查询结构，以提高系统性能。更多学习教程公众号风哥教程itpux_com

Part03-生产环境项目实施方案

3.1 YashanDB子查询实现

3.1.1 YashanDB标量子查询

— 创建测试表
CREATE TABLE fgedu_employees (
emp_id NUMBER PRIMARY KEY,
emp_name VARCHAR2(100),
department VARCHAR2(100),
salary NUMBER,
hire_date DATE
);

— 插入测试数据
INSERT INTO fgedu_employees VALUES (1, ‘张三’, ‘技术部’, 5000, SYSDATE – 365);
INSERT INTO fgedu_employees VALUES (2, ‘李四’, ‘销售部’, 6000, SYSDATE – 180);
INSERT INTO fgedu_employees VALUES (3, ‘王五’, ‘技术部’, 7000, SYSDATE – 90);
INSERT INTO fgedu_employees VALUES (4, ‘赵六’, ‘销售部’, 8000, SYSDATE – 30);
INSERT INTO fgedu_employees VALUES (5, ‘孙七’, ‘技术部’, 9000, SYSDATE);

— 标量子查询
SELECT
emp_id,
emp_name,
salary,
(SELECT AVG(salary) FROM fgedu_employees) AS “平均工资”
FROM fgedu_employees;

EMP_ID EMP_NAME SALARY 平均工资
—— ——– —— ———-
1 张三 5000 7000
2 李四 6000 7000
3 王五 7000 7000
4 赵六 8000 7000
5 孙七 9000 7000

3.1.2 YashanDB列子查询

— 列子查询
SELECT
emp_id,
emp_name,
department,
salary
FROM fgedu_employees
WHERE salary > (
SELECT AVG(salary)
FROM fgedu_employees
);

EMP_ID EMP_NAME DEPARTMENT SALARY
—— ——– ———- ——
4 赵六 销售部 8000
5 孙七 技术部 9000

— 使用IN子句的列子查询
SELECT
emp_id,
emp_name,
department
FROM fgedu_employees
WHERE department IN (
SELECT department
FROM fgedu_employees
GROUP BY department
HAVING COUNT(*) > 2
);

EMP_ID EMP_NAME DEPARTMENT
—— ——– ———-
1 张三 技术部
3 王五 技术部
5 孙七 技术部

3.1.3 YashanDB相关子查询

— 相关子查询
SELECT
e1.emp_id,
e1.emp_name,
e1.department,
e1.salary,
(SELECT AVG(salary) FROM fgedu_employees e2 WHERE e2.department = e1.department) AS “部门平均工资”
FROM fgedu_employees e1;

EMP_ID EMP_NAME DEPARTMENT SALARY 部门平均工资
—— ——– ———- —— ————
1 张三 技术部 5000 7000
2 李四 销售部 6000 7000
3 王五 技术部 7000 7000
4 赵六 销售部 8000 7000
5 孙七 技术部 9000 7000

3.2 YashanDB CTE实现

3.2.1 YashanDB基本CTE

— 基本CTE
WITH dept_avg_salary AS (
SELECT
department,
AVG(salary) AS avg_salary
FROM fgedu_employees
GROUP BY department
)
SELECT
e.emp_id,
e.emp_name,
e.department,
e.salary,
d.avg_salary AS “部门平均工资”
FROM fgedu_employees e
JOIN dept_avg_salary d ON e.department = d.department;

EMP_ID EMP_NAME DEPARTMENT SALARY 部门平均工资
—— ——– ———- —— ————
1 张三 技术部 5000 7000
2 李四 销售部 6000 7000
3 王五 技术部 7000 7000
4 赵六 销售部 8000 7000
5 孙七 技术部 9000 7000

3.2.2 YashanDB多CTE

— 多CTE
WITH
dept_avg_salary AS (
SELECT
department,
AVG(salary) AS avg_salary
FROM fgedu_employees
GROUP BY department
),
dept_emp_count AS (
SELECT
department,
COUNT(*) AS emp_count
FROM fgedu_employees
GROUP BY department
)
SELECT
d1.department,
d1.avg_salary AS “部门平均工资”,
d2.emp_count AS “员工数量”
FROM dept_avg_salary d1
JOIN dept_emp_count d2 ON d1.department = d2.department;

DEPARTMENT 部门平均工资 员工数量
———- ———— ——–
技术部 7000 3
销售部 7000 2

3.2.3 YashanDB递归CTE

— 递归CTE
WITH RECURSIVE numbers (n) AS (
SELECT 1 AS n
UNION ALL
SELECT n + 1
FROM numbers
WHERE n < 10 ) SELECT n FROM numbers; N ---------- 1 2 3 4 5 6 7 8 9 10

3.3 YashanDB优化实现

3.3.1 YashanDB子查询优化

— 优化前：相关子查询
SELECT
e1.emp_id,
e1.emp_name,
e1.department,
e1.salary,
(SELECT AVG(salary) FROM fgedu_employees e2 WHERE e2.department = e1.department) AS “部门平均工资”
FROM fgedu_employees e1;

— 优化后：使用JOIN替代相关子查询
SELECT
e.emp_id,
e.emp_name,
e.department,
e.salary,
d.avg_salary AS “部门平均工资”
FROM fgedu_employees e
JOIN (
SELECT
department,
AVG(salary) AS avg_salary
FROM fgedu_employees
GROUP BY department
) d ON e.department = d.department;

— 优化前：嵌套子查询
SELECT *
FROM fgedu_employees
WHERE salary > (
SELECT AVG(salary)
FROM fgedu_employees
WHERE department IN (
SELECT department
FROM fgedu_employees
GROUP BY department
HAVING COUNT(*) > 2
)
);

— 优化后：使用CTE
WITH dept_with_many_emps AS (
SELECT department
FROM fgedu_employees
GROUP BY department
HAVING COUNT(*) > 2
),
avg_salary_in_dept AS (
SELECT AVG(salary) AS avg_salary
FROM fgedu_employees
WHERE department IN (SELECT department FROM dept_with_many_emps)
)
SELECT *
FROM fgedu_employees
WHERE salary > (SELECT avg_salary FROM avg_salary_in_dept);

3.3.2 YashanDB CTE优化

— 优化前：复杂CTE
WITH
cte1 AS (
SELECT * FROM fgedu_employees WHERE department = ‘技术部’
),
cte2 AS (
SELECT * FROM cte1 WHERE salary > 6000
),
cte3 AS (
SELECT * FROM cte2 WHERE hire_date > SYSDATE – 180
)
SELECT * FROM cte3;

— 优化后：简化CTE
WITH cte AS (
SELECT *
FROM fgedu_employees
WHERE department = ‘技术部’
AND salary > 6000
AND hire_date > SYSDATE – 180
)
SELECT * FROM cte;

— 优化前：未使用索引的CTE
WITH cte AS (
SELECT *
FROM fgedu_employees
WHERE salary > 6000
)
SELECT * FROM cte;

— 优化后：创建索引
CREATE INDEX idx_fgedu_employees_salary ON fgedu_employees(salary);

WITH cte AS (
SELECT *
FROM fgedu_employees
WHERE salary > 6000
)
SELECT * FROM cte;

风哥提示：子查询和CTE的优化需要综合考虑多个因素，包括索引、执行计划、查询结构等。建议在编写子查询和CTE时，充分考虑这些因素，以提高查询性能。from yashandb视频:www.itpux.com

Part04-生产案例与实战讲解

4.1 YashanDB子查询实战案例

在生产环境中，使用子查询解决业务问题：

— 案例：获取高于部门平均工资的员工
SELECT
emp_id,
emp_name,
department,
salary,
(SELECT AVG(salary) FROM fgedu_employees e2 WHERE e2.department = e1.department) AS “部门平均工资”
FROM fgedu_employees e1
WHERE salary > (SELECT AVG(salary) FROM fgedu_employees e2 WHERE e2.department = e1.department);

EMP_ID EMP_NAME DEPARTMENT SALARY 部门平均工资
—— ——– ———- —— ————
4 赵六 销售部 8000 7000
5 孙七 技术部 9000 7000

— 案例：获取每个部门工资最高的员工
SELECT
e1.emp_id,
e1.emp_name,
e1.department,
e1.salary
FROM fgedu_employees e1
WHERE (e1.department, e1.salary) IN (
SELECT
department,
MAX(salary)
FROM fgedu_employees
GROUP BY department
);

EMP_ID EMP_NAME DEPARTMENT SALARY
—— ——– ———- ——
5 孙七 技术部 9000
4 赵六 销售部 8000

4.2 YashanDB CTE实战案例

在生产环境中，使用CTE解决复杂业务问题：

— 案例：分析员工工资分布
WITH salary_stats AS (
SELECT
department,
COUNT(*) AS emp_count,
MIN(salary) AS min_salary,
MAX(salary) AS max_salary,
AVG(salary) AS avg_salary,
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) AS median_salary
FROM fgedu_employees
GROUP BY department
)
SELECT
department,
emp_count AS “员工数量”,
min_salary AS “最低工资”,
max_salary AS “最高工资”,
avg_salary AS “平均工资”,
median_salary AS “中位数工资”
FROM salary_stats
ORDER BY department;

DEPARTMENT 员工数量 最低工资 最高工资 平均工资 中位数工资
———- ——– ——– ——– ——– ———-
技术部 3 5000 9000 7000 7000
销售部 2 6000 8000 7000 7000

— 案例：递归CTE生成日期序列
WITH RECURSIVE date_series (date_val) AS (
SELECT SYSDATE AS date_val
UNION ALL
SELECT date_val – 1
FROM date_series
WHERE date_val > SYSDATE – 7
)
SELECT
TO_CHAR(date_val, ‘YYYY-MM-DD’) AS date_str,
TO_CHAR(date_val, ‘DAY’) AS day_of_week
FROM date_series;

DATE_STR DAY_OF_WEEK
———- ———–
2026-04-11 SATURDAY
2026-04-10 FRIDAY
2026-04-09 THURSDAY
2026-04-08 WEDNESDAY
2026-04-07 TUESDAY
2026-04-06 MONDAY
2026-04-05 SUNDAY
2026-04-04 SATURDAY

4.3 YashanDB优化实战案例

在生产环境中，优化子查询和CTE提高性能：

— 案例：优化相关子查询
— 优化前：相关子查询执行缓慢
EXPLAIN PLAN FOR
SELECT
e1.emp_id,
e1.emp_name,
e1.department,
e1.salary,
(SELECT AVG(salary) FROM fgedu_employees e2 WHERE e2.department = e1.department) AS “部门平均工资”
FROM fgedu_employees e1;

— 执行计划显示全表扫描

— 优化后：使用JOIN替代相关子查询
EXPLAIN PLAN FOR
SELECT
e.emp_id,
e.emp_name,
e.department,
e.salary,
d.avg_salary AS “部门平均工资”
FROM fgedu_employees e
JOIN (
SELECT
department,
AVG(salary) AS avg_salary
FROM fgedu_employees
GROUP BY department
) d ON e.department = d.department;

— 执行计划显示更好的性能

— 案例：优化CTE性能
— 优化前：未使用索引的CTE
CREATE TABLE fgedu_large_table (
id NUMBER PRIMARY KEY,
name VARCHAR2(100),
value NUMBER
);

— 插入测试数据
INSERT INTO fgedu_large_table
SELECT LEVEL, ‘Test_’ || LEVEL, LEVEL * 10
FROM DUAL
CONNECT BY LEVEL <= 10000; -- 未使用索引的CTE EXPLAIN PLAN FOR WITH cte AS ( SELECT * FROM fgedu_large_table WHERE value > 5000
)
SELECT * FROM cte;

— 执行计划显示全表扫描

— 优化后：创建索引
CREATE INDEX idx_fgedu_large_table_value ON fgedu_large_table(value);

— 使用索引的CTE
EXPLAIN PLAN FOR
WITH cte AS (
SELECT *
FROM fgedu_large_table
WHERE value > 5000
)
SELECT * FROM cte;

— 执行计划显示使用索引

生产环境建议：在生产环境中，子查询和CTE的优化是提高系统性能的关键。建议定期分析子查询和CTE的执行计划，优化查询结构，创建适当的索引，以提高查询性能。更多视频教程www.fgedu.net.cn

Part05-风哥经验总结与分享

5.1 YashanDB使用经验总结

YashanDB子查询与CTE使用经验总结：

  • 子查询使用：适用于简单的查询场景，避免嵌套过深
  • CTE使用：适用于复杂的查询场景，提高代码可读性
  • 性能考虑：子查询和CTE可能影响性能，需要优化
  • 索引使用：合理的索引可以提高子查询和CTE的性能
  • 执行计划：定期分析执行计划，优化查询路径

5.2 YashanDB故障排除

# 常见问题及解决方法

## 1. 子查询执行缓慢
– 症状：子查询执行时间长
– 原因：嵌套过深、缺少索引、全表扫描
– 解决：优化子查询结构，创建适当的索引，避免全表扫描

## 2. CTE性能问题
– 症状：CTE执行时间长
– 原因：CTE过于复杂、缺少索引、递归CTE效率低
– 解决：简化CTE结构，创建适当的索引，优化递归CTE

## 3. 相关子查询性能差
– 症状：相关子查询执行缓慢
– 原因：相关子查询需要多次执行
– 解决：使用JOIN替代相关子查询，减少执行次数

## 4. 递归CTE无限循环
– 症状：递归CTE执行时间过长或报错
– 原因：递归条件不正确，导致无限循环
– 解决：确保递归条件正确，设置合理的终止条件

## 5. 内存不足
– 症状：子查询或CTE执行时报错”ORA-04030: out of process memory”
– 原因：子查询或CTE需要大量内存
– 解决：优化查询结构，减少中间结果集，增加内存

5.3 YashanDB使用建议

YashanDB子查询与CTE使用建议：

  • 选择合适的查询方式：根据查询复杂度选择子查询或CTE
  • 优化查询结构：避免嵌套过深的子查询，简化CTE结构
  • 使用索引：为查询条件创建适当的索引
  • 监控性能：定期监控子查询和CTE的执行性能
  • 分析执行计划：定期分析执行计划，优化查询路径
  • 测试充分：在测试环境充分测试子查询和CTE，确保性能满足要求
风哥提示：子查询和CTE是SQL语言中强大的特性，可以简化复杂查询，提高代码可读性。建议在使用子查询和CTE时，充分考虑性能因素，优化查询结构，以提高系统性能。学习交流加群风哥微信: itpux-com

持续改进：子查询和CTE的优化是一个持续的过程，需要根据实际情况不断调整和改进。建议定期review子查询和CTE的执行情况，优化查询结构，创建适当的索引，以提高系统性能和可靠性。更多学习教程公众号风哥教程itpux_com

本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html

相关推荐

联系我们

在线咨询：点击这里给我发消息

微信号：itpux-com

工作日：9:30-18:30，节假日休息