关于创建视图!!!!!!! 老大,你怎么能在视图中用外界的表变量呢!!!把表变量变为表再试试吧! 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 视图不能基于表变量,必须是表.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',78union all select '01101105','c2',78union all select '01101105','c3',85union all select '01101106','c1',81union all select '01101106','c2',88union all select '01101106','c3',98union all select '01101107','c1',69union all select '01101107','c2',85union all select '01101107','c3',86gocreate view yourasselect 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 视图不能基于表变量,必须是表.学习! 求SQL2000下的一个SQL函数写法 请问如何生成如下的视图? 对已经存在的表创建【聚集索引】和【非聚集索引】表中的数据会不会重新调整物理地址? sql2005 端口问题 求一条更新语句! 高手帮忙找错误!!急!!!!!!!!!! 请问如何将两台服务器上的数据库联合做查询? 连接sqlserver2008时报错 如何指定一个表的所有者(Owner)?? SQL语句 查找一个表中相同的记录 如何把smalldatetime类型转换为一个整数? 怎样将存储过程返回的表作为select from 的表?
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
学习!