日期:2014-05-17  浏览次数:20327 次

SQL:如何将多对多的关系调整为一对一的关系
表A 表B
-------------------- ------------------
ID ITEM NAME NAME ITEM
A01 1 T1 T1 0
A01 2 T1 T1 1
A02 1 T1 T2 0
A02 2 T2 T2 1
现在要获得结果为
ID ITEM NAME  NAME  ITEM
A01 1 T1 T1 0  
A01 2 T1 T1 1
A02 1 T1      
A02 2 T2 T2 0
  T2 1
其中,B表中NAME有值则A表中必有NAME值,但A表中NAME有值,B表NAME不一定有值。有个想法就是取A表中DISTINCT NAME值,然后用游标分别给A表和B表加一个一一对应的序号,用A表LEFT JOIN B表,再UION B表 LEFT JOIN A表值中A表部分为空的数据。但觉得此方法太过复杂,请问有没有更简便的方法?

------解决方案--------------------
一一对应,你这两个表的主键分别是什么?
------解决方案--------------------
你想要的结果,我没看明白
------解决方案--------------------
SQL code

--> 测试数据:@表A
declare @表A table([ID] varchar(3),[ITEM] int,[NAME] varchar(2))
insert @表A
select 'A01',1,'T1' union all
select 'A01',2,'T1' union all
select 'A02',1,'T1' union all
select 'A02',2,'T2'

--> 测试数据:@表B
declare @表B table([NAME] varchar(2),[ITEM] int)
insert @表B
select 'T1',0 union all
select 'T1',1 union all
select 'T2',0 union all
select 'T2',1

;WITH maco AS 
(
    select ROW_NUMBER() OVER (partition BY NAME ORDER BY GETDATE()) AS rid,* from @表A
)
SELECT a.ID,a.ITEM,a.NAME,b.* FROM maco a 
FULL JOIN @表B b ON a.rid-1=b.ITEM AND a.NAME=b.NAME
/*
ID   ITEM        NAME NAME ITEM
---- ----------- ---- ---- -----------
A01  1           T1   T1   0
A01  2           T1   T1   1
A02  1           T1   NULL NULL
A02  2           T2   T2   0
NULL NULL        NULL T2   1
*/

------解决方案--------------------
SQL code

--> 测试数据:@表A
declare @表A table([ID] varchar(3),[ITEM] int,[NAME] varchar(2))
insert @表A
select 'A01',1,'T1' union all
select 'A01',2,'T1' union all
select 'A02',1,'T1' union all
select 'A02',2,'T2'

--> 测试数据:@表B
declare @表B table([NAME] varchar(2),[ITEM] int)
insert @表B
select 'T1',0 union all
select 'T1',1 union all
select 'T2',0 union all
select 'T2',1

;WITH macoa AS 
(
    select ROW_NUMBER() OVER (partition BY NAME ORDER BY GETDATE()) AS rid,* from @表A
),
macob AS
(
    select ROW_NUMBER() OVER (partition BY NAME ORDER BY GETDATE()) AS rid,* from @表B
)
SELECT a.ID,a.ITEM,a.NAME,b.* FROM macoa a 
FULL JOIN macob b ON a.rid=b.rid AND a.NAME=b.NAME

/*
ID   ITEM        NAME rid                  NAME ITEM
---- ----------- ---- -------------------- ---- -----------
A01  1           T1   1                    T1   0
A01  2           T1   2                    T1   1
A02  2           T2   1                    T2   0
NULL NULL        NULL 2                    T2   1
A02  1           T1   NULL                 NULL NULL
*/