求一条SQL查询语句

xjchen 2006-02-22 05:29:49
两张表 users 和 news 关联
users里的用户可以发布信息保存在news里
现在要取出一个记录集, 要求返回news 里的记录, 但每个用户最多取10条信息,要如何去写这个查询语句?
...全文
57 点赞 收藏 2
写回复
2 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
dulei115 2006-02-22
--测试数据,测试通过,不知道楼主的数据结构,楼主自己改
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
回复
dulei115 2006-02-22
测试数据,测试通过,不知道楼主的数据结构,楼主自己改
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
回复
相关推荐
发帖
疑难问题
创建于2007-09-28

2.1w+

社区成员

MS-SQL Server 疑难问题
申请成为版主
帖子事件
创建了帖子
2006-02-22 05:29
社区公告
暂无公告