sql查询每个类别的前多少条数据

最近有个需求，根据搜索日期的id查询出每个关键词商品的前多少条数据，经过研究写出了如下的sql

select * from tab_search_result a where keywordid in ('3','4') and searchid in ('52','53') and 30 > (select count(*) from tab_search_result  where  keywordid=a.keywordid and id< a.id ) 

解释一下，就是查询出某张表根据搜索关键词id和查询关键词id来找，各个对应的前30条数据
外层的in下面的可以用子查询，根据关键词名字来匹配并查询keyword表的id，搜索id也一样操作

最终三个表相互关联，不借助java代码层面的最终sql如下

select * from tab_search_result a where keywordid in (
SELECT id from tab_keyword where keyword in('phone','clothes')
) and searchid in (
select id from tab_search where keywordid in (SELECT id from tab_keyword where keyword in('phone','clothes')) and searchdate like '2022-06-17%'
)
 and 30 > (select count(*) from tab_search_result  where  keywordid=a.keywordid and id< a.id ) 

如过想要过滤某个条件，如结果表xxx字段为xx是如下写法

select * from tab_search_result a where keywordid in (
SELECT id from tab_keyword where keyword in('phone','clothes')
) and searchid in (
select id from tab_search where keywordid in (SELECT id from tab_keyword where keyword in('phone','clothes')) and searchdate like '2022-06-17%'
) and xxx ='xx'
 and 30 > (select count(*) from tab_search_result  where  keywordid=a.keywordid and id< a.id and xxx ='xx')

写完发现这个sql太复杂了，全是子的sql，效率不是特别高，如果有可以优化的想法欢迎在下面留言