本文介紹的實例成功的實現了動態行轉列。下面我以一個簡單的數據庫為例子，說明一下。
數據表結構
這里我用一個比較簡單的例子來說明，也是行轉列的經典例子，就是學生的成績
三張表：學生表、課程表、成績表
學生表
就簡單一點，學生學號、學生姓名兩個字段
| CREATE TABLE `student` ( `stuid` VARCHAR(16) NOT NULL COMMENT '學號', `stunm` VARCHAR(20) NOT NULL COMMENT '學生姓名', PRIMARY KEY (`stuid`))COLLATE='utf8_general_ci'ENGINE=InnoDB; |
課程表
課程編號、課程名
| CREATE TABLE `courses` ( `courseno` VARCHAR(20) NOT NULL, `coursenm` VARCHAR(100) NOT NULL, PRIMARY KEY (`courseno`))COMMENT='課程表'COLLATE='utf8_general_ci'ENGINE=InnoDB; |
成績表
學生學號、課程號、成績
| CREATE TABLE `score` ( `stuid` VARCHAR(16) NOT NULL, `courseno` VARCHAR(20) NOT NULL, `scores` FLOAT NULL DEFAULT NULL, PRIMARY KEY (`stuid`, `courseno`))COLLATE='utf8_general_ci'ENGINE=InnoDB; |
以上就是數據庫表的結構了，這里沒有建立外鍵，但是根據表的結構，可以清楚的看到成績表中的學號和課程號是與學生表、課程表分別關聯起來的。
數據準備
| /*學生表數據*/Insert Into student (stuid, stunm) Values('1001', '張三');Insert Into student (stuid, stunm) Values('1002', '李四');Insert Into student (stuid, stunm) Values('1003', '趙二');Insert Into student (stuid, stunm) Values('1004', '王五');Insert Into student (stuid, stunm) Values('1005', '劉青');Insert Into student (stuid, stunm) Values('1006', '周明');/*課程表數據*/Insert Into courses (courseno, coursenm) Values('C001', '大學語文');Insert Into courses (courseno, coursenm) Values('C002', '新視野英語');Insert Into courses (courseno, coursenm) Values('C003', '離散數學');Insert Into courses (courseno, coursenm) Values('C004', '概率論與數理統計');Insert Into courses (courseno, coursenm) Values('C005', '線性代數');Insert Into courses (courseno, coursenm) Values('C006', '高等數學(一)');Insert Into courses (courseno, coursenm) Values('C007', '高等數學(二)');/*成績表數據*/Insert Into score(stuid, courseno, scores) Values('1001', 'C001', 67);Insert Into score(stuid, courseno, scores) Values('1002', 'C001', 68);Insert Into score(stuid, courseno, scores) Values('1003', 'C001', 69);Insert Into score(stuid, courseno, scores) Values('1004', 'C001', 70);Insert Into score(stuid, courseno, scores) Values('1005', 'C001', 71);Insert Into score(stuid, courseno, scores) Values('1006', 'C001', 72);Insert Into score(stuid, courseno, scores) Values('1001', 'C002', 87);Insert Into score(stuid, courseno, scores) Values('1002', 'C002', 88);Insert Into score(stuid, courseno, scores) Values('1003', 'C002', 89);Insert Into score(stuid, courseno, scores) Values('1004', 'C002', 90);Insert Into score(stuid, courseno, scores) Values('1005', 'C002', 91);Insert Into score(stuid, courseno, scores) Values('1006', 'C002', 92);Insert Into score(stuid, courseno, scores) Values('1001', 'C003', 83);Insert Into score(stuid, courseno, scores) Values('1002', 'C003', 84);Insert Into score(stuid, courseno, scores) Values('1003', 'C003', 85);Insert Into score(stuid, courseno, scores) Values('1004', 'C003', 86);Insert Into score(stuid, courseno, scores) Values('1005', 'C003', 87);Insert Into score(stuid, courseno, scores) Values('1006', 'C003', 88);Insert Into score(stuid, courseno, scores) Values('1001', 'C004', 88);Insert Into score(stuid, courseno, scores) Values('1002', 'C004', 89);Insert Into score(stuid, courseno, scores) Values('1003', 'C004', 90);Insert Into score(stuid, courseno, scores) Values('1004', 'C004', 91);Insert Into score(stuid, courseno, scores) Values('1005', 'C004', 92);Insert Into score(stuid, courseno, scores) Values('1006', 'C004', 93);Insert Into score(stuid, courseno, scores) Values('1001', 'C005', 77);Insert Into score(stuid, courseno, scores) Values('1002', 'C005', 78);Insert Into score(stuid, courseno, scores) Values('1003', 'C005', 79);Insert Into score(stuid, courseno, scores) Values('1004', 'C005', 80);Insert Into score(stuid, courseno, scores) Values('1005', 'C005', 81);Insert Into score(stuid, courseno, scores) Values('1006', 'C005', 82);Insert Into score(stuid, courseno, scores) Values('1001', 'C006', 77);Insert Into score(stuid, courseno, scores) Values('1002', 'C006', 78);Insert Into score(stuid, courseno, scores) Values('1003', 'C006', 79);Insert Into score(stuid, courseno, scores) Values('1004', 'C006', 80);Insert Into score(stuid, courseno, scores) Values('1005', 'C006', 81);Insert Into score(stuid, courseno, scores) Values('1006', 'C006', 82); |
