求一条SQL查询语句

[xjchen]

两张表 users 和 news 关联
users里的用户可以发布信息保存在news里
现在要取出一个记录集, 要求返回news 里的记录, 但每个用户最多取10条信息,要如何去写这个查询语句?

[dulei115]

测试数据，测试通过，不知道楼主的数据结构，楼主自己改
if object_id('table1') is not null drop table table1
go
select 1 as col1, 'a' as col2
into table1
union select 2, 'b'

if object_id('table2') is not null drop table table2
go
select 1 as col1, 'aa' as col2
into table2
union select 1, 'ab'
union select 1, 'ac'
union select 2, 'ba'
union select 2, 'bb'
union select 2, 'bc'
go

select a.col1, a.col2, b.col2
from table1 a join table2 b on a.col1 = b.col1
where b.col2 in (select top 2 col2 from table2 where col1 = a.col1)

drop table table1
drop table table2

[dulei115]

--测试数据，测试通过，不知道楼主的数据结构，楼主自己改
if object_id('table1') is not null drop table table1
go
select 1 as col1, 'a' as col2
into table1
union select 2, 'b'

if object_id('table2') is not null drop table table2
go
select 1 as col1, 'aa' as col2
into table2
union select 1, 'ab'
union select 1, 'ac'
union select 2, 'ba'
union select 2, 'bb'
union select 2, 'bc'
go

select a.col1, a.col2, b.col2
from table1 a join table2 b on a.col1 = b.col1
where b.col2 in (select top 2 col2 from table2 where col1 = a.col1)
/*
1 a aa
1 a ab
2 b ba
2 b bb
*/

drop table table1
drop table table2