sql - 根据具体情况选择

Question

如果有人可以更新标题以更好地反射(reflect)我的问题，我们将不胜感激。我想我一直在努力寻找答案，因为我不知道正确的搜索词。

If I have the following values in a table:

TableID      TableSensitivity
-----------------------------
1            aSensitivity
1            aRevision
2            bRevision
3            cRevision
3            cSensitivity

How can I filter the values containing "Revision" only for each TableID where the is already a value containing "Sensitivity". This is the expected output:

TableID      TableSensitivity
-----------------------------
1            aSensitivity
2            bRevision
3            cSensitivity

这是一个示例设置:

DECLARE  @Example TABLE (TableID INT, TableSensitivity VARCHAR(200))

INSERT INTO @Example(TableID, TableSensitivity) VALUES (1, 'aSensitivity')
INSERT INTO @Example(TableID, TableSensitivity) VALUES (1, 'aRevision')
INSERT INTO @Example(TableID, TableSensitivity) VALUES (2, 'bRevision')
INSERT INTO @Example(TableID, TableSensitivity) VALUES (3, 'cSensitivity')
INSERT INTO @Example(TableID, TableSensitivity) VALUES (3, 'cRevision')

我已经尝试使用 CASE 来确定具有敏感度的行，并认为我可以使用 MAX 进行过滤，但无法让它工作:

SELECT TableID
     , TableSensitivity
     , CASE WHEN TableSensitivity LIKE '%Sensitivity' THEN 1 ELSE 0 END 'SomeKey' 
FROM @Example

Answer 1

你可以使用WHERE NOT EXISTS

SELECT TableID
     , TableSensitivity
FROM @Example e1
WHERE NOT EXISTS
(
    SELECT *
    FROM @Example e2
    WHERE e1.TableID=e2.TableID
    AND e2.TableSensitivity LIKE '%Sensitivity'
    AND e1.TableSensitivity LIKE '%Revision'
)