MySQL数据库

“透视表”或“交叉表报告”的实现

概述

在 MySQL 中，可以利用数学函数（而非 IF 或 CASE 语句）创建透视表。这种方法利用以下函数的特性：

1. SIGN(x)

   • 返回：
     • -1 当 x < 0
     • 0 当 x = 0
     • +1 当 x > 0

2. ABS(SIGN(x))

   • 返回：
     • 0 当 x = 0
     • 1 当 x ≠ 0

3. 1 - ABS(SIGN(x))

   • 返回：
     • 1 当 x = 0
     • 0 当 x ≠ 0

示例表结构和数据

CREATE TABLE exams (
  pkey INT(11) NOT NULL AUTO_INCREMENT,
  name VARCHAR(15),
  exam INT,
  score INT,
  PRIMARY KEY (pkey)
);

INSERT INTO exams (name, exam, score) VALUES
('Bob', 1, 75), ('Bob', 2, 77), ('Bob', 3, 78), ('Bob', 4, 80),
('Sue', 1, 90), ('Sue', 2, 97), ('Sue', 3, 98), ('Sue', 4, 99);

执行以下查询以验证数据：

SELECT * FROM exams;

输出：

pkey	name	exam	score
1	Bob	1	75
2	Bob	2	77
3	Bob	3	78
4	Bob	4	80
5	Sue	1	90
6	Sue	2	97
7	Sue	3	98
8	Sue	4	99

---

创建透视表

通过单个查询生成透视表：

SELECT 
  name,
  SUM(score * (1 - ABS(SIGN(exam - 1)))) AS exam1,
  SUM(score * (1 - ABS(SIGN(exam - 2)))) AS exam2,
  SUM(score * (1 - ABS(SIGN(exam - 3)))) AS exam3,
  SUM(score * (1 - ABS(SIGN(exam - 4)))) AS exam4
FROM exams
GROUP BY name;

输出结果：

name	exam1	exam2	exam3	exam4
Bob	75	77	78	80
Sue	90	97	98	99

---

分解透视逻辑

以 exam2 列为例，解释逻辑：

SELECT 
  name, 
  score, 
  exam, 
  exam - 2 AS diff, 
  SIGN(exam - 2) AS sign_val, 
  ABS(SIGN(exam - 2)) AS abs_val, 
  1 - ABS(SIGN(exam - 2)) AS match_flag, 
  score * (1 - ABS(SIGN(exam - 2))) AS exam2
FROM exams;

输出：

name	score	exam	diff	sign_val	abs_val	match_flag	exam2
Bob	75	1	-1	-1	1	0	0
Bob	77	2	0	0	0	1	77
Bob	78	3	1	1	1	0	0
Bob	80	4	2	1	1	0	0
Sue	90	1	-1	-1	1	0	0
Sue	97	2	0	0	0	1	97
Sue	98	3	1	1	1	0	0
Sue	99	4	2	1	1	0	0

---

总结

• 利用数学函数 SIGN 和 ABS 可以高效实现透视表。
• 查询不依赖 IF 或 CASE，避免复杂条件带来的兼容性问题。
• 这种方法可以在几乎所有数据库中工作，通用性强。

翻译：透视表与行间差异的计算

关于 IF 的问题

使用 IF 创建透视表时可能出现问题。以下查询并不能正确输出：

SELECT name,
  IF(exam=1, score, NULL) AS exam1,
  IF(exam=2, score, NULL) AS exam2,
  IF(exam=3, score, NULL) AS exam3,
  IF(exam=4, score, NULL) AS exam4
FROM exams
GROUP BY name;

输出：

name	exam1	exam2	exam3	exam4
Bob	75	NULL	NULL	NULL
Sue	90	NULL	NULL	NULL

这是因为 GROUP BY 在处理 IF 时，只保留了每组的第一条记录。

---

正确的解决方案

使用 SUM 和 IF 结合可解决该问题：

SELECT name,
  SUM(IF(exam=1, score, NULL)) AS exam1,
  SUM(IF(exam=2, score, NULL)) AS exam2,
  SUM(IF(exam=3, score, NULL)) AS exam3,
  SUM(IF(exam=4, score, 0)) AS exam4
FROM exams
GROUP BY name;

输出：

name	exam1	exam2	exam3	exam4
Bob	75	77	78	80
Sue	90	97	98	99

---

使用数学函数创建透视表

另一种方法是使用数学函数 SIGN 和 ABS：

SELECT name,
  SUM(score * (1 - ABS(SIGN(exam - 1)))) AS exam1,
  SUM(score * (1 - ABS(SIGN(exam - 2)))) AS exam2,
  SUM(score * (1 - ABS(SIGN(exam - 3)))) AS exam3,
  SUM(score * (1 - ABS(SIGN(exam - 4)))) AS exam4
FROM exams
GROUP BY name;

---

计算考试分数的差异

以下查询计算考试分数的逐步差异（delta）：

SELECT name,
  SUM(score * (1 - ABS(SIGN(exam - 1)))) AS exam1,
  SUM(score * (1 - ABS(SIGN(exam - 2)))) AS exam2,
  SUM(score * (1 - ABS(SIGN(exam - 3)))) AS exam3,
  SUM(score * (1 - ABS(SIGN(exam - 4)))) AS exam4,
  SUM(score * (1 - ABS(SIGN(exam - 2)))) - SUM(score * (1 - ABS(SIGN(exam - 1)))) AS delta_1_2,
  SUM(score * (1 - ABS(SIGN(exam - 3)))) - SUM(score * (1 - ABS(SIGN(exam - 2)))) AS delta_2_3,
  SUM(score * (1 - ABS(SIGN(exam - 4)))) - SUM(score * (1 - ABS(SIGN(exam - 3)))) AS delta_3_4
FROM exams
GROUP BY name;

输出：

name	exam1	exam2	exam3	exam4	delta_1_2	delta_2_3	delta_3_4
Bob	75	77	78	80	2	1	2
Sue	90	97	98	99	7	1	1

---

计算总增量分数和平均值

结合总增量分数和平均值：

SELECT name,
  SUM(score * (1 - ABS(SIGN(exam - 1)))) AS exam1,
  SUM(score * (1 - ABS(SIGN(exam - 2)))) AS exam2,
  SUM(score * (1 - ABS(SIGN(exam - 3)))) AS exam3,
  SUM(score * (1 - ABS(SIGN(exam - 4)))) AS exam4,
  SUM(score * (1 - ABS(SIGN(exam - 2)))) - SUM(score * (1 - ABS(SIGN(exam - 1)))) +
  SUM(score * (1 - ABS(SIGN(exam - 3)))) - SUM(score * (1 - ABS(SIGN(exam - 2)))) +
  SUM(score * (1 - ABS(SIGN(exam - 4)))) - SUM(score * (1 - ABS(SIGN(exam - 3)))) AS TotalIncPoints,
  (SUM(score * (1 - ABS(SIGN(exam - 1)))) +
   SUM(score * (1 - ABS(SIGN(exam - 2)))) +
   SUM(score * (1 - ABS(SIGN(exam - 3)))) +
   SUM(score * (1 - ABS(SIGN(exam - 4))))) / 4 AS AVG
FROM exams
GROUP BY name;

输出：

name	exam1	exam2	exam3	exam4	TotalIncPoints	AVG
Bob	75	77	78	80	5	77.50
Sue	90	97	98	99	9	96.00

---

结合更多平均值

计算移动平均值（AVG1_2、AVG2_3、AVG3_4）：

SELECT name,
  SUM(score * (1 - ABS(SIGN(exam - 1)))) AS exam1,
  SUM(score * (1 - ABS(SIGN(exam - 2)))) AS exam2,
  SUM(score * (1 - ABS(SIGN(exam - 3)))) AS exam3,
  SUM(score * (1 - ABS(SIGN(exam - 4)))) AS exam4,
  (SUM(score * (1 - ABS(SIGN(exam - 1)))) + SUM(score * (1 - ABS(SIGN(exam - 2))))) / 2 AS AVG1_2,
  (SUM(score * (1 - ABS(SIGN(exam - 2)))) + SUM(score * (1 - ABS(SIGN(exam - 3))))) / 2 AS AVG2_3,
  (SUM(score * (1 - ABS(SIGN(exam - 3)))) + SUM(score * (1 - ABS(SIGN(exam - 4))))) / 2 AS AVG3_4,
  (SUM(score * (1 - ABS(SIGN(exam - 1)))) +
   SUM(score * (1 - ABS(SIGN(exam - 2)))) +
   SUM(score * (1 - ABS(SIGN(exam - 3)))) +
   SUM(score * (1 - ABS(SIGN(exam - 4))))) / 4 AS AVG
FROM exams
GROUP BY name;

输出：

name	exam1	exam2	exam3	exam4	AVG1_2	AVG2_3	AVG3_4	AVG
Bob	75	77	78	80	76.00	77.50	79.00	77.50
Sue	90	97	98	99	93.50	97.50	98.50	96.00

---

总结

这种方法利用数学函数和单个 SQL 查询实现复杂统计（透视表、增量、平均值等），无需依赖复杂逻辑条件（如 IF 或 CASE），从而提高兼容性和效率。