CREATE TABLE T_Class(
class_id   int IDENTITY(1,1) PRIMARY KEY,
class_name varchar(20),
class_code varchar(20)
)

CREATE TABLE T_Exam
(
exam_id int IDENTITY(1,1) PRIMARY KEY,
student varchar(20),
class_id int FOREIGN KEY REFERENCES t_class(class_id),
class_mark decimal(4,2)
)

INSERT INTO t_class(class_name,class_code)
SELECT '语文','yuwen'
UNION ALL 
SELECT '数学','shuxue'
UNION ALL 
SELECT '英语','yingyu'

INSERT INTO T_Exam(student,class_id,class_mark)
SELECT 'A同学',1,80
UNION ALL 
SELECT 'A同学',2,90
UNION ALL 
SELECT 'B同学',2,85
UNION ALL 
SELECT 'B同学',3,75
UNION ALL 
SELECT 'C同学',1,85
UNION ALL 
SELECT 'C同学',3,95
go

CREATE VIEW test
AS 
SELECT a.student AS 'student(学生名字)','yuwen(语文课程代码)'=isnull(MAX(CASE WHEN a.class_id=1 THEN CONVERT(varchar(10),class_mark) END ),'--')
,'shuxue(数学课程代码)'=isnull(MAX(CASE WHEN a.class_id=2 THEN CONVERT(varchar(10),class_mark) END ),'--')
,'yingyu(英语课程代码)'=isnull(MAX(CASE WHEN a.class_id=3 THEN CONVERT(varchar(10),class_mark) END ),'--')
 FROM t_exam a LEFT JOIN T_Class b ON A.class_id=b.class_id
 GROUP BY a.student
 
 SELECT * FROM test
 
 /*
 student(学生名字)        yuwen(语文课程代码) shuxue(数学课程代码) yingyu(英语课程代码)
-------------------- ------------- -------------- --------------
A同学                  80.00         90.00          --
B同学                  --            85.00          75.00
C同学                  85.00         --             95.00
警告: 聚合或其他 SET 操作消除了 Null 值。

(3 行受影响)


 */