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

--> 测试数据：@表Adeclare @表A table([ID] varchar(3),[ITEM] int,[NAME] varchar(2))insert @表Aselect 'A01',1,'T1' union allselect 'A01',2,'T1' union allselect 'A02',1,'T1' union allselect 'A02',2,'T2'--> 测试数据：@表Bdeclare @表B table([NAME] varchar(2),[ITEM] int)insert @表Bselect 'T1',0 union allselect 'T1',1 union allselect 'T2',0 union allselect '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   0A01  2           T1   T1   1A02  1           T1   NULL NULLA02  2           T2   T2   0NULL NULL        NULL T2   1*/
[解决办法]
SQL code
--> 测试数据：@表Adeclare @表A table([ID] varchar(3),[ITEM] int,[NAME] varchar(2))insert @表Aselect 'A01',1,'T1' union allselect 'A01',2,'T1' union allselect 'A02',1,'T1' union allselect 'A02',2,'T2'--> 测试数据：@表Bdeclare @表B table([NAME] varchar(2),[ITEM] int)insert @表Bselect 'T1',0 union allselect 'T1',1 union allselect 'T2',0 union allselect '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   0A01  2           T1   2                    T1   1A02  2           T2   1                    T2   0NULL NULL        NULL 2                    T2   1A02  1           T1   NULL                 NULL NULL*/