主從表右聯查找，不足5條紀錄地補全5條，補的紀錄其它字條段為空，至補上關聯ID

Question

主從表右聯查找，不足5條紀錄地補全5條，補的紀錄其它字條段為空，至補上關聯ID如：
主表：
id,name
01,abc
02,efg從表：
id,context
01,aaaa
01,bbbb
01,cccc
02,eeee現在要得到得紀錄為（5條紀錄得整倍數）：
id,name,context
01, abc, aaaa
01, abc, bbbb
01, abc, cccc
01, abc,
01, abc,

Answer 1

--测试数据
create table #T(id varchar(2),name varchar(3))
insert #t select '01','abc'
insert #t select '02','efg'create table #TT(id varchar(2),context varchar(4))
insert #TT select '01','aaaa'
insert #TT select '01','bbbb'
insert #TT select '01','cccc'
insert #TT select '02','eeee'select c.id,c.name,context=isnull(d.context,'') from 
(select * from #t a,(select col=1 union select 2 union select 3 union select 4 union select 5) b) c
left join
(select col=(select count(*) from #TT where id=a.id and context<=a.context),a.* from #TT a) d
on c.id=d.id and c.col=d.col
order by c.id,context descdrop table #tt,#t/*结果
id   name context 
---- ---- ------- 
01   abc  cccc
01   abc  bbbb
01   abc  aaaa
01   abc  
01   abc  
02   efg  eeee
02   efg  
02   efg  
02   efg  
02   efg  （所影响的行数为 10 行）*/

Answer 2

--这样才对上面超出五条就漏
--测试数据
create table #T(id varchar(2),name varchar(3))
insert #t select '01','abc'
insert #t select '02','efg'create table #TT(id varchar(2),context varchar(4))
insert #TT select '01','aaaa'
insert #TT select '01','bbbb'
insert #TT select '01','cccc'
insert #TT select '01','ffff'
insert #TT select '01','rrrr'
insert #TT select '01','yyyy'
insert #TT select '02','eeee'select id=isnull(c.id,d.id),name=isnull(c.name,(select name from #T where id=d.id)),context=isnull(d.context,'') from 
(select * from #t a,(select col=1 union select 2 union select 3 union select 4 union select 5) b) c
 full join
(select col=(select count(*) from #TT where id=a.id and context<=a.context),a.* from #TT a) d
on c.id=d.id and c.col=d.col
order by c.id,context descdrop table #tt,#t/*结果
id   name context 
---- ---- ------- 
01   abc  yyyy
01   abc  rrrr
01   abc  ffff
01   abc  cccc
01   abc  bbbb
01   abc  aaaa
02   efg  eeee
02   efg  
02   efg  
02   efg  
02   efg  （所影响的行数为 11 行）
*/

Answer 3

declare @t1 table(id varchar(4),name varchar(10))
insert into @t1 select '01','abc'
insert into @t1 select '02','efg'declare @t2 table(id varchar(4),context varchar(10))
insert into @t2 select '01','aaaa'
insert into @t2 select '01','bbbb'
insert into @t2 select '01','cccc'
insert into @t2 select '02','eeee'
select
    a.*,case left(b.context,6) when 'isnull' then '' else b.context end as context
from
    @t1 a,
    (select * from @t2 
           union all 
           select null,'isnull_1'
           union all 
           select null,'isnull_2'
           union all 
           select null,'isnull_3'
           union all 
           select null,'isnull_4'
           union all 
           select null,'isnull_5') b
where
    (a.id=b.id or b.id is null)
    and
    b.context in(select 
                     top 5 c.context 
                 from 
                     (select * from @t2 
                      union all 
                      select null,'isnull_1'
                      union all 
                      select null,'isnull_2'
                      union all 
                      select null,'isnull_3'
                      union all 
                      select null,'isnull_4'
                      union all 
                      select null,'isnull_5') c
                 where 
                     c.id=a.id or c.id is null 
                 order by id desc)/*
id   name       context    
---- ---------- ---------- 
01   abc        aaaa
01   abc        bbbb
01   abc        cccc
01   abc        
01   abc        
02   efg        eeee
02   efg        
02   efg        
02   efg        
02   efg     
*/

Answer 4

create table t1(id varchar(4),name varchar(10))
insert into t1 select '01','abc'
insert into t1 select '02','efg'create table t2(id varchar(4),context varchar(10))
insert into t2 select '01','aaaa'
insert into t2 select '01','bbbb'
insert into t2 select '01','cccc'
insert into t2 select '02','eeee'
insert into t2 select '02','ffff'
insert into t2 select '02','gggg'
insert into t2 select '02','hhhh'
insert into t2 select '02','iiii'
insert into t2 select '02','jjjj'set rowcount 100
select identity(int,1,1) as num into # from sysobjects
set rowcount 0select
    b.*,isnull(d.context,'') context
from
    (select id,(case count(*)%5 when 0 then count(*) else (count(*)/5+1)*5 end) as num from t2 group by id) a
inner join
    t1 b
on
    a.id=b.id
inner join
    # c
on
    a.num>=c.num
left join   
    (select id,context,(select count(*) from t2 where id=m.id and context<=m.context) num from t2 m) d
on
    a.id=d.id and c.num=d.num/*
id   name       context    
---- ---------- ---------- 
01   abc        aaaa
01   abc        bbbb
01   abc        cccc
01   abc        
01   abc        
02   efg        eeee
02   efg        ffff
02   efg        gggg
02   efg        hhhh
02   efg        iiii
02   efg        jjjj
02   efg        
02   efg        
02   efg        
02   efg 
*/drop table t1,t2,#

Answer 5

借助了一个辅助临时表，而且只能在同一id的context不重复的前提下使用；否则还需要借助另外一张临时表。