关于创建视图!!!!!!!

[hongye112928]

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'。
是不是两个批处理之间是没关系的啊??????????
要如何改正这个错误问题呀?????

[lqityga]

有理.

[binshan]

视图不能基于表变量,必须是表.
学习！

[wzh1215]

老大，你怎么能在视图中用外界的表变量呢！！！
把表变量变为表再试试吧！

[zjcxc]

视图不能基于表变量,必须是表.

create table student(学生编号 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)

create table class(课程编号 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)

create table [select](学生编号 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