问一个关于sql 查询 的问题

[wuxing2006]

问一个关于sql 的问题
表 fair_comp
id name
1 test1
2 test2

表 comp_booth
id fair_comp_id name
1 1 booth1
2 1 booth2
3 1 booth3
4 2 booth4

当comp_booth里的记录条数大于2时，fair_comp显示为两条
希望结果为
select * from fair_comp .....
id name
1 test1
1 test1
2 test2

盼解

[doer_ljy]

[Quote=引用 2 楼 Robin_Ares 的回复:]
Select a.* from
fair_comp a,
(select fair_comp_id ,
row_number() over (patition by fair_comp_id order by fair_comp_id ) row_number
from comp_booth
) b
where a.id = b.fair_comp_id
and b.row_number < 3
[/Quote]
支持

[wuxing2006]

这是一份迟来的爱 给分

[WangZWang]

Select a.* from fair_comp a,comp_booth b
where a.id=b.fair_comp_id and 
     (Select count(*) from comp_booth where id>b.id)<=2

[robin_ares]

Select a.* from
fair_comp a,
(select fair_comp_id ,
row_number() over (patition by fair_comp_id order by fair_comp_id ) row_number
from comp_booth
) b
where a.id = b.fair_comp_id
and b.row_number < 3