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

[vfork]

主從表右聯查找，不足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,

[zhaoanle]

--测试数据
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 desc

drop 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 行）*/

[vfork]

樓上的基本上可以了，但還有個條件。
反正要是5的倍數，
假設有3條，那就補到5條。
假設有9條，那就補到10條。
假設有15條，顯示15條。

[zhaoanle]

--这样才对上面超出五条就漏
--测试数据
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 desc

drop 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 行）
*/

[zhaoanle]

樓上的基本上可以了，但還有個條件。
反正要是5的倍數，
假設有3條，那就補到5條。
假設有9條，那就補到10條。
假設有15條，顯示15條

如果要达到这样的话,感觉有点复杂

[zhaoanle]

希望有高手想个简单的方法
关注了

[子陌红尘]

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
*/

[子陌红尘]

太牵强，再想想。

[zhaoanle]

楼上超过五条的记录就漏掉了
---
反正要是5的倍數，
假設有3條，那就補到5條。
假設有9條，那就補到10條。
假設有15條，顯示15條
---
这样的条件不知子陌老大能做到吗?

关注!

[vfork]

繼續光注，我自己也想想看。

[子陌红尘]

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 0

select
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,#

[子陌红尘]

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

[aniude]

MARK

[liangpei2008]

学习

[jixiaojie]

up