如何将带参数的员工末次培训T-SQL查询改写为可外部过滤的视图

问题背景 #

你需要实现一个可以按指定截止日期查询每位员工最后一次培训记录的视图，原带参数的T-SQL逻辑可以正确返回结果，但无法直接改造为普通视图使用，以下是具体解决方案。

首先说明直接改造原逻辑为普通视图的问题 #

你原有的逻辑是先按截止日期过滤培训记录，再为每个员工计算排名取最新值，如果直接删除参数把排名逻辑写死在视图里，外层的WHERE条件是在视图计算完成后才执行的，过滤顺序颠倒会导致结果不符合预期：

-- 错误的视图写法，无法得到正确结果
CREATE VIEW WrongView
AS
SELECT *
FROM (
    SELECT t.*
        , RANK() OVER (PARTITION BY employeeNo ORDER BY dateStart DESC, timeStart DESC) rn 
    FROM Training t
) v
WHERE rn = 1 

调用SELECT * FROM WrongView WHERE dateStart <= '2020-12-31'时，员工0001的rn=1的记录是2021年的DotNET培训，会被过滤掉，结果中不会返回员工0001的2020年MSOffice培训记录，不符合需求。

可行解决方案 #

方案1：使用内联表值函数（最推荐，性能最优） #

这是最贴合你需求的实现，执行效率和原生查询一致，调用方式接近视图：

CREATE FUNCTION dbo.GetLatestTrainingByCutoffDate (@cutoffDate DATE)
RETURNS TABLE
AS
RETURN
(
    SELECT employeeNo, trainingCourse, dateStart, timeStart
    FROM (
        SELECT t.*
            , RANK() OVER (PARTITION BY employeeNo ORDER BY dateStart DESC, timeStart DESC) rn 
        FROM Training t
        WHERE dateStart <= @cutoffDate
    ) v
    WHERE rn = 1 
);

调用方式：

SELECT * FROM dbo.GetLatestTrainingByCutoffDate('2020-12-31')

方案2：纯视图实现（适合培训表数据量较小的场景） #

如果必须使用SELECT * FROM 视图 WHERE 条件的调用格式，可以通过预生成所有截止日期的匹配结果实现：

CREATE VIEW myView
AS
SELECT 
    cutoff.cutoffDate,
    t.employeeNo,
    t.trainingCourse,
    t.dateStart AS trainingStartDate,
    t.timeStart
FROM (
    -- 取所有培训日期作为可选的截止日期
    SELECT DISTINCT dateStart AS cutoffDate FROM Training
) cutoff
CROSS APPLY (
    SELECT *
    FROM (
        SELECT t_inner.*
            , RANK() OVER (PARTITION BY t_inner.employeeNo ORDER BY t_inner.dateStart DESC, t_inner.timeStart DESC) rn
        FROM Training t_inner
        WHERE t_inner.dateStart <= cutoff.cutoffDate
    ) v
    WHERE rn = 1
) t

调用方式和你要求的完全匹配：

SELECT * FROM myView WHERE cutoffDate <= '2020-12-31'

注意事项 #

• 如果员工存在同一天同一时间参加多个培训的情况，RANK()会返回多条并列第一的记录，如果只需要取任意一条，替换为ROW_NUMBER()即可。
• 建议给Training表的employeeNo、dateStart、timeStart加联合索引，可大幅提升查询性能。

内容的提问来源于stack exchange，提问作者Lim