如何优化并简化多候选条件的SQL查询语句？

原始问题场景 #

现有一段SQL查询，通过UNION ALL合并三个候选者的查询结果，每个分支都包含完全相同的多表LEFT JOIN逻辑，仅筛选条件和Candidate标识不同。这种写法冗余度高，维护时容易因漏改某一分支的JOIN逻辑导致错误，希望简化为仅编写一次JOIN逻辑，后续分支复用关联结果，同时保证输出列一致。

原始冗余SQL代码 #

DROP TABLE IF EXISTS #TempTable1

SELECT 'Candidate1' AS 'Candidate', A.DisplayName, A.Location, A.AccountId, F.Title, F.Body, F.Tags
INTO #TempTable1
FROM [Users] A
LEFT JOIN [Badges] B ON A.Id = B.Id
LEFT JOIN [Comments] C ON A.Id = C.Id AND C.UserId = B.UserId
LEFT JOIN [LinkTypes] D ON A.Id = D.Id
LEFT JOIN [PostLinks] E ON A.Id = E.Id AND E.PostId = C.PostId
LEFT JOIN [Posts] F ON A.Id = F.Id
LEFT JOIN [PostTypes] G ON A.Id = G.Id
LEFT JOIN [Votes] H ON A.Id = H.Id AND H.UserId = B.UserId AND H.PostId = E.PostId
LEFT JOIN [VoteTypes] I ON A.Id = I.Id
WHERE A.DisplayName LIKE 'Jeff%'AND F.Title = 'Director'

UNION ALL

SELECT 'Candidate2' AS 'Candidate', A.DisplayName, A.Location, A.AccountId, F.Title, F.Body, F.Tags
FROM [Users] A
LEFT JOIN [Badges] B ON A.Id = B.Id
LEFT JOIN [Comments] C ON A.Id = C.Id AND C.UserId = B.UserId
LEFT JOIN [LinkTypes] D ON A.Id = D.Id
LEFT JOIN [PostLinks] E ON A.Id = E.Id AND E.PostId = C.PostId
LEFT JOIN [Posts] F ON A.Id = F.Id
LEFT JOIN [PostTypes] G ON A.Id = G.Id
LEFT JOIN [Votes] H ON A.Id = H.Id AND H.UserId = B.UserId AND H.PostId = E.PostId
LEFT JOIN [VoteTypes] I ON A.Id = I.Id
WHERE A.DisplayName LIKE 'ANA%' AND A.AccountId = '3125156'

UNION ALL

SELECT 'Candidate3' AS 'Candidate', A.DisplayName, A.Location, A.AccountId, F.Title, F.Body, F.Tags
FROM [Users] A
LEFT JOIN [Badges] B ON A.Id = B.Id
LEFT JOIN [Comments] C ON A.Id = C.Id AND C.UserId = B.UserId
LEFT JOIN [LinkTypes] D ON A.Id = D.Id
LEFT JOIN [PostLinks] E ON A.Id = E.Id AND E.PostId = C.PostId
LEFT JOIN [Posts] F ON A.Id = F.Id
LEFT JOIN [PostTypes] G ON A.Id = G.Id
LEFT JOIN [Votes] H ON A.Id = H.Id AND H.UserId = B.UserId AND H.PostId = E.PostId
LEFT JOIN [VoteTypes] I ON A.Id = I.Id
WHERE A.DisplayName LIKE 'Peter%' AND A.Location = 'USA'

SELECT * FROM #TempTable1

修正后的可行简化方案 #

你期望的简化写法存在语法问题（SELECT *会包含Users表的所有列，与第一个分支的输出列数/列名不匹配），以下是修正后的优化版本：

DROP TABLE IF EXISTS #TempTable1

-- 用CTE封装通用的多表JOIN逻辑，仅编写一次
WITH UserJoinedData AS (
    SELECT 
        A.DisplayName, 
        A.Location, 
        A.AccountId, 
        F.Title, 
        F.Body, 
        F.Tags
    FROM [Users] A
    LEFT JOIN [Badges] B ON A.Id = B.Id
    LEFT JOIN [Comments] C ON A.Id = C.Id AND C.UserId = B.UserId
    LEFT JOIN [LinkTypes] D ON A.Id = D.Id
    LEFT JOIN [PostLinks] E ON A.Id = E.Id AND E.PostId = C.PostId
    LEFT JOIN [Posts] F ON A.Id = F.Id
    LEFT JOIN [PostTypes] G ON A.Id = G.Id
    LEFT JOIN [Votes] H ON A.Id = H.Id AND H.UserId = B.UserId AND H.PostId = E.PostId
    LEFT JOIN [VoteTypes] I ON A.Id = I.Id
)
-- 基于CTE编写各候选者的筛选逻辑
SELECT * INTO #TempTable1 FROM (
    SELECT 'Candidate1' AS Candidate, DisplayName, Location, AccountId, Title, Body, Tags
    FROM UserJoinedData
    WHERE DisplayName LIKE 'Jeff%' AND Title = 'Director'

    UNION ALL

    SELECT 'Candidate2' AS Candidate, DisplayName, Location, AccountId, Title, Body, Tags
    FROM UserJoinedData
    WHERE DisplayName LIKE 'ANA%' AND AccountId = '3125156'

    UNION ALL

    SELECT 'Candidate3' AS Candidate, DisplayName, Location, AccountId, Title, Body, Tags
    FROM UserJoinedData
    WHERE DisplayName LIKE 'Peter%' AND Location = 'USA'
) AS CombinedResults

SELECT * FROM #TempTable1

进阶优化：用筛选条件表进一步简化 #

如果后续需要新增更多候选者，可以把筛选条件集中管理，可读性和维护性更强：

DROP TABLE IF EXISTS #TempTable1

WITH UserJoinedData AS (
    -- 同上的多表JOIN逻辑
    SELECT 
        A.DisplayName, 
        A.Location, 
        A.AccountId, 
        F.Title, 
        F.Body, 
        F.Tags
    FROM [Users] A
    LEFT JOIN [Badges] B ON A.Id = B.Id
    LEFT JOIN [Comments] C ON A.Id = C.Id AND C.UserId = B.UserId
    LEFT JOIN [LinkTypes] D ON A.Id = D.Id
    LEFT JOIN [PostLinks] E ON A.Id = E.Id AND E.PostId = C.PostId
    LEFT JOIN [Posts] F ON A.Id = F.Id
    LEFT JOIN [PostTypes] G ON A.Id = G.Id
    LEFT JOIN [Votes] H ON A.Id = H.Id AND H.UserId = B.UserId AND H.PostId = E.PostId
    LEFT JOIN [VoteTypes] I ON A.Id = I.Id
),
CandidateFilters AS (
    -- 集中管理所有候选者的筛选规则
    SELECT 'Candidate1' AS Candidate, 'Jeff%' AS NamePattern, NULL AS AccountFilter, NULL AS LocationFilter, 'Director' AS TitleFilter
    UNION ALL
    SELECT 'Candidate2' AS Candidate, 'ANA%' AS NamePattern, '3125156' AS AccountFilter, NULL AS LocationFilter, NULL AS TitleFilter
    UNION ALL
    SELECT 'Candidate3' AS Candidate, 'Peter%' AS NamePattern, NULL AS AccountFilter, 'USA' AS LocationFilter, NULL AS TitleFilter
)
SELECT 
    cf.Candidate,
    ujd.DisplayName,
    ujd.Location,
    ujd.AccountId,
    ujd.Title,
    ujd.Body,
    ujd.Tags
INTO #TempTable1
FROM UserJoinedData ujd
JOIN CandidateFilters cf 
    ON ujd.DisplayName LIKE cf.NamePattern
    AND (cf.AccountFilter IS NULL OR ujd.AccountId = cf.AccountFilter)
    AND (cf.LocationFilter IS NULL OR ujd.Location = cf.LocationFilter)
    AND (cf.TitleFilter IS NULL OR ujd.Title = cf.TitleFilter)

SELECT * FROM #TempTable1

优化说明 #

• 复用JOIN逻辑：通过CTE将重复的多表关联逻辑只编写一次，后续所有分支自动复用，避免漏改风险。
• 列一致性保障：所有分支严格选择相同的输出列，避免SELECT *导致的列不匹配问题。
• 维护成本降低：修改JOIN逻辑只需调整CTE部分；新增候选者只需在筛选条件表中添加一行，无需重复编写关联代码。

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