关于创建视图!!!!!!!
declare @student table(学生编号 char(10),学生姓名 char(2) , 性别 char(2),年龄 int)
insert @student values('01101101','1','男',22)
insert @student values('01101103','2','女',22)
insert @student values('01101104','3','男',22)
insert @student values('01101105','4','男',22)
insert @student values('01101106','6','女',22)
insert @student values('01101107','7','男',22)
insert @student values('01101108','8','男',22)
insert @student values('01101109','9','男',22)
insert @student values('011011010','10','女',22)
declare @class table(课程编号 char(2), 课程名称 char(10) , 课程性质 char(6) , 课程常数 int, 周学时 int)
insert @class values('c1','英语','考试课',1.1,3)
insert @class values('c2','汇编语言','考试课',1.1,5)
insert @class values('c3','delphi','考查课',0.9,4)
declare @select table(学生编号 char(8),课程编号 char(2),成绩 int)
insert @select values('01101101','c1',88)
insert @select values('01101101','c2',78)
insert @select values('01101101','c3',89)
insert @select values('01101103','c1',85)
insert @select values('01101103','c2',76)
insert @select values('01101103','c3',71)
insert @select values('01101104','c1',76)
insert @select values('01101104','c2',86)
insert @select values('01101104','c3',73)
insert @select select '01101105','c1',78
union all select '01101105','c2',78
union all select '01101105','c3',85
union all select '01101106','c1',81
union all select '01101106','c2',88
union all select '01101106','c3',98
union all select '01101107','c1',69
union all select '01101107','c2',85
union all select '01101107','c3',86
go
create view your
as
select a.学生编号,
isnull(
(sum(case when c.课程性质 = '考试课'then b.成绩 end) *1.1 + sum(case when c.课程性质 = '考查课'then b.成绩 end) * 0.9 ) /
(sum(case when c.课程性质 = '考试课'then c.周学时 end) *1.1*20 + sum(case when c.课程性质 = '考查课'then c.周学时 end) * 0.9*20),0) 智育分
from @student a left join @select b on a.学生编号 = b.学生编号 left join @class c on b.课程编号 = c.课程编号
group by a.学生编号
go
我在这里建立视图,可是有错误:必须声明变量 '@student'。
是不是两个批处理之间是没关系的啊??????????
要如何改正这个错误问题呀?????