连接两个不同表中的两个不同列并合并重复项

发布于 2024-12-21 15:08:04 字数 983 浏览 1 评论 0原文

我有两个临时表表 1

 ID1         Name        ID2           Single
----------------------------------------------------
 1            ABC         1            100
 2            DEF         1            200

表 2

 ID1         Name        ID2           Monthly
----------------------------------------------------
 3            PQR         2            500
 4            LMN         2            600
 1            ABC         2            700
 2            DEF         2            800

我想要输出

 ID1         Name        ID2            Single   Monthly
--------------------------------------------------------
 1            ABC         1            100        700
 2            DEF         1            200        800
 3            PQR         2            NULL       500
 4            LMN         2            NULL       600

我使用了 all Joins 、 Union ALL 、 Union 没有任何效果提前致谢

原文

i have two temporary table
Table 1

 ID1         Name        ID2           Single
----------------------------------------------------
 1            ABC         1            100
 2            DEF         1            200

Table 2

 ID1         Name        ID2           Monthly
----------------------------------------------------
 3            PQR         2            500
 4            LMN         2            600
 1            ABC         2            700
 2            DEF         2            800

I want Output

 ID1         Name        ID2            Single   Monthly
--------------------------------------------------------
 1            ABC         1            100        700
 2            DEF         1            200        800
 3            PQR         2            NULL       500
 4            LMN         2            NULL       600

I used all Joins , Union ALL , Union nothing working
thanks in advance

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

小猫一只 2024-12-28 15:08:04

试试这个：

select coalesce(T1.ID1, T2.ID1) as ID1,
       coalesce(T1.Name, T2.Name) as ID1,
       coalesce(T1.ID2, T2.ID2) as ID2,
       T1.Single,
       T2.Monthly
from Table1 as T1
  full outer join Table2 as T2
    on T1.ID1 = T2.ID1

https://data.stackexchange.com/stackoverflow/q/121659/

如果您知道所有行始终会出现在 Table2 中，则可以使用右外连接而不是全连接。

Try this:

select coalesce(T1.ID1, T2.ID1) as ID1,
       coalesce(T1.Name, T2.Name) as ID1,
       coalesce(T1.ID2, T2.ID2) as ID2,
       T1.Single,
       T2.Monthly
from Table1 as T1
  full outer join Table2 as T2
    on T1.ID1 = T2.ID1

https://data.stackexchange.com/stackoverflow/q/121659/

If you know that all rows always will be present in Table2 you can use a right outer join instead of full join.

回复收藏 0 原文

耳钉梦 2024-12-28 15:08:04

希望您使用的是 Sql Server 2008（否则我的查询中的插入语句将不起作用）。试试这个。
从所需的输出来看，我猜您需要 table2 中的所有值以及相应的 Single(表 1 中的列名称)值。

DECLARE @tempTable1 TABLE (ID1 INT,Name VARCHAR(10),ID2 INT,Single INT)
DECLARE @tempTable2 TABLE (ID1 INT,Name VARCHAR(10),ID2 INT,Monthly INT)

    INSERT INTO @tempTable1 VALUES
    (1            ,'ABC'         ,1            ,100),
    (2            ,'DEF'         ,1            ,200)

INSERT INTO @tempTable2 VALUES
(3            ,'PQR'         ,2            ,500  ),
(4            ,'LMN'         ,2            ,600  ),
(1            ,'ABC'         ,2            ,700  ),
(2            ,'DEF'         ,2            ,800 );
SELECT
                T2.ID1
                ,T2.Name
                ,T2.ID2
                ,T1.Single
                ,T2.Monthly

FROM            @tempTable2 T2
LEFT OUTER JOIN @tempTable1 T1
ON              T2.ID1 = T1.ID1
ORDER BY        T2.ID1

Hope you are using Sql Server 2008(other wise the insert statement in my query won't work). Try this one.
From the required out put, i guess you need all the values from table2 and there corresponding Single(Column name in table 1) value.

DECLARE @tempTable1 TABLE (ID1 INT,Name VARCHAR(10),ID2 INT,Single INT)
DECLARE @tempTable2 TABLE (ID1 INT,Name VARCHAR(10),ID2 INT,Monthly INT)

    INSERT INTO @tempTable1 VALUES
    (1            ,'ABC'         ,1            ,100),
    (2            ,'DEF'         ,1            ,200)

INSERT INTO @tempTable2 VALUES
(3            ,'PQR'         ,2            ,500  ),
(4            ,'LMN'         ,2            ,600  ),
(1            ,'ABC'         ,2            ,700  ),
(2            ,'DEF'         ,2            ,800 );
SELECT
                T2.ID1
                ,T2.Name
                ,T2.ID2
                ,T1.Single
                ,T2.Monthly

FROM            @tempTable2 T2
LEFT OUTER JOIN @tempTable1 T1
ON              T2.ID1 = T1.ID1
ORDER BY        T2.ID1

回复收藏 0 原文

~没有更多了~