问一个关于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盼解
表 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盼解
where a.id=b.fair_comp_id and
(Select count(*) from comp_booth where id>b.id)<=2
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