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