创建含IF逻辑的SQL View报错，求合并中间表查询方案

错误原因 #

SQL Server的视图定义只能包含单一的SELECT查询语句，不允许包含IF、CREATE TABLE、DROP TABLE这类DDL/DML或流程控制操作，这就是你之前尝试各种写法都报错的核心原因。

解决方案 #

直接将中间表Test_T的聚合逻辑嵌入主查询，用CTE（公共表表达式）或子查询替代中间表，即可创建合法的视图。以下是两种可行写法：

写法1：使用CTE整合中间表逻辑 #

CREATE VIEW Test_Data AS
WITH UserTypeAgg AS (
    -- 原Test_T的聚合逻辑，转换为CTE
    SELECT 
        pb.user_pin AS PerID,
        MAX(CASE WHEN pb.user_type = '4' THEN pb.user_type ELSE 0 END) AS PerF,
        MAX(CASE WHEN pb.user_type = '6' THEN pb.user_type ELSE 0 END) AS PerP
    FROM User_Data pb
    GROUP BY pb.user_pin
)
SELECT DISTINCT TOP 100 PERCENT  -- 必须加TOP才能在视图中使用ORDER BY
    people.pin AS [User ID],
    people.last_name,
    people.name,
    dept.name,
    -- 修正原Level字段的子查询关联逻辑，避免错误
    STUFF((
        SELECT ', ' + lvl.name 
        FROM people_lvl 
        JOIN lvl ON people_lvl.id = lvl.id
        WHERE people_lvl.pin = people.pin 
        FOR XML PATH('')
    ), 1, 2, '') AS [Level],
    CASE UserTypeAgg.PerF WHEN 4 THEN 1 ELSE 0 END AS [PerF],
    CASE UserTypeAgg.PerP WHEN 6 THEN 1 ELSE 0 END AS [PerP]
FROM people 
LEFT JOIN dept ON people.dept_id = dept.id
LEFT JOIN people_lvl ON people.lvl_id = people_lvl.id
LEFT JOIN UserTypeAgg ON UserTypeAgg.PerID = people.pin
ORDER BY people.pin

写法2：直接在CASE中嵌入子查询（无需CTE） #

CREATE VIEW Test_Data AS
SELECT DISTINCT TOP 100 PERCENT
    people.pin AS [User ID],
    people.last_name,
    people.name,
    dept.name,
    STUFF((
        SELECT ', ' + lvl.name 
        FROM people_lvl 
        JOIN lvl ON people_lvl.id = lvl.id
        WHERE people_lvl.pin = people.pin 
        FOR XML PATH('')
    ), 1, 2, '') AS [Level],
    -- 直接计算PerF：判断该用户是否存在user_type=4的记录
    CASE 
        WHEN EXISTS(SELECT 1 FROM User_Data pb WHERE pb.user_pin = people.pin AND pb.user_type = '4') 
        THEN 1 ELSE 0 
    END AS [PerF],
    -- 直接计算PerP：判断该用户是否存在user_type=6的记录
    CASE 
        WHEN EXISTS(SELECT 1 FROM User_Data pb WHERE pb.user_pin = people.pin AND pb.user_type = '6') 
        THEN 1 ELSE 0 
    END AS [PerP]
FROM people 
LEFT JOIN dept ON people.dept_id = dept.id
LEFT JOIN people_lvl ON people.lvl_id = people_lvl.id
ORDER BY people.pin

关键说明 #

1. 视图中的ORDER BY必须配合TOP 100 PERCENT或OFFSET 0 ROWS使用，否则SQL Server会抛出语法错误。
2. 原查询中Level字段的子查询存在关联逻辑问题，已修正为通过people_lvl关联lvl表，避免无意义的笛卡尔积。
3. 写法2的逻辑更简洁，直接通过EXISTS判断用户是否存在指定类型的记录，性能可能比写法1更优（无需额外聚合）。

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