我是SQL的新手,并且正在学习用户定义的sql
中的功能我有两个桌子,我给了我插入这些桌子的行。
--Table1
create table sql_exam(
exa_examid bigint not null primary key,
exa_name varchar(100) not null,
exa_maxmark decimal(5,2) not null,
exa_minmarkreqdforpass decimal(5,2) not null,
exa_examscheduletime datetime not null
)
--Rows inserted into Table1
insert into sql_exam(exa_examid,exa_name,exa_maxmark,exa_minmarkreqdforpass,exa_examscheduletime) values (1,'Maths',100,40,'2012-10-10 10:00')
insert into sql_exam(exa_examid,exa_name,exa_maxmark,exa_minmarkreqdforpass,exa_examscheduletime) values (2,'English',75,35,'2012-10-11 10:00')
--Table2
create table sql_studentmarks(
stm_studentid int not null primary key,
stm_examid bigint foreign key references sql_exam(exa_examid),
stm_mark decimal(5,2)
)
--Rows inserted into Table2
insert into sql_studentmarks(stm_studentid,stm_examid,stm_mark) values (1,1,80)
insert into sql_studentmarks(stm_studentid,stm_examid,stm_mark) values (2,1,90)
insert into sql_studentmarks(stm_studentid,stm_examid,stm_mark) values (3,1,40)
insert into sql_studentmarks(stm_studentid,stm_examid,stm_mark) values (1,2,70)
insert into sql_studentmarks(stm_studentid,stm_examid,stm_mark) values (2,2,60)
insert into sql_studentmarks(stm_studentid,stm_examid,stm_mark) values (3,2,17)
我需要帮助创建标量功能的帮助,我需要获得
- 标量功能将返回"数学"中获得最高分数的学生ID
- 表格功能将返回学生ID并获得最高分数的学生获得的标记。
只是想学习SQL。我尝试了 - "
create function fnGetMathsHightest()
returns int
as
begin
declare @st_id int
return @st_id
end
select dbo.fnGetMathsHightest()
from sql_studentmarks
where stm_examid=1
group by stm_studentid
having stm_mark=max(stm_mark)
第一个。它看起来不好。
它看起来像是1。
CREATE FUNCTION ssfnGetStudenytId
(
-- Add the parameters for the function here
)
RETURNS int
AS
BEGIN
declare @vId as int
set @vId = Select stm_studentid from sql_studentmarks where stm_mark = (SELECT MAX( stm_mark )from sql_studentmarks )
RETURN @vId
END