现有两个这样的表,如下:
TB1
GUID COL1 COL2
ABC1 123 100
ABC2 234 50TB2
GUID MC SL JE
ABC1 1100 1 100
ABC1 1122 1 50
ABC2 2233 1 50我想合并为一个表TB3
GUID COL1 COL2 MC SL JE
ABC1 123 100 1100 1 100
ABC1 NULL NULL 1122 1 50
ABC2 234 50 2233 1 50
TB1
GUID COL1 COL2
ABC1 123 100
ABC2 234 50TB2
GUID MC SL JE
ABC1 1100 1 100
ABC1 1122 1 50
ABC2 2233 1 50我想合并为一个表TB3
GUID COL1 COL2 MC SL JE
ABC1 123 100 1100 1 100
ABC1 NULL NULL 1122 1 50
ABC2 234 50 2233 1 50
Select A.GUID,A.COL1,A.COL2,B.MC,B.SL,B.JE From TB1 A Inner Join TB2 B On A.GUID=B.GUID
from TB1 right outer join
(select GUID,MC , SL, JE, Row_number()over(partition by GUID order by GUID) As cnt
from TB2) AS A
on (A.GUID=TB1.GUID and a.CNT=1)
Create table #TB1([GUID] varchar(10),COL1 int,COL2 int)
insert #tb1
select 'ABC1','123','100'
union all
select 'ABC2','234','50'Create table #TB2([GUID] varchar(10),MC int,SL int,JE int)
insert #tb2
select 'ABC1','1100','1','100'
union all
select 'ABC1','1122','1','50'
union all
select 'ABC2','2233','1','50'
select #tb1.[GUID],#tb1.COL1,#tb1.COL2,#tb2.MC,#tb2.SL,#tb2.JE from #tb1
left join #tb2 on #tb1.[GUID]=#tb2.[GUID]drop table #tb1
drop table #tb2
/*
GUID COL1 COL2 MC SL JE
ABC1 123 100 1100 1 100
ABC1 123 100 1122 1 50
ABC2 234 50 2233 1 50
*/
INSERT INTO @tb1 (
GUID,
COL1,
COL2
) VALUES (
'ABC1',
123,
100 )
INSERT INTO @tb1 (
GUID,
COL1,
COL2
) VALUES (
'ABC2',
234,
50 )
DECLARE @tab2 TABLE (GUID CHAR(4),MC INT ,SL INT ,JE INT )
INSERT INTO @tab2
SELECT
'ABC1', 1100 , 1, 100
UNION ALL
SELECT
'ABC1', 1122 , 1 , 50
UNION ALL
SELECT
'ABC2', 2233 , 1 , 50 select A.GUID,b.COL1, b.COL2,A.MC,A.SL,A.JE
from @tb1 b right outer join
(select GUID,MC , SL, JE, Row_number()over(partition by GUID order by GUID) As cnt
from @tab2) AS A
on (A.GUID=b.GUID and a.CNT=1)
这样写楼主更容易看懂,也容易理解。
From TB2 left Join TB1
on (TB1.guid=tb2.guid)
and (tb1.je=tb2.je)
insert tb1
select 'ABC1','123','100'
union all
select 'ABC2','234','50'Create table TB2([GUID] varchar(10),MC int,SL int,JE int)
insert tb2
select 'ABC1','1100','1','100'
union all
select 'ABC1','1122','1','50'
union all
select 'ABC2','2233','1','50'
select TB2.GUID,COL1,COL2,MC,SL,JE
from TB1 left join TB2 on TB2.GUID=TB1.GUID