关于合并一对多查询记录的问题

Question

a 表                          b表
                   
id   code                     id      code
a1    mm1                     a1      mm3
a1    mm2                     a1      mm4
a2    kk1                     a1      mm5 
a3    nn1                     a1      mm6
a3    nn2                     a2      kk2
a3    nn3                     a3      nn6
a3    nn4                     a3      nn7 
a3    nn5                     a3      nn8
......                        ......想要得出以下结果：id     codea     codeb
a1     mm1       mm3
       mm2       mm4
                 mm5
                 mm6
a2     kk1       kk2
a3     nn1       nn6
       nn2       nn7
       nn3       nn8
       nn4
       nn5
......
可有什么方法实现？

Answer 1

select
    isnull(m.id,n.id) as id,
    m.code as codea,
    n.code as codeb
from
    (select t.*,(select count(1) from a where id=t.id and code<=t.code) as num from a t) m
full outer join
    (select t.*,(select count(1) from b where id=t.id and code<=t.code) as num from b t) n
on
    m.id=n.id and m.num=n.num
order by
    isnull(m.id,n.id),isnull(m.num,n.num)

Answer 2

declare @a table(id varchar(8),code varchar(8))
insert into @a select 'a1','mm1'
insert into @a select 'a1','mm2'
insert into @a select 'a2','kk1'
insert into @a select 'a3','nn1'
insert into @a select 'a3','nn2'
insert into @a select 'a3','nn3'
insert into @a select 'a3','nn4'
insert into @a select 'a3','nn5'declare @b table(id varchar(8),code varchar(8))
insert into @b select 'a1','mm3'
insert into @b select 'a1','mm4'
insert into @b select 'a1','mm5'
insert into @b select 'a1','mm6'
insert into @b select 'a2','kk2'
insert into @b select 'a3','nn6'
insert into @b select 'a3','nn7'
insert into @b select 'a3','nn8'
select
    isnull(m.id,n.id) as id,
    m.code as codea,
    n.code as codeb
from
    (select t.*,(select count(1) from @a where id=t.id and code<=t.code) as num from @a t) m
full outer join
    (select t.*,(select count(1) from @b where id=t.id and code<=t.code) as num from @b t) n
on
    m.id=n.id and m.num=n.num
order by
    isnull(m.id,n.id),isnull(m.num,n.num)
/*
id       codea    codeb    
-------- -------- -------- 
a1       mm1      mm3
a1       mm2      mm4
a1       NULL     mm5
a1       NULL     mm6
a2       kk1      kk2
a3       nn1      nn6
a3       nn2      nn7
a3       nn3      nn8
a3       nn4      NULL
a3       nn5      NULL
*/

Answer 3

DECLARE @TA TABLE(id VARCHAR(2),  code   VARCHAR(5), ID2 INT IDENTITY(1,1))
INSERT @TA
SELECT 'a1',  'mm1' UNION ALL 
SELECT 'a1',  'mm2' UNION ALL 
SELECT 'a2',  'kk1' UNION ALL 
SELECT 'a3',  'nn1' UNION ALL 
SELECT 'a3',  'nn2' UNION ALL 
SELECT 'a3',  'nn3' UNION ALL 
SELECT 'a3',  'nn4' UNION ALL 
SELECT 'a3',  'nn5'
                DECLARE @TB TABLE(id VARCHAR(2),      code VARCHAR(5), ID2 INT IDENTITY(1,1))
INSERT @TB
SELECT 'a1',  'mm3' UNION ALL 
SELECT 'a1',  'mm4' UNION ALL 
SELECT 'a1',  'mm5' UNION ALL 
SELECT 'a1',  'mm6' UNION ALL 
SELECT 'a2',  'kk2' UNION ALL 
SELECT 'a3',  'nn6' UNION ALL 
SELECT 'a3',  'nn7' UNION ALL 
SELECT 'a3',  'nn8'SELECT CASE WHEN ISNULL(A.SEQ,B.SEQ)=1 THEN ISNULL(A.ID,'') ELSE '' END AS ID,ISNULL(A.CODE,'') AS CODEA,ISNULL(B.CODE,'') AS CODEB
FROM (
SELECT *,SEQ=ID2-(SELECT COUNT(*) FROM @TA WHERE ID<A.ID) FROM @TA AS A) A 
FULL JOIN (
SELECT *,SEQ=ID2-(SELECT COUNT(*) FROM @TB WHERE ID<A.ID) FROM @TB AS A) B ON A.ID=B.ID AND A.SEQ=B.SEQ
/*
ID   CODEA CODEB 
---- ----- ----- 
a1   mm1   mm3
     mm2   mm4
           mm5
           mm6
a2   kk1   kk2
a3   nn1   nn6
     nn2   nn7
     nn3   nn8
     nn5   
     nn4   (10 row(s) affected)
*/

Answer 4

--在斌哥基礎上改了點
IF OBJECT_ID('TEMPDB..#')IS NOT NULL DROP TABLE #
GO
declare @a table(id varchar(8),code varchar(8))
insert into @a select 'a1','mm1'
insert into @a select 'a1','mm2'
insert into @a select 'a2','kk1'
insert into @a select 'a3','nn1'
insert into @a select 'a3','nn2'
insert into @a select 'a3','nn3'
insert into @a select 'a3','nn4'
insert into @a select 'a3','nn5'
declare @b table(id varchar(8),code varchar(8))
insert into @b select 'a1','mm3'
insert into @b select 'a1','mm4'
insert into @b select 'a1','mm5'
insert into @b select 'a1','mm6'
insert into @b select 'a2','kk2'
insert into @b select 'a3','nn6'
insert into @b select 'a3','nn7'
insert into @b select 'a3','nn8'
select
    isnull(m.id,n.id) as id,
    m.code as codea,
    n.code as codeb,
    CNT=IDENTITY(INT,1,1) INTO #
from
    (select t.*,(select count(1) from @a where id=t.id and code<=t.code) as num from @a t) m
full outer join
    (select t.*,(select count(1) from @b where id=t.id and code<=t.code) as num from @b t) n
on
    m.id=n.id and m.num=n.num
order by
    isnull(m.id,n.id),isnull(m.num,n.num)
SELECT ID=CASE WHEN  EXISTS(SELECT 1 FROM # WHERE ID=T.ID AND CNT<T.CNT ) THEN '' ELSE ID END,
CODEA,CODEB
 FROM # T
/*ID       CODEA    CODEB    
-------- -------- -------- 
a1       mm1      mm3
         mm2      mm4
         NULL     mm5
         NULL     mm6
a2       kk1      kk2
a3       nn1      nn6
         nn2      nn7
         nn3      nn8
         nn4      NULL
         nn5      NULL*/

Answer 5

处理一下输出的格式：
declare @a table(id varchar(8),code varchar(8))
insert into @a select 'a1','mm1'
insert into @a select 'a1','mm2'
insert into @a select 'a2','kk1'
insert into @a select 'a3','nn1'
insert into @a select 'a3','nn2'
insert into @a select 'a3','nn3'
insert into @a select 'a3','nn4'
insert into @a select 'a3','nn5'declare @b table(id varchar(8),code varchar(8))
insert into @b select 'a1','mm3'
insert into @b select 'a1','mm4'
insert into @b select 'a1','mm5'
insert into @b select 'a1','mm6'
insert into @b select 'a2','kk2'
insert into @b select 'a3','nn6'
insert into @b select 'a3','nn7'
insert into @b select 'a3','nn8'
select
    (case when isnull(m.num,n.num)=1 then isnull(m.id,n.id) else '' end) as id,
    isnull(m.code,'') as codea,
    isnull(n.code,'') as codeb
from
    (select t.*,(select count(1) from @a where id=t.id and code<=t.code) as num from @a t) m
full outer join
    (select t.*,(select count(1) from @b where id=t.id and code<=t.code) as num from @b t) n
on
    m.id=n.id and m.num=n.num
order by
    isnull(m.id,n.id),isnull(m.num,n.num)
/*
id       codea    codeb    
-------- -------- -------- 
a1       mm1      mm3
         mm2      mm4
                  mm5
                  mm6
a2       kk1      kk2
a3       nn1      nn6
         nn2      nn7
         nn3      nn8
         nn4      
         nn5      
*/