这个存储过程该如何写?
create table student(
sid int primary key not null,
sname varchar(20),
grade decimal(10,2),
)
create table class(
id int primary key not null,
cid int,
cname varchar(20)
)
create table student_class(
id int primary key not null,
sid int not null,
cid int not null
)
create table grade_percent(
id int primary key not null,
cid int,
percent decimal(10,2)
)
select (((isnull(t2.count,0))*100)/t1.count) percent,t1.cid from
(
select count(distinct(sc.sid)) count,c.cid from student s
join student_class sc on s.sid = sc.sid
join class c on sc.cid = c.cid
group by c.cid
) t1
left join
(
select count(distinct(sc.sid)) count,c.cid from student s
join student_class sc on s.sid = sc.sid
join class c on sc.cid = c.cid
where s.grade > 90
group by c.cid
) t2
on t1.cid = t2.cid
--- 说明
--- student,class表多对多,求各个班级成绩在90分以上的人数比例,插入到grade_percent表如下图
grade_percent表
----------------------
id cid percent
----------------------
1 1 80
2 2 67.7
3 3 70
上面这个存储过程如何写?
如果输出集合存储过程怎么写
------解决方案--------------------create table grade_percent(
id int primary key not null,
cid int,
percent decimal(10,2)
)
--->这个表不需要,统计出来的,删除掉
select cid,cast(cast((More90*1.0/total)*100 as numeric(5,2)) as varchar)+'%' as [percent]
from
(
select a.cid,sum(case when b.grade>=90 then 1 else 0 end) as More90,count(*) as total