SQL 分支语句与行转列

SQL 分支语句与行转列学习笔记

在 SQL 中，分支语句用于根据条件执行不同的逻辑，而行转列是一种将行数据转换为列数据的操作。

1. 分支语句

分支语句用于根据条件执行不同的逻辑。SQL 提供了两种常用的分支语句：DECODE 和 CASE WHEN。

（1）DECODE 函数

DECODE 是 Oracle 提供的一种简单的条件判断函数。

语法：

DECODE(expression, value1, result1, value2, result2, ..., default_result)

示例：

-- 判断字符串是否等于 'kk'
SELECT DECODE('kk', 'kk', '找到', '找不到') FROM dual; -- 结果：'找到'

-- 根据部门编号输出部门名称
SELECT deptno,
       DECODE(deptno, 10, '10部门', 20, '20部门', 30, '30部门', 'null') AS 部门名称
FROM tmp_emp;

注意：

• DECODE 函数只能用于简单的等值判断。
• 如果所有条件都不满足，则返回默认值（default_result）。

（2）CASE WHEN 语句

CASE WHEN 是一种更灵活的分支语句，支持复杂的条件判断。

语法：

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ...
    ELSE default_result
END

示例：

-- 根据部门编号输出部门名称
SELECT deptno,
       CASE
           WHEN deptno = 10 THEN '10部门'
           WHEN deptno = 20 THEN '20部门'
           WHEN deptno = 30 THEN '30部门'
           ELSE 'null'
       END AS 部门名称
FROM tmp_emp;

-- 根据工资范围调整工资
SELECT sal,
       CASE
           WHEN sal < 1000 THEN sal + 1
           WHEN sal BETWEEN 1000 AND 2000 THEN sal + 2
           ELSE sal + 3
       END AS 调整后工资
FROM tmp_emp;

注意：

• CASE WHEN 支持复杂的条件判断，比 DECODE 更灵活。
• ELSE 子句是可选的，如果没有 ELSE，则默认返回 NULL。

2. 行转列

行转列是一种将行数据转换为列数据的操作，常用于生成报表或汇总数据。

（1）使用 DECODE 实现行转列

-- 创建测试表
CREATE TABLE tmp_test (
    id INT,
    name VARCHAR2(10),
    subject VARCHAR2(20),
    score NUMBER
);

-- 插入测试数据
INSERT INTO tmp_test VALUES(1, '小红', '语文', 70);
INSERT INTO tmp_test VALUES(2, '小红', '数学', 80);
INSERT INTO tmp_test VALUES(3, '小红', '英语', 75);
INSERT INTO tmp_test VALUES(4, '小蓝', '语文', 65);
INSERT INTO tmp_test VALUES(5, '小蓝', '数学', 75);
INSERT INTO tmp_test VALUES(6, '小蓝', '英语', 60);
INSERT INTO tmp_test VALUES(7, '小黄', '语文', 60);
INSERT INTO tmp_test VALUES(8, '小黄', '数学', 90);
INSERT INTO tmp_test VALUES(9, '小黑', '数学', 80);
INSERT INTO tmp_test VALUES(10, '小黑', '英语', 90);
COMMIT;

-- 使用 DECODE 实现行转列
SELECT name,
       MAX(DECODE(subject, '语文', score, 0)) AS 语文,
       MAX(DECODE(subject, '数学', score, 0)) AS 数学,
       MAX(DECODE(subject, '英语', score, 0)) AS 英语
FROM tmp_test
GROUP BY name;

（2）使用 CASE WHEN 实现行转列

-- 使用 CASE WHEN 实现行转列
SELECT name,
       MAX(CASE WHEN subject = '语文' THEN score ELSE 0 END) AS 语文,
       MAX(CASE WHEN subject = '数学' THEN score ELSE 0 END) AS 数学,
       MAX(CASE WHEN subject = '英语' THEN score ELSE 0 END) AS 英语
FROM tmp_test
GROUP BY name;

（3）使用 PIVOT 实现行转列（Oracle 11g 及以上版本）

-- 使用 PIVOT 实现行转列
SELECT *
FROM (SELECT name, subject, score FROM tmp_test)
PIVOT (MAX(score) FOR subject IN ('语文' AS 语文, '数学' AS 数学, '英语' AS 英语));

注意：

• PIVOT 是 Oracle 11g 引入的功能，语法更简洁。
• PIVOT 只能用于聚合函数（如 MAX、MIN、SUM 等）。

3. 综合示例

以下是一些综合示例，展示了分支语句和行转列的实际应用。

（1）按部门编号涨工资

-- 使用 DECODE
SELECT deptno,
       sal,
       DECODE(deptno, 10, sal * 1.1, 20, sal * 1.2, sal * 1.3) AS 调整后工资
FROM tmp_emp;

-- 使用 CASE WHEN
SELECT deptno,
       sal,
       CASE
           WHEN deptno = 10 THEN sal * 1.1
           WHEN deptno = 20 THEN sal * 1.2
           ELSE sal * 1.3
       END AS 调整后工资
FROM tmp_emp;

（2）行转列生成学生成绩表

-- 使用 DECODE
SELECT name,
       MAX(DECODE(subject, '语文', score, 0)) AS 语文,
       MAX(DECODE(subject, '数学', score, 0)) AS 数学,
       MAX(DECODE(subject, '英语', score, 0)) AS 英语
FROM tmp_test
GROUP BY name;

-- 使用 CASE WHEN
SELECT name,
       MAX(CASE WHEN subject = '语文' THEN score ELSE 0 END) AS 语文,
       MAX(CASE WHEN subject = '数学' THEN score ELSE 0 END) AS 数学,
       MAX(CASE WHEN subject = '英语' THEN score ELSE 0 END) AS 英语
FROM tmp_test
GROUP BY name;

-- 使用 PIVOT
SELECT *
FROM (SELECT name, subject, score FROM tmp_test)
PIVOT (MAX(score) FOR subject IN ('语文' AS 语文, '数学' AS 数学, '英语' AS 英语));

总结

1. 分支语句：DECODE 和 CASE WHEN 的使用方法及区别。
2. 行转列：使用 DECODE、CASE WHEN 和 PIVOT 实现行转列操作。