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

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

join sql-server-2008 sql

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