数据库 5 复杂查询#

基础SQL满足简单业务，窗口函数、递归CTE、存储过程、触发器是处理复杂统计、树形数据、数据库内逻辑的核心能力，不同数据库支持版本、语法存在明显区别。

一、窗口函数 row_number()#

窗口函数属于SQL标准语法，用于分组内排序、排名，不压缩行数（区别于GROUP BY聚合）
常用：ROW_NUMBER()、RANK()、DENSE_RANK()

1
-- 通用语法：按部门分组，薪资降序排名
2
SELECT
3
    name,dept,salary,
4
    ROW_NUMBER() OVER(PARTITION BY dept ORDER BY salary DESC) AS rn
5
FROM employee;

方言说明#

MySQL：8.0+版本才支持窗口函数，5.x版本无此能力；
Oracle、SQLServer：长久原生支持，兼容性更好。

二、CTE 公共表达式 & 递归CTE#

CTE简化多层子查询，递归CTE主要用于树形结构（组织架构、层级菜单）

1
-- 普通CTE（三库通用基础写法）
2
WITH emp_cte AS (
3
    SELECT name,salary FROM employee WHERE salary>5000
4
)
5
SELECT * FROM emp_cte;

1
-- 递归CTE简易模板（树形查询）
2
WITH RECURSIVE tree AS (
3
    SELECT id,name,parent_id FROM dept WHERE parent_id=0
4
    UNION ALL
5
    SELECT d.id,d.name,d.parent_id FROM dept d INNER JOIN tree t ON d.parent_id=t.id
6
)
7
SELECT * FROM tree;

方言差异#

MySQL：递归CTE必须加关键字 RECURSIVE；
Oracle、SQLServer：递归CTE不需要RECURSIVE；
Oracle 11g早期版本递归CTE支持有限，建议12c以上使用。

三、存储过程#

将SQL逻辑封装在数据库端，重复调用。

1
-- MySQL示例
2
DELIMITER //
3
CREATE PROCEDURE query_emp(IN dept_id INT)
4
BEGIN
5
    SELECT * FROM employee WHERE dept_id = dept_id;
6
END //
7
DELIMITER ;

1
-- SQLServer
2
CREATE PROC query_emp @dept_id INT
3
AS
4
BEGIN
5
    SELECT * FROM employee WHERE dept_id = @dept_id;
6
END

1
-- Oracle（PL/SQL语法，完全独立）
2
CREATE PROCEDURE query_emp(p_dept_id IN NUMBER)
3
IS
4
BEGIN
5
    SELECT * FROM employee WHERE dept_id=p_dept_id;
6
END;

重点：三者存储过程语法互不兼容，属于数据库私有扩展，跨库迁移成本极高。

四、触发器#

监听表增删改，自动执行预设SQL，用于数据同步、审计日志。

1
-- MySQL触发器示例
2
CREATE TRIGGER tri_emp_insert
3
AFTER INSERT ON employee FOR EACH ROW
4
BEGIN
5
    INSERT INTO emp_log(name) VALUES(NEW.name);
6
END;