udfdemo11.sql

来自「T-SQL示例大全」· SQL 代码 · 共 83 行

/*
文件名称: UDFDemo11.sql
目        的: 建立一个会返回每一个部门薪资最高或倒数前 N 名的多重命令语句数据表值用户自定义函数
*/
USE 北风贸易
GO

if exists (select * from dbo.sysobjects where id = object_id(N'dbo.udf_GetTopNSalary') and xtype in (N'FN', N'IF', N'TF'))
drop function dbo.udf_GetTopNSalary
GO

-- 建立一个会返回每一个部门薪资最高或倒数前 N 名的多重命令语句数据表值用户自定义函数
CREATE FUNCTION dbo.udf_GetTopNSalary
(@TopN int = 3,@TopOrBottom varchar(6) = 'Top')
-- 此一 Table 变量用来存放各部门薪资最高或最低的前几名
RETURNS @SalaryTableVariable TABLE (姓名 nvarchar(10),
                                                                  目前薪资 money,
                                                                  部门 nvarchar(10))
AS
BEGIN

DECLARE @Counter int,@Number int,@StartNo int
DECLARE @Department nvarchar(10)

-- 变量 @Counter 做为计数器
SET @Counter = 0

-- 此一 Table 变量用来存放部门数据
DECLARE @DepartmentTableVariable TABLE
(
编号 int IDENTITY(1,1) NOT NULL,
部门 nvarchar(10)
)

-- 将部门数据存入 Table 变量中
INSERT @DepartmentTableVariable SELECT DISTINCT 部门 FROM 飞狐工作室

-- 变量 @Number 用来储存部门的数目
SELECT @Number = COUNT(*) FROM @DepartmentTableVariable

-- 此一 Table 变量用来存放前 100 PERCENT 的数据
DECLARE @Top100PercentVariable TABLE
(
编号 int IDENTITY(1,1) NOT NULL,
姓名 varchar(10),
目前薪资 money,
部门 varchar(10)
)

-- 此循环能够依次将各个部门薪资最高或最低的前几名存入 Table 变量中
WHILE (@Counter < @Number)
      BEGIN

           SET @Counter = @Counter + 1
           SELECT @Department = 部门 FROM @DepartmentTableVariable WHERE 编号 = @Counter

           IF @TopOrBottom = 'Top'
                INSERT @Top100PercentVariable
                    SELECT 姓名,目前薪资,部门 FROM 飞狐工作室
                        WHERE 部门 = @Department ORDER BY 目前薪资 DESC
           ELSE
                INSERT @Top100PercentVariable
                    SELECT 姓名,目前薪资,部门 FROM 飞狐工作室
                        WHERE 部门 = @Department ORDER BY 目前薪资 ASC

           SET @StartNo = @@ROWCOUNT

           DELETE @Top100PercentVariable WHERE 编号 > @TopN

           INSERT @SalaryTableVariable
               SELECT 姓名,目前薪资,部门 FROM @Top100PercentVariable

           DELETE @Top100PercentVariable

           SET @TopN = @TopN + @StartNo

      END

RETURN
END

GO