select top 5 * from a left join (select top 1 * from b) b on a.id = b.id
declare @a table(id int); insert into @a select 1 union all select 2 union all select 3;declare @b table(id int identity, a_id int); insert into @b (a_id) select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 2 union all select 2 union all select 2 union all select 2 union all select 2 union all select 2 union all select 3 union all select 3;-- SQL 2000 select a.id aid, b.id bid from @a a,@b b where b.id in (select top 5 id from @b where a_id=a.id order by id);-- SQL 2005 select aid,bid from (select a.id aid, b.id bid, row_number() over (partition by a.id order by b.id) rn from @a a, @b b where a.id=b.a_id) t where rn<=5;
用join 是不错的。 但 语句中用 Right join 还是 Left join ,就要看两个表的放置位置了。 在你的语句中,join 不行,你可以试一下 Right join
declare @a table(id int,c char(2)); insert into @a select 1,'aa' union all select 2,'bb' union all select 3,'cc' union all select 4,'dd' union all select 5,'ee' union all select 6,'ff' union all select 7,'gg';declare @b table(id int identity, a_id int); insert into @b (a_id) select 1 union all select 1 union all select 1 union all select 2 union all select 2 union all select 3 union all select 4 union all select 4 union all select 5 union all select 5 union all select 6 ;
-- sql 2000 select a.id a_id,b.id b_id from (select top 5 * from @a order by id) a,@b b where b.id=(select top 1 id from @b where a_id=a.id order by id);-- sql 2005 with t as( select a.id a_id, b.id b_id, ROW_NUMBER() over (partition by a.id order by b.id) rn from (select top 5 * from @a order by id) a,@b b where a.id=b.a_id ) select a_id,b_id from t where rn=1;
--> 测试数据: [A] if object_id('[A]') is not null drop table [A] create table [A] (ID int,Orther varchar(2)) insert into [A] select 1,'aa' union all select 2,'bb' union all select 3,'cc' union all select 4,'dd' union all select 5,'ee' union all select 6,'ff' union all select 7,'gg' --> 测试数据: [b] if object_id('[b]') is not null drop table [b] create table [b] (id int,a_id int) insert into [b] select 1,1 union all select 2,1 union all select 3,1 union all select 4,2 union all select 5,2 union all select 6,3 union all select 7,4 union all select 8,4 union all select 9,5 union all select 10,5 union all select 11,6 select top 5 a_id,b_id=id from b where not exists(select 1 from b t where t.a_id=b.a_id and t.id<b.id) order by id--结果: a_id b_id ----------- ----------- 1 1 2 4 3 6 4 7 5 9
;with a(ID, Orther) as ( select 1,'aa' union all select 2,'bb' union all select 3,'cc' union all select 4,'dd' union all select 5,'ee' union all select 6,'ff' union all select 7,'gg' ), b(id, a_id) as ( select 1,1 union all select 2,1 union all select 3,1 union all select 4,2 union all select 5,2 union all select 6,3 union all select 7,4 union all select 8,4 union all select 9,5 union all select 10,5 union all select 11,6 )select a.id as a_id, b_id = (select top 1 b.id from b where a.id = b.a_id) from a
自己琢磨出来了。 select top 5 b.*,c* from tab_a as b join (select * from tab_b as a where id=(select top 1 id from tab_b where a_id=a.a_id )) as c on b.id=c.id order by b.id desc先滤掉B表重复记录,然后和a表匹配,有点麻烦。。等待更好的结果
declare @a table(id int);
insert into @a
select 1 union all select 2 union all select 3;declare @b table(id int identity, a_id int);
insert into @b (a_id)
select 1 union all select 1 union all
select 1 union all select 1 union all
select 1 union all select 1 union all
select 2 union all select 2 union all
select 2 union all select 2 union all
select 2 union all select 2 union all
select 3 union all select 3;-- SQL 2000
select a.id aid, b.id bid
from @a a,@b b
where b.id in (select top 5 id from @b where a_id=a.id order by id);-- SQL 2005
select aid,bid
from (select a.id aid, b.id bid,
row_number() over (partition by a.id order by b.id) rn
from @a a, @b b where a.id=b.a_id) t
where rn<=5;
但 语句中用 Right join 还是 Left join ,就要看两个表的放置位置了。
在你的语句中,join 不行,你可以试一下 Right join
1 aa
2 bb
3 cc
4 dd
5 ee
6 ff
7 ggb tabid a_id
1 1
2 1
3 1
4 2
5 2
6 3
7 4
8 4
9 5
10 5
11 6最后得到的结果是a_id b_id
1 1
2 4
3 6
4 7
5 9 意思先得到top 5 A 表记录,表盒他匹配。
declare @a table(id int,c char(2));
insert into @a
select 1,'aa' union all select 2,'bb' union all
select 3,'cc' union all select 4,'dd' union all
select 5,'ee' union all select 6,'ff' union all
select 7,'gg';declare @b table(id int identity, a_id int);
insert into @b (a_id)
select 1 union all select 1 union all
select 1 union all select 2 union all
select 2 union all select 3 union all
select 4 union all select 4 union all
select 5 union all select 5 union all
select 6 ;
-- sql 2000
select a.id a_id,b.id b_id
from (select top 5 * from @a order by id) a,@b b
where b.id=(select top 1 id from @b where a_id=a.id order by id);-- sql 2005
with t as(
select a.id a_id, b.id b_id,
ROW_NUMBER() over (partition by a.id order by b.id) rn
from (select top 5 * from @a order by id) a,@b b
where a.id=b.a_id
)
select a_id,b_id from t where rn=1;
if object_id('[A]') is not null drop table [A]
create table [A] (ID int,Orther varchar(2))
insert into [A]
select 1,'aa' union all
select 2,'bb' union all
select 3,'cc' union all
select 4,'dd' union all
select 5,'ee' union all
select 6,'ff' union all
select 7,'gg'
--> 测试数据: [b]
if object_id('[b]') is not null drop table [b]
create table [b] (id int,a_id int)
insert into [b]
select 1,1 union all
select 2,1 union all
select 3,1 union all
select 4,2 union all
select 5,2 union all
select 6,3 union all
select 7,4 union all
select 8,4 union all
select 9,5 union all
select 10,5 union all
select 11,6
select top 5 a_id,b_id=id from b where not exists(select 1 from b t where t.a_id=b.a_id and t.id<b.id) order by id--结果:
a_id b_id
----------- -----------
1 1
2 4
3 6
4 7
5 9
;with a(ID, Orther) as
(
select 1,'aa'
union all
select 2,'bb'
union all
select 3,'cc'
union all
select 4,'dd'
union all
select 5,'ee'
union all
select 6,'ff'
union all
select 7,'gg'
),
b(id, a_id) as
(
select 1,1
union all
select 2,1
union all
select 3,1
union all
select 4,2
union all
select 5,2
union all
select 6,3
union all
select 7,4
union all
select 8,4
union all
select 9,5
union all
select 10,5
union all
select 11,6
)select a.id as a_id, b_id = (select top 1 b.id from b where a.id = b.a_id) from a
select top 5 b.*,c* from tab_a as b join (select * from tab_b as a where id=(select top 1 id from tab_b where a_id=a.a_id )) as c on b.id=c.id order by b.id desc先滤掉B表重复记录,然后和a表匹配,有点麻烦。。等待更好的结果