关于建立数据库视图表student中有以下：student subject gradestudent1 80studen

关于建立数据库视图
表student中有以下：
student subject grade
student1 80
student1 70
student1 英语 60
student2 90
student2 80
student2 英语 100
.
建立视图v_student,结果为：
student 英语
student1 80 70 60
student1 90 80 100
求具体语句.

balala15 1年前已收到1个回答举报

孤城落晖幼苗

共回答了25个问题采纳率：92% 举报

如果表的STUDENT字段和SUBJECT字段为组合关键字,那么这样
select distinct student,
语文=isnull((select grade from student b where b.student=a.student and b.subject='语文' ),''),
数学=isnull((select grade from student c where c.student=a.student and c.subject='数学' ),''),
英语=isnull((select grade from student d where d.student=a.student and d.subject='英语' ),''),
from student a
否则,这样写更保险：
select distinct student,
语文=isnull((select max(grade) from student b where b.student=a.student and b.subject='语文' ),''),
数学=isnull((select max(grade) from student c where c.student=a.student and c.subject='数学' ),''),
英语=isnull((select max(grade) from student d where d.student=a.student and d.subject='英语' ),''),
from student a

1年前追问

balala15 举报

用create view这种方式来写一下，谢谢了。

举报孤城落晖

CREATE VIEW v_student AS select distinct student, 语文=isnull((select grade from student b where b.student=a.student and b.subject='语文' ),''), 数学=isnull((select grade from student c where c.student=a.student and c.subject='数学' ),''), 英语=isnull((select grade from student d where d.student=a.student and d.subject='英语' ),''), from student a GO 或 CREATE VIEW v_student AS select distinct student, 语文=isnull((select max(grade) from student b where b.student=a.student and b.subject='语文' ),''), 数学=isnull((select max(grade) from student c where c.student=a.student and c.subject='数学' ),''), 英语=isnull((select max(grade) from student d where d.student=a.student and d.subject='英语' ),''), from student a GO

可能相似的问题