在项目中遇到一种情况，是需要联结表A和表B。其中，表A中有一条数据，如A_id以及A相关的数据，而表B中对应的数据有多条，如B_id, A_id, type, value，其中用于区分不同数据条目的字段是A_id和type。需要用SQL语句，将结果数据集按如下的规则显示：
A_id type1_val type2_val type3_val
一个类比的例子是：
有个学生(成绩)表student，里面记录学生每次考试、每个科目的成绩，要求构造出一个结果集，字段是学生id，第几次，科目1，科目2，科目3的成绩
有如下测试表
mysql> select * from student;
+----+--------+------------+-------+-------+
| id | stu_id | project_id | times | score |
+----+--------+------------+-------+-------+
| 1 | 101 | 1 | 1 | 90 |
| 2 | 101 | 2 | 1 | 80 |
| 3 | 101 | 3 | 1 | 91 |
| 4 | 102 | 1 | 1 | 50 |
| 5 | 102 | 2 | 1 | 80 |
| 6 | 102 | 3 | 1 | 55 |
| 7 | 103 | 1 | 1 | 80 |
| 8 | 103 | 2 | 1 | 82 |
| 9 | 103 | 3 | 1 | 84 |
| 10 | 101 | 1 | 2 | 70 |
| 11 | 101 | 2 | 2 | 100 |
| 12 | 101 | 3 | 2 | 89 |
| 13 | 102 | 1 | 2 | 100 |
| 14 | 102 | 2 | 2 | 81 |
| 15 | 102 | 3 | 2 | 78 |
| 16 | 103 | 1 | 2 | 80 |
| 17 | 103 | 2 | 2 | 92 |
| 18 | 103 | 3 | 2 | 74 |
| 19 | 101 | 1 | 3 | 72 |
| 20 | 101 | 2 | 3 | 90 |
| 21 | 101 | 3 | 3 | 81 |
| 22 | 102 | 1 | 3 | 92 |
| 23 | 102 | 2 | 3 | 82 |
| 24 | 102 | 3 | 3 | 79 |
| 25 | 103 | 1 | 3 | 100 |
| 26 | 103 | 2 | 3 | 77 |
| 27 | 103 | 3 | 3 | 59 |
+----+--------+------------+-------+-------+
27 rows in set (0.00 sec)
可以使用CASE WHEN语句，一则例子
这个语句的作用是，判断某个字段是否符合某个条件，然后取得相应的值，与编程语言的switch语句类似
参考SQL语句如下：
select stu_id, times,
SUM(CASE project_id WHEN 1 THEN score ELSE 0 END) AS pro_1,
SUM(CASE project_id WHEN 2 THEN score ELSE 0 END) AS pro_2,
SUM(CASE project_id WHEN 3 THEN score ELSE 0 END) AS pro_3,
SUM(score) AS total
from student
GROUP BY stu_id, times
ORDER BY total desc, pro_1 desc, pro_2 desc, pro_3 desc;
核心的字段SUM(CASE project_id WHEN 1 THEN score ELSE 0 END) AS pro_1，相当于将原来的数据表GROUP后的结果，进行遍历，如果满足条件(project_id = 1)的，则加上score，否则加0，最后得出结果
+--------+-------+-------+-------+-------+-------+
| stu_id | times | pro_1 | pro_2 | pro_3 | total |
+--------+-------+-------+-------+-------+-------+
| 101 | 1 | 90 | 80 | 91 | 261 |
| 102 | 2 | 100 | 81 | 78 | 259 |
| 101 | 2 | 70 | 100 | 89 | 259 |
| 102 | 3 | 92 | 82 | 79 | 253 |
| 103 | 2 | 80 | 92 | 74 | 246 |
| 103 | 1 | 80 | 82 | 84 | 246 |
| 101 | 3 | 72 | 90 | 81 | 243 |
| 103 | 3 | 100 | 77 | 59 | 236 |
| 102 | 1 | 50 | 80 | 55 | 185 |
+--------+-------+-------+-------+-------+-------+
9 rows in set (0.00 sec)
参考建表语句：
--
-- 表的结构 `student`
--
CREATE TABLE `student` (
`id` int(11) NOT NULL,
`stu_id` int(11) NOT NULL COMMENT '学生id',
`project_id` int(11) NOT NULL COMMENT '课程id',
`times` int(11) NOT NULL COMMENT '第几次考试',
`score` int(11) NOT NULL COMMENT '分数'
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- 转存表中的数据 `student`
--
INSERT INTO `student` (`id`, `stu_id`, `project_id`, `times`, `score`) VALUES
(1, 101, 1, 1, 90),
(2, 101, 2, 1, 80),
(3, 101, 3, 1, 91),
(4, 102, 1, 1, 50),
(5, 102, 2, 1, 80),
(6, 102, 3, 1, 55),
(7, 103, 1, 1, 80),
(8, 103, 2, 1, 82),
(9, 103, 3, 1, 84),
(10, 101, 1, 2, 70),
(11, 101, 2, 2, 100),
(12, 101, 3, 2, 89),
(13, 102, 1, 2, 100),
(14, 102, 2, 2, 81),
(15, 102, 3, 2, 78),
(16, 103, 1, 2, 80),
(17, 103, 2, 2, 92),
(18, 103, 3, 2, 74),
(19, 101, 1, 3, 72),
(20, 101, 2, 3, 90),
(21, 101, 3, 3, 81),
(22, 102, 1, 3, 92),
(23, 102, 2, 3, 82),
(24, 102, 3, 3, 79),
(25, 103, 1, 3, 100),
(26, 103, 2, 3, 77),
(27, 103, 3, 3, 59);
--
-- Indexes for dumped tables
--
--
-- Indexes for table `student`
--
ALTER TABLE `student`
ADD PRIMARY KEY (`id`);
--
-- 在导出的表使用AUTO_INCREMENT
--
--
-- 使用表AUTO_INCREMENT `student`
--
ALTER TABLE `student`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=28;
