查询学院不同年份，不同月份，各专业省级、国家级竞赛获奖总人数，各学院的省级获奖情况，各学院的国家级获奖情况，学校获奖省级总人数，学校获奖国家级总人数。
create procedure proc_include(in syear int,in date_begin int,in date_end int)
delimiter $$
begin
declare s_totalnumber int;
declare g_totalnumber int;
select ('学院各专业省级、国家级竞赛获奖总人数')
select college.name,pgrade,major.majorname,COUNT(pgrade) as num
from team,componentinfo,student,major,college
where team.teamid = componentinfo.teamid
and componentinfo.stid = student.stid
and student.majorkey = major.majorkey
and major.collegekey = college.collegekey
and year(ptime) = syear
and month(ptime)>date_begin
and month(ptime)<date_end
group by college.name,majorname,pgrade
having pgrade <> 'NULL'
order by num
select ('各学院的省级获奖情况')
select college.name,pgrade,COUNT(pgrade) as num
from team,componentinfo,student,major,college
where team.teamid = componentinfo.teamid
and componentinfo.stid = student.stid
and student.majorkey = major.majorkey
and major.collegekey = college.collegekey
and year(ptime) = syear
and month(ptime)>date_begin
and month(ptime)<date_end
group by college.name,pgrade
having pgrade <> 'NULL' and pgrade <> '国家级'
select ('各学院的国家级获奖情况')
select college.name,pgrade,COUNT(pgrade) as num
from team,componentinfo,student,major,college
where team.teamid = componentinfo.teamid
and componentinfo.stid = student.stid
and student.majorkey = major.majorkey
and major.collegekey = college.collegekey
and year(ptime)=syear
and month(ptime)>date_begin
and month(ptime)<date_end
group by college.name,pgrade
having pgrade <> 'NULL' and pgrade <> '省级'
select g_totalnumber = COUNT(pgrade)
from team
where year(ptime) = syear
and month(ptime)>date_begin
and month(ptime)<date_end
and pgrade <> 'NULL' and pgrade <> '省级'
select s_totalnumber = COUNT(pgrade)
from team
where year(ptime) = syear
and month(ptime)>date_begin
and month(ptime)<date_end
and pgrade <> 'NULL' and pgrade <> '国家级'
select('学校获奖省级总人数：')
select s_totalnumber
select ('学校获奖国家级总人数：')
select g_totalnumber;
end
$$
delimiter;
