数据库 基础学习8— 横标与纵表转换
一、纵表转横表：
1 create table Student--学生表 2 ( 3 Sno char(3) primary key not null,--学号（主键） 4 Sname char(8) not null,--学生姓名 5 Ssex char(2) not null,--学生性别 6 Sbirthday datetime,--学生出生年月 7 Class char(5)--学生所在班级 8 ) 9 go 10 create table Teacher--教师表 11 ( 12 Tno char(3) not null primary key,--教工编号（主码） 13 Tname Char(4) not null,--教工姓名 14 Tsex Char(2) not null,--教工性别 15 Tbirthday datetime check(Tbirthday>'1900-1-1'),--教工出生年月 16 Prof Char(6),--职称 17 Depart Varchar(10) not null--教工所在部门 18 ) 19 go 20 create table Course--课程表 21 ( 22 Cno char(5) primary key not null,--课程号（主键） 23 Cname varchar(10) not null,--课程名称 24 Tno char(3) not null references Teacher(Tno)--教工编号（外键） 25 ) 26 go 27 create table Score--成绩表 28 ( 29 primary key (Sno,Cno), 30 Sno char(3) not null references Student(Sno),--学号（外键） 31 Cno char(5) not null references Course(Cno),--课程号（外键） 32 Degree decimal(4,1)--成绩 33 ) 34 insert into Student values('108','曾华','男','1977-09-01','95033') 35 insert into Student values('105','匡明','男','1975-10-02','95031') 36 insert into Student values('107','王丽','女','1976-01-23','95033') 37 insert into Student values('101','李军','男','1976-02-20','95033') 38 insert into Student values('109','王芳','女','1975-02-10','95031') 39 insert into Student values('103','陆君','男','1974-06-03','95031') 40 41 update Student set Class='95031' where Sno='103' 42 update Student set Class='95033' where Sno='108' 43 update Student set Class='95031' where Sno='109' 44 update Student set Class='95031' where Sno='105' 45 46 47 select *from Student 48 49 insert into Teacher values('804', '李诚', '男', '1958-12-02', '副教授', '计算机系') 50 insert into Teacher values('856', '张旭', '男', '1969-03-12', '讲师', '电子工程系') 51 insert into Teacher values('825', '王萍', '女', '1972-05-05', '助教', '计算机系') 52 insert into Teacher values('831', '刘冰', '女', '1977-08-14', '助教', '电子工程系') 53 54 select *from Teacher 55 56 insert into Course values('3-105','计算机导论','825') 57 insert into Course values('3-245','操作系统','804') 58 insert into Course values('6-166','数字电路','856') 59 insert into Course values('9-888','高等数学','831') 60 61 select *from Course 62 63 insert into Score values('103', '3-245', 86) 64 insert into Score values('105', '3-245', 75) 65 insert into Score values('109', '3-245', 68) 66 insert into Score values('103', '3-105', 92) 67 insert into Score values('105', '3-105', 88) 68 insert into Score values('109', '3-105', 76) 69 insert into Score values('101', '3-105', 64) 70 insert into Score values('107', '3-105', 91) 71 insert into Score values('108', '3-105', 78) 72 insert into Score values('101', '6-166', 85) 73 insert into Score values('107', '6-166', 79) 74 insert into Score values('108', '6-166', 81) 75 76 insert into Score values('103', '6-166', 81) 77 insert into Score values('105', '6-166', 81) 78 insert into Score values('109', '6-166', 81) 79 80 delete from Score where Sno=109 and cno='6-166' 81 82 83 select COUNT(*) from Score 84 85 select *from Score 86 update Score set degree='64' where Sno=101 and cno='3-105' 87 update Score set degree='85' where Sno=101 and cno='6-166' 88 delete from Score where Sno=107 and cno='6-166'
select * from Score
纵表结构
sno cno degree
101 3-105 64.0
101 6-166 85.0
103 3-105 92.0
103 3-245 86.0
105 3-105 88.0
105 3-245 75.0
107 3-105 91.0
107 6-166 79.0
108 3-105 78.0
108 6-166 81.0
109 3-105 76.0
109 3-245 68.0
转换为 横表
方法
select sno 学号 ，
sum(case cno when '3-105' then degree else 0 end) as 计算机导论，
sum(case cno when '6-166' then degree else 0 end) as 数字电路，
sum(case cno when '3-245' then degree else 0 end) as 操作系统
from score group by sno
横表结构
学号 计算机导论 数字电路 操作系统
101 64.0 85.0 0.0
103 92.0 0.0 86.0
105 88.0 0.0 75.0
107 91.0 79.0 0.0
108 78.0 81.0 0.0
109 76.0 0.0 68.0
横表转纵表 方法
SELECT sno,'3-105' AS cno,计算机导论 AS degree FROM Score UNION ALL
SELECT sno,'6-166' AS cno,数字电路 AS degree FROM Score UNION ALL
SELECT sno,'3-245' AS cno,操作系统 AS degree FROM Score
ORDER BY Sno,Cno DESC;
