MSSQL根据其他行列值出现情况筛选行的非常规SQL查询实现

实现思路 #

• 先做第一层过滤：剔除不符合基础规则的行，只保留「单列值为A/B、另一列为非A/B普通值」的候选数据，同时消除COL_LEFT、COL_RIGHT的顺序差异，统一提取每行的特殊值（A/B）和配对普通值X
• 再做第二层校验：筛选出同时存在A配对、B配对的有效普通值X
• 最后关联返回所有有效X对应的原始行，即为最终符合要求的结果

可直接运行的SQL代码 #

WITH CandidateRows AS (
    -- 过滤基础合规行，统一提取特殊值与配对X，消除左右列顺序影响
    SELECT
        COL_LEFT,
        COL_RIGHT,
        CASE WHEN COL_LEFT IN ('A','B') THEN COL_LEFT ELSE COL_RIGHT END AS SpecialVal,
        CASE WHEN COL_LEFT IN ('A','B') THEN COL_RIGHT ELSE COL_LEFT END AS MatchX
    FROM YourTableName -- 替换为实际业务表名即可
    WHERE
        -- 排除两列同时为A/B的行
        NOT (COL_LEFT IN ('A','B') AND COL_RIGHT IN ('A','B'))
        -- 排除两列都不是A/B的行
        AND (COL_LEFT IN ('A','B') OR COL_RIGHT IN ('A','B'))
),
ValidMatchX AS (
    -- 筛选同时存在A、B配对的有效X值
    SELECT MatchX
    FROM CandidateRows
    GROUP BY MatchX
    HAVING COUNT(DISTINCT SpecialVal) = 2
)
-- 返回最终符合所有规则的原始行
SELECT cr.COL_LEFT, cr.COL_RIGHT
FROM CandidateRows cr
INNER JOIN ValidMatchX vx ON cr.MatchX = vx.MatchX;

结果匹配说明 #

以上语句在提供的测试数据集上运行结果完全符合预期：

• 最终识别的有效普通值X为C、E、F，三个值均同时存在与A、B的配对记录
• 所有被排除的行分属三类：两列均为A/B（如B/A行）、两列均为非A/B普通值、配对X仅对应A或仅对应B（如S仅配A、L仅配B、K仅配B、M仅配A等）

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