代码之家  ›  专栏  ›  技术社区  ›  Saurabh Kumar

如何使用基于计数的列进行外部联接?

  •  1
  • Saurabh Kumar  · 技术社区  · 15 年前

    前两个是输入表,我需要使用它们填充第三个(DataOut表)

    我需要用所有行填充DataOut表,包括none matched和multiple matched,并填充一个状态列,区分WDATA中的单个对应行、WDATA中的No行或WDATA中的恰好一行。

    DataIn
    QID  RID  DOB
    -------------
    1    1    01/01/1980
    1    2    03/01/1981
    1    3    01/02/1991
    
    
    
    WDATA(key is QID, RID,PID)
    QID  RID  PID
    ---------------
    1    1    101
    1    1    102
    1    3    204
    
    
    
    DataOut
    QID  RID  PID  status
    -----------------------
    1    1    101  ”multiple match”
    1    1    102  ”multiple match”
    1    2    null ”no match”
    1    3    204  ”single match”
    
    3 回复  |  直到 15 年前
        1
  •  2
  •   marc_s MisterSmith    15 年前

    这个查询怎么样??

    SELECT
        di.QID, di.RID, w.PID,
        CASE (SELECT COUNT(*) FROM WDATA w2 WHERE di.QID = w2.QID AND di.RID = w2.RID)
            WHEN 0 THEN 'no match'
            WHEN 1 THEN 'single match'
            ELSE 'multiple match'
        END AS 'Status'
    FROM
        DataIn di 
    LEFT OUTER JOIN 
        WDATA w ON di.QID = w.QID AND di.RID = w.RID
    

    QID  RID  PID   Status
     1    1   101   multiple match
     1    1   102   multiple match
     1    2   NULL  no match
     1    3   204   single match
    

    这就是你要找的吗??

        2
  •  0
  •   Meff    15 年前

    DECLARE @DataIn TABLE
    (
        QID INT NOT NULL,
        RID INT NOT NULL,
        DOB DATE NOT NULL
    )
    INSERT INTO @DataIn
    VALUES
    (1,1,'01/01/1980'),
    (1,2,'03/01/1981'),
    (1,3,'01/02/1991')
    
    DECLARE @WDATA TABLE
    (
        QID INT NOT NULL,
        RID INT NOT NULL,
        PID INT NOT NULL        
    )
    INSERT INTO @WDATA
    VALUES
    (1,1,101),
    (1,1,102),
    (1,3,204)
    
    ;WITH OuterCTE(QID, RID, PID) AS
    (
    SELECT 
        ISNULL(D.QID, W.QID) AS QID,
        ISNULL(D.RID, W.RID) AS RID,    
        W.PID
    FROM @DataIn AS D FULL OUTER JOIN @WDATA AS W ON W.RID = D.RID AND W.QID = D.QID
    )
    
    SELECT
        CTE.QID,
        CTE.RID,
        CTE.PID,
        CASE 
            WHEN COUNT(W.PID) = 0 THEN 'no match'
            WHEN COUNT(W.PID) = 1 THEN 'single match'
            ELSE 'multiple match'
        END
    FROM
        OuterCTE AS CTE
        LEFT JOIN @WDATA AS W 
        ON CTE.QID = W.QID 
        AND CTE.RID = W.RID
    GROUP BY
        CTE.QID,
        CTE.RID,
        CTE.PID
    
        3
  •  0
  •   Abe Miessler    15 年前

    试试这个:

    SELECT di.QID,di.RID,wd.PID, 
    CASE 
       WHEN  wd.PID is null THEN 'no match'
       WHEN COUNT(di.QID) = 1 THEN 'single match'
       WHEN COUNT(di.QID) > 1 THEN 'multiple match'
    END
    FROM DataIn as di
    LEFT JOIN WDATA as wd
    ON di.QID = wd.QID AND di.RID = wd.RID
    GROUP BY di.QID,di.RID,wd.PID
    
    推荐文章