• 全部
• 基础类
• 应用实例
• 新技术前沿

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

hongye112928 2003-12-14 10:51:38
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

...全文
7 点赞 收藏 4

4 条回复

zjcxc 元老 2003-12-14

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

wzh1215 2003-12-14

binshan 2003-12-14

lqityga 2003-12-14

MS-SQL Server

3.3w+

MS-SQL Server相关内容讨论专区

2003-12-14 10:51