从SQL表中选择带有标志X或Y的记录,但不选择具有X和Y的记录

 收藏

我有这样的桌子

AID B   C   
2471    D471    X
2471    D471    Y

2471    E471    X

2471    F471    Y

2472    D471    X
2472    D471    X

2473    E471    Y
2473    E471    Y

2474    F471    Y

我需要通过将A和B分组来选择具有X或Y的AID, 但是当我们按A和B分组时,它不应选择具有X和Y的记录

预期产量

AID B   C
2471    E471    X

2471    F471    Y

2472    D471    X
2472    D471    X

2473    E471    Y
2473    E471    Y

2474    F471    Y

如果您对此查询有任何疑问,请告诉我。

创建表和插入数据的脚本

CREATE TABLE tablename
(
    [A] int
    , [B] varchar(10)
    , [C] varchar(5)
);

INSERT INTO tablename
    ([A], [B], [C])
VALUES
    (2471, 'D471', 'X'),
    (2471, 'D471', 'Y'),
    (2471, 'E471', 'X'),
    (2471, 'F471', 'Y'),
    (2472, 'D471', 'X'),
    (2472, 'D471', 'X'),
    (2473, 'E471', 'Y'),
    (2473, 'E471', 'Y'),
    (2474, 'F471', 'Y')
;
回复
  • Assuming that the C column can only contain the values X and Y, you should be able to just check the distinct count:

    WITH cte AS (
        SELECT A, B
        FROM tablename
        GROUP BY A, B
        HAVING COUNT(DISTINCT C) = 1
    )
    
    SELECT t1.A, t1.B, t1.C
    FROM tablename t1
    INNER JOIN cte t2
        ON t1.A = t2.A AND t1.B = t2.B;
    

    screen capture of demo below

    Demo