在項目中遇到一種情況，是需要聯(lián)結(jié)表A和表B沙峻。其中，表A中有一條數(shù)據(jù)两芳，如A_id以及A相關(guān)的數(shù)據(jù)摔寨，而表B中對應(yīng)的數(shù)據(jù)有多條，如B_id, A_id, type, value怖辆，其中用于區(qū)分不同數(shù)據(jù)條目的字段是A_id和type是复。需要用SQL語句，將結(jié)果數(shù)據(jù)集按如下的規(guī)則顯示：
A_id type1_val type2_val type3_val
一個類比的例子是：
有個學生(成績)表student竖螃，里面記錄學生每次考試淑廊、每個科目的成績，要求構(gòu)造出一個結(jié)果集特咆，字段是學生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語句，一則例子
這個語句的作用是菜职，判斷某個字段是否符合某個條件青抛，然后取得相應(yīng)的值，與編程語言的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酬核，相當于將原來的數(shù)據(jù)表GROUP后的結(jié)果蜜另，進行遍歷，如果滿足條件(project_id = 1)的愁茁，則加上score蚕钦，否則加0亭病，最后得出結(jié)果
+--------+-------+-------+-------+-------+-------+
| 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)
參考建表語句：
--
-- 表的結(jié)構(gòu) `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 '分數(shù)'
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- 轉(zhuǎn)存表中的數(shù)據(jù) `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;
