MSSQL：基于一对多关系创建含特定值的高效员工视图

1. 高效创建视图的方案 #

推荐使用**窗口函数ROW_NUMBER()**替代冗余的子查询，性能更优且代码结构清晰。核心逻辑是按「员工+扩展字段」分组，对每组内的有效记录（未过期）按生效日期倒序排序，标记出最新的一条记录，再通过关联和转置得到目标视图。

示例代码 #

CREATE VIEW vw_EmployeeExtendedInfo
AS
WITH LatestEmployeeData AS (
    SELECT 
        ed.EmployeeID,
        d.Name AS FieldName,
        ed.StringValue,
        ed.BooleanValue,
        -- 按员工+扩展字段分组，取最新有效记录（ToDate为NULL或晚于当前日期视为有效）
        ROW_NUMBER() OVER (
            PARTITION BY ed.EmployeeID, d.DataID 
            ORDER BY ed.FromDate DESC
        ) AS RowNum
    FROM EmployeeData ed
    JOIN Data d ON ed.DataID = d.DataID
    WHERE ed.ToDate IS NULL OR ed.ToDate > GETDATE()
)
SELECT 
    e.FirstName,
    e.EmploymentNumber,
    -- 转置扩展字段为视图列
    MAX(CASE WHEN FieldName = 'Title' THEN StringValue END) AS Title,
    MAX(CASE WHEN FieldName = 'Team' THEN StringValue END) AS Team,
    MAX(CASE WHEN FieldName = 'IsManager' THEN BooleanValue END) AS IsManager
    -- 新增扩展字段可在此继续添加CASE语句
FROM Employee e
LEFT JOIN LatestEmployeeData led ON e.EmployeeID = led.EmployeeID AND led.RowNum = 1
GROUP BY e.EmployeeID, e.FirstName, e.EmploymentNumber;

优化优势 #

• 窗口函数的执行效率远高于嵌套子查询，数据库能生成更优的执行计划，尤其适合数据量较大的场景。
• 用CTE（公共表表达式）封装最新数据逻辑，代码可读性和可维护性更强。
• 提前在CTE中过滤过期记录，减少后续关联运算的数据量。

2. 利用Data表Type字段自动选择存储列 #

完全可以通过CASE表达式结合Data.Type字段实现自动匹配对应存储列，根据需求可选择静态视图或动态生成视图两种方式：

方式1：静态视图自动匹配类型（适合固定扩展字段） #

修改CTE部分，新增FieldValue列根据Type自动选取对应存储值，后续转置时再按需转换类型：

CREATE VIEW vw_EmployeeExtendedInfo
AS
WITH LatestEmployeeData AS (
    SELECT 
        ed.EmployeeID,
        d.Name AS FieldName,
        -- 根据Data.Type自动匹配存储列
        CASE d.Type
            WHEN 'String' THEN ed.StringValue
            WHEN 'Boolean' THEN CAST(ed.BooleanValue AS VARCHAR(5))
            WHEN 'Int' THEN CAST(ed.IntValue AS VARCHAR(20))
            -- 新增类型可在此扩展CASE分支
            ELSE NULL
        END AS FieldValue,
        ROW_NUMBER() OVER (
            PARTITION BY ed.EmployeeID, d.DataID 
            ORDER BY ed.FromDate DESC
        ) AS RowNum
    FROM EmployeeData ed
    JOIN Data d ON ed.DataID = d.DataID
    WHERE ed.ToDate IS NULL OR ed.ToDate > GETDATE()
)
SELECT 
    e.FirstName,
    e.EmploymentNumber,
    MAX(CASE WHEN FieldName = 'Title' THEN FieldValue END) AS Title,
    MAX(CASE WHEN FieldName = 'Team' THEN FieldValue END) AS Team,
    -- 转换回布尔类型
    CAST(MAX(CASE WHEN FieldName = 'IsManager' THEN FieldValue END) AS BIT) AS IsManager
FROM Employee e
LEFT JOIN LatestEmployeeData led ON e.EmployeeID = led.EmployeeID AND led.RowNum = 1
GROUP BY e.EmployeeID, e.FirstName, e.EmploymentNumber;

方式2：动态SQL生成视图（适合扩展字段频繁变化） #

如果扩展字段会经常新增，静态视图需要手动维护，可通过存储过程动态生成视图：

CREATE PROCEDURE sp_CreateEmployeeExtendedView
AS
BEGIN
    SET NOCOUNT ON;

    -- 拼接扩展字段的CASE语句
    DECLARE @FieldList NVARCHAR(MAX) = '';
    SELECT @FieldList += ', MAX(CASE WHEN FieldName = ''' + Name + ''' THEN ' + 
        CASE Type
            WHEN 'String' THEN 'FieldValue'
            WHEN 'Boolean' THEN 'CAST(FieldValue AS BIT)'
            WHEN 'Int' THEN 'CAST(FieldValue AS INT)'
            ELSE 'NULL'
        END + ') AS ' + QUOTENAME(Name)
    FROM Data
    WHERE Name IN ('Title', 'Team', 'IsManager'); -- 可按需过滤需要展示的字段

    -- 拼接完整的视图创建SQL
    DECLARE @SQL NVARCHAR(MAX) = '
    CREATE OR ALTER VIEW vw_EmployeeExtendedInfo
    AS
    WITH LatestEmployeeData AS (
        SELECT 
            ed.EmployeeID,
            d.Name AS FieldName,
            CASE d.Type
                WHEN ''String'' THEN ed.StringValue
                WHEN ''Boolean'' THEN CAST(ed.BooleanValue AS VARCHAR(5))
                WHEN ''Int'' THEN CAST(ed.IntValue AS VARCHAR(20))
                ELSE NULL
            END AS FieldValue,
            ROW_NUMBER() OVER (
                PARTITION BY ed.EmployeeID, d.DataID 
                ORDER BY ed.FromDate DESC
            ) AS RowNum
        FROM EmployeeData ed
        JOIN Data d ON ed.DataID = d.DataID
        WHERE ed.ToDate IS NULL OR ed.ToDate > GETDATE()
    )
    SELECT 
        e.FirstName,
        e.EmploymentNumber' + @FieldList + '
    FROM Employee e
    LEFT JOIN LatestEmployeeData led ON e.EmployeeID = led.EmployeeID AND led.RowNum = 1
    GROUP BY e.EmployeeID, e.FirstName, e.EmploymentNumber;';

    -- 执行动态SQL生成视图
    EXEC sp_executesql @SQL;
END;

执行该存储过程即可自动生成包含指定扩展字段的视图，后续新增字段只需更新Data表，重新执行存储过程即可。

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