解决方案 »
- sp_addextendedproperty 插入描述的问题,非常急!!!
- 库存管理用"管理系统"了,还有必要维护一个手工帐本吗?
- server sql2005数据导入到Mysql中
- 请问这个存储过程如何实现多表联合查询?
- SQl 2005 入门级问题
- 删除记录大问题!!!!!!!!!
- 触发器中调用要存储过程并要接收存储过程中的返回值,返回值类型为float,不知道触发器中如何接收!!!
- SQL Server 7.0 + Win2000 advanced server的问题!
- 关于导入数据,大侠帮忙
- oracle LOB 初探
- 添加成功.为什么.SQL数据里.找不到刚刚添加的东西
- 紧急求助:Logon failed for login "sa" due to trigger execution!!!
create table student(
sno int,--学号
sname varchar(15),--姓名
sdept varchar(10),--班级
sage int --年龄
)
create table course (
cno int,--课程号
cname varchar(10) --课程名)
create table course_stu
(
sno int,--学号
cno int,--课程号
sgrade decimal(9,3)--成绩)
--向student表里面插入数据
insert into student values(1,'李1','06web1',20)
insert into student values(2,'李2','06web1',19 )
insert into student values(3,'李3','06web1',21 )
insert into student values(4,'李4','06web2',18 )
insert into student values(5,'李5','06web2',21 )--向course表里面插入数据
insert into course values(1,'数据结构')
insert into course values(2,'java')
insert into course values(4,'SQL Server')
insert into course values(3,'操作系统')
--向course_stu表里面插入数据
insert into course_stu values (1,1,60.0 )
insert into course_stu values (1,2,67.0 )
insert into course_stu values (1,3,68.0 )
insert into course_stu values (1,4,75.0 )
insert into course_stu values (2,1,78.0 )
insert into course_stu values (2,2,89.0 )
insert into course_stu values (2,3,68.0 )
insert into course_stu values (2,4,65.0 )
insert into course_stu values (3,1,72.0 )
insert into course_stu values (3,2,77.0 )
insert into course_stu values (3,3,78.0 )
insert into course_stu values (3,4,85.0 )
insert into course_stu values (4,1,75.0 )
insert into course_stu values (4,2,86.0 )
insert into course_stu values (4,3,79.0 )
insert into course_stu values (4,4,69.0 )
go
--创建存储过程
create procedure getavggrade as
begin
SET NOCOUNT ON; select a.sname,avg(b.sgrade) from student a inner join course_stu b on a.sno=b.sno group by a.sname
end
go
exec getavggrade
go
drop procedure getavggrade
drop table student,course,course_stu
/*
sname
--------------- ---------------------------------------
李1 67.500000
李2 75.000000
李3 78.000000
李4 77.250000*/