SQL Server在主表中没有重复项的情况下连接两个表

问题描述：

我想要将两个表连接在一起，以便返回所有外部行并且不重复主表的行。例如：SQL Server在主表中没有重复项的情况下连接两个表

T1 

    pk code value 
    1 One 100 
    2 Two 200 

T2 

    fk value 
    1  10 
    1  15 
    1  30 
    2  25

我想T2的所有记录，而不T1记录复制，所以结果集我想是这样的：

T2.fk T1.code T1.value T2.value 
    1  One  100  10 
    1  NULL  NULL  15 
    1  NULL  NULL  30 
    2  Two  200  25

是否有一个SQL Server连接方法实现这一？

检查出'LEFT JOIN'！ – jarlh

参考下面。它的简单易懂http://www.codeproject.com/KB/database/Visual_SQL_Joins/Visual_SQL_JOINS_orig.jpg – aads

这些'NULL'值背后的逻辑是什么？没有普通的连接会给你这个结果集。 –

答

您需要排名您排在T2，并做了left join包括等级作为连接条件：

with cte as(select *, row_number() over(partition by fk order by value) as rn from T2) 

select c.fk, t.code, t.value, c.value 
from cte c 
left join T1 t on c.fk = t.pk and c.rn = 1

下面是完整的例子：

DECLARE @t1 TABLE 
    (
     pk INT , 
     code VARCHAR(MAX) , 
     value INT 
    ) 
INSERT INTO @t1 
VALUES (1, 'One', 100), 
     (2, 'Two', 200) 

DECLARE @t2 TABLE (fk INT, value INT) 
INSERT INTO @t2 
VALUES (1, 10), 
     (1, 15), 
     (1, 30), 
     (2, 25); 
WITH cte 
      AS (SELECT * , 
         ROW_NUMBER() OVER (PARTITION BY fk ORDER BY value) AS rn 
       FROM  @t2 
      ) 
    SELECT c.fk , 
      t.code , 
      t.value , 
      c.value 
    FROM cte c 
      LEFT JOIN @t1 t ON c.fk = t.pk 
           AND c.rn = 1

完美...我似乎在所有的答案都没有实际击中标记：/ – Scribbly

答

试试这个：

select T2.fk, 
CASE 
    WHEN (SELECT COUNT(*) FROM t2 tother WHERE tother.fk = t2.fk 
    AND tother.value > t2.value) > 0 THEN NULL ELSE t1.code 
END, 
CASE 
    WHEN (SELECT COUNT(*) FROM t2 tother WHERE tother.fk = t2.fk 
    AND tother.value > t2.value) > 0 THEN NULL ELSE t1.value 
END,T2.value 
from t2 
join t1 
on t2.fk = t1.pk

答

DECLARE @t1 TABLE (pk int,code varchar(10),value int) 
    DECLARE @t2 TABLE (fk int,value int) 

    INSERT INTO @t1 
    SELECT 1,'one',100 
    UNION 
    SELECT 2,'two',200 

    INSERT INTO @t2 
    SELECT 1,10 
    UNION SELECT 1,15 UNION SELECT 1,30 UNION SELECT 2,25 


;WITH cte AS(
SELECT t2.fk,t2.value t2val,t1.pk,t1.code,t1.value t1val,ROW_NUMBER() OVER(PARTITION BY fk ORDER BY fk) rno FROM @t2 t2 LEFT JOIN @t1 t1 on t2.fk=t1.pk) 
SELECT fk,code=(CASE WHEN rno=1 THEN code ELSE null END),t1val=(CASE WHEN rno=1 THEN t1val ELSE NULL END),t2val FROM cte

输出

fk code t1val t2val 
1 one  100  10 
1 NULL NULL  15 
1 NULL NULL  30 
2 two  200  25

SQL Server在主表中没有重复项的情况下连接两个表

输出

相关推荐