业务中遇到一个语句:

类似:

(SELECT * FROM xtable WHERE xx=100500137 ORDER BY xid asc limit 10)

union all (SELECT * FROM xtable WHERE xx=104546692 ORDER BY xid asc imit 10)

union all (SELECT * FROM xtable WHERE xx=109000327 ORDER BY xid asc limit 10)

union all (SELECT * FROM xtable WHERE xx=110084551 ORDER BY xid asc limit 10)

union all (SELECT * FROM xtable WHERE xx=110186680 ORDER BY xid asc limit 10)

union all (SELECT * FROM xtable WHERE xx=110748205 ORDER BY xid asc limit 10);


业务上是根据xx分组,每个组中按前10个xid排序,组内必须有序,组间不必有序,xx可能有几十个;


首先想到是group by  但是group by 不能limit每个分组数目数目


后面想到一个方式对每个xid 取一个序号,然后控制序号的偏移量;

select * from (

select  t1.*,(select count(*)+1 from tmp t where t.xx=t1.xx and t.xid<t1.xid) as group_id from tmp t1 where t1.xx in(100500137,104546692,109000327,110084551,110186680,110748205)

) tbl where tbl.group_id<=10;


效率上如果在xx比较小的情况下union all比较有优势,但是如果xx在相对比较多得情况下后面一种方式比较有优势,xx,xid上有联合索引!







Logo

更多推荐