mysql排序数据重复问题,查找原因发现,每次接口请求数据库创建新的session,sql如下
select s.singer_name,p.*,o.* from
( select a.mic_id,a.singer_id,a.mic_name,mic_coverurl,mic_sing_state,mic_url,b.play_sum
from a_music a left JOIN a_music_amount b on a.ma_id = b.ma_id ORDER BY b.mic_hitsum desc) p
LEFT JOIN ( select c.cm_id,c.u_id,c.mic_id from c_collect_music c left join a_user d on c.u_id=d.u_id WHERE d.u_id=1)o
on p.mic_id = o.mic_id left join a_singer s on p.singer_id=s.singer_id order by p.playsum ASC limit 0,10
SQL在排序时playsum保持数据初始化为0, 在加入分页时查询数据创建新的session会导致每个用户所看到的数据不同,虽然加入了排序,解决办法,进行双排排序,先进行想要的数据进行排序,在按照其他要求排序,以确保数据的排序问题。
select s.singer_name,p.*,o.* from
( select a.mic_id,a.singer_id,a.mic_name,mic_coverurl,mic_sing_state,mic_url,b.play_sum
from a_music a left JOIN a_music_amount b on a.ma_id = b.ma_id) p
LEFT JOIN ( select c.cm_id,c.u_id,c.mic_id from c_collect_music c left join a_user d on c.u_id=d.u_id WHERE d.u_id=1)o
on p.mic_id = o.mic_id left join a_singer s on p.singer_id=s.singer_id order by p.play_sum ASC ,p.mic_id ASC limit 0,10