SQLServer 标量值函数定义和使用实例 - 度量快速开发平台-专业、快速的软件定制快开平台

--自定义函数
--名次查询
USE [yuejuan105]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--获取考试成绩名次。
CREATE FUNCTION [dbo].[GetRankNum] (
@ExamID varchar(30) , --考试批次
@GradeID varchar(30), --年级ID
@ClassID varchar(30), --班级ID
@SubjectID varchar(30), --科目ID
@TestScores varchar(30), --考试分数
@Type int --查询类型（1:科目班级名次、2:科目年级名次、3:总分班级名次、4:总分年级名次）
)
RETURNS integer AS
begin
declare
@intVac int --返回名次
--科目班级名次
if(@Type=1)
begin
Select @intVac = Count(1) FROM T_CJ Where 1=1
AND BJ=@ClassID
AND NJ=@GradeID
AND KSID = @ExamID
AND KM = @SubjectID
AND ZF > @TestScores
end
--科目年级名次
else if(@Type=2)
begin
Select @intVac = Count(1) FROM T_CJ Where 1=1
AND NJ=@GradeID
AND KSID = @ExamID
AND KM = @SubjectID
AND ZF > @TestScores
end
--总分班级名次
else if(@Type=3)
begin
Select @intVac = Count(1) FROM T_CJ Where 1=1
AND BJ=@ClassID
AND NJ=@GradeID
AND KSID = @ExamID
AND ZF > @TestScores
end
--总分年级名次
else if(@Type=4)
begin
Select @intVac = Count(1) FROM T_CJ Where 1=1
AND NJ=@GradeID
AND KSID = @ExamID
AND ZF > @TestScores
end
else
begin
Select @intVac =-1
end
--返回名次
return @intVac+1
end
GO
--调用标量值函数-班年科目查询
Select
A.CJID ,
A.XH,
A.XSXM,
A.NJ,
A.BJ,
A.KM,
ZF,
B.ClassAVG,
C.GradeAVG,
dbo.GetRankNum(A.KSID,A.NJ,A.BJ,A.KM,A.ZF,1) AS ClassNum,
dbo.GetRankNum(A.KSID,A.NJ,A.BJ,A.KM,A.ZF,2) AS GradeNum
FROM dbo.T_CJ AS A
Left join
(
Select
KM,KSID,BJ,NJ,avg(ZF) as ClassAVG FROM T_CJ
Group by KM,KSID,BJ,NJ
) AS B ON
A.km=b.km
and a.nj=b.nj
and a.bj=b.bj
and a.ksid=b.ksid
Left Join
(
Select
KM,KSID,NJ,avg(ZF) as GradeAVG FROM T_CJ
Group by KM,KSID,NJ
) AS C ON
A.km=C.km
and a.nj=C.nj
and a.ksid=C.ksid
Where 1=1

复制代码