关于创建视图!!!!!!! 老大,你怎么能在视图中用外界的表变量呢!!!把表变量变为表再试试吧! 解决方案 » 免费领取超大流量手机卡,每月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 视图不能基于表变量,必须是表.学习! 大侠~帮忙啊~函数取值问题!! 新手弱弱的求一个查询。 如何取得Datatime类弄的日期部份 Business Intelligence Development Studio快捷方式失效 money转换为varchar时出问题了. 多表内连去掉重复列问题! 百万级]通用存储过程.分页存储过程. 调用问题。。。。。。。。。。。。。。。。。。。。 字符串长度问题 一个感觉很简单的SQL却写不出来,请帮忙.. 在XP下能否建立SQL SERVER2000的服务器版 如何把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
学习!