sql-server - CTE两次加入同一张表

Question

我有一个表@MitoT，代码如下:

DECLARE @ResT UNIQUEIDENTIFIER = NEWID(), @ResC UNIQUEIDENTIFIER = NEWID();
DECLARE
    @MitoT TABLE (
        Pid UNIQUEIDENTIFIER NOT NULL PRIMARY KEY,
        MitoResultID UNIQUEIDENTIFIER NOT NULL,
        Position VARCHAR(10) NOT NULL,
        Base VARCHAR(10) NOT NULL);

并用一些数据填充它:

INSERT INTO @MitoT VALUES (NEWID(), @ResT, '1', 'A');
INSERT INTO @MitoT VALUES (NEWID(), @ResT, '3', 'A');
INSERT INTO @MitoT VALUES (NEWID(), @ResT, '4', 'A');
INSERT INTO @MitoT VALUES (NEWID(), @ResT, '5', 'N');
INSERT INTO @MitoT VALUES (NEWID(), @ResC, '1', 'A');
INSERT INTO @MitoT VALUES (NEWID(), @ResC, '3', 'C');
INSERT INTO @MitoT VALUES (NEWID(), @ResC, '5', '-');
INSERT INTO @MitoT VALUES (NEWID(), @ResT, '6', 'G');
INSERT INTO @MitoT VALUES (NEWID(), @ResC, '6', 'T');
INSERT INTO @MitoT VALUES (NEWID(), @ResT, '9', 'A');
INSERT INTO @MitoT VALUES (NEWID(), @ResC, '11', 'C');

所以对于任何 Position MitoResultID = @ResT 可以存在一行或 MitoResultID = @ResC或两者兼而有之。现在我尝试一个 CTE，它每 Position 返回我一次, BaseT (即 Base WHERE MitoResultID = @ResT )和 BaseC (即 Base WHERE MitoResultID = @ResC )对于所有情况:

WITH Compare (PositionT, PositionC, BaseT, BaseC) AS (
    SELECT t.Position, c.Position, t.Base, c.Base
        FROM @MitoT t
        FULL OUTER JOIN @MitoT c ON t.Position = c.Position
        WHERE (t.MitoResultID = @ResT AND c.MitoResultID = @ResC)
        OR (t.MitoResultID = @ResT AND c.MitoResultID IS NULL)
        OR (t.MitoResultID IS NULL AND c.MitoResultID = @ResC)
        )
SELECT * FROM Compare

但显然我做错了，因为它只显示那些 Position BaseT和 BaseC存在。请帮助我了解问题所在。

更新:结果如下:

<表类="s-表"><头>位置T位置CBaseT基地C<正文>55N-66GT33一个C11一个一个

所以我遗漏了 BaseT 或 BaseC 为 NULL 的位置行，例如 4、9 或 11

Answer 1

正如 Nikola Markovinović 在他的评论中所建议的那样:

SELECT t.Position, c.Position, t.Base, c.Base FROM (select * from @MitoT t where MitoResultID = @ResT) t FULL OUTER JOIN (select * from @MitoT c where MitoResultID = @ResC) c ON t.Position = c.Position

这非常有效，CTE 现在看起来像:

WITH Compare (PositionT, PositionC, BaseT, BaseC) AS (
    SELECT t.Position, c.Position, t.Base, c.Base 
        FROM (select * from @MitoT t where MitoResultID = @ResT) t 
        FULL OUTER JOIN (select * from @MitoT c where MitoResultID = @ResC) c 
        ON t.Position = c.Position
    )
SELECT * FROM Compare

结果表:

<表类="s-表"><头>位置T位置CBaseT基地C<正文>11一个一个33一个C55N-空11空C66GT9空一个空4空一个空