select t1.id, t1.pool_id, t1.amount, t1.packet_num, t1.packet_type, t1.expired_time,t1.uid
FROM `card_expired`AS t1
JOIN (SELECT ROUND(RAND() *
((SELECT MAX(id) FROM `card_expired` where amount = #{amount} and uid = 0)
- (SELECT MIN(id) FROM `card_expired`))
+ (SELECT MIN(id) FROM `card_expired`) ) AS id) AS t2
WHERE t1.id >= t2.id and t1.amount = #{amount} and t1.uid = 0
ORDER BY t1.id LIMIT 1;
思路:id必须是自增的,采用随机数来限制一个查询范围(如果id直接不连续也没有关系,后续逻辑加一个保底的sql就可以了)如果效率不高则需自行调优(加索引,改sql)
select <include refid="Base_Column_List"></include>
from card_expired where amount = #{amount,jdbcType=INTEGER} and uid = 0 LIMIT 1