在数据库TEST中创建test_student(id int,student_name varchar(20),course_name varchar(10),core int)表,现在要创建存储过程sp_get()获取学生考试挂科率以及各个科目的挂科率。
------解决方案--------------------
- SQL code
编写一个存储过程,包含一个输入参数, 指定阶乘最大值,包含一个输出参数,返回阶乘的值。create proc pro @n int,@sum int outputas declare @index int set @index=1 set @sum=1while(@index<[email protected]) begin set @[email protected][email protected] set @[email protected]+1 end declare @sum intexec pro 10,@sum outputselect @sum as 结果为4、编写一个存储过程,统计两个输入参数间能被整除 的整数个数及这些整数的和,两个输入参数,两个输 出参数。 create proc pron @n int,@m int,@sum int output,@total int outputasset @sum=0set @total=0while (@n<[email protected]) begin if @n%13=0 begin set @[email protected][email protected] set @[email protected]+1 set @[email protected]+1 end else set @[email protected]+1 enddeclare @sum int,@total int exec pron 5,1000,@sum output,@total outputselect @sum as 总和,@total as 总个数参考
------解决方案--------------------
学生挂科率,和各个科目挂科率。表达式能贴出来。
------解决方案--------------------
--如果是查询
--学生考试挂科率
select m.id , m.student_name , cast(isnull(n.cnt, 0)*100.0 / m.cnt as decimal(18,2)) [挂科率] from
(select id , student_name , count(1) cnt from test_student group by id , student_name ) m
left join
(select id , student_name , count(1) cnt from test_student where core < 60 group by id , student_name ) n
on m.id = n.id
--各个科目的挂科率
select m.course_name , cast(isnull(n.cnt, 0)*100.0 / m.cnt as decimal(18,2)) [挂科率] from
(select course_name , count(1) cnt from test_student group by course_name) m
left join
(select course_name , count(1) cnt from test_student where core < 60 group by course_name) n
on m.course_name = n.course_name
--如果是要存储过程
--学生考试挂科率
create procedure my_proc1 as
begin
select m.id , m.student_name , cast(isnull(n.cnt, 0)*100.0 / m.cnt as decimal(18,2)) [挂科率] from
(select id , student_name , count(1) cnt from test_student group by id , student_name ) m
left join
(select id , student_name , count(1) cnt from test_student where core < 60 group by id , student_name ) n
on m.id = n.id
end
--各个科目的挂科率
crete procedure my_proc2 as
begin
select m.course_name , cast(isnull(n.cnt, 0)*100.0 / m.cnt as decimal(18,2)) [挂科率] from
(select course_name , count(1) cnt from test_student group by course_name) m
left join
(select course_name , count(1) cnt from test_student where core < 60 group by course_name) n
on m.course_name = n.course_name
end