做了一个投票网页,第二阶段需要显示投票前100名的数据,但是mysql中update不支持limit m,n的格式写法,不能更新除了前100名数据的delete字段,delete字段用来做软删除,就是说首页只显示delete字段不为null的数据,如何用一条语句搞定这个问题?
先
SELECT num_of_votes FROM tb_name ORDER BY num_of_votes DESC
然后取结果中第一百条数据的num_of_votes
字段值赋给变量threshold
最后
UPDATE tb_name SET delete = some_value WHERE num_of_votes >= threshold
===============================================================
如果用一条语句的话:
UPDATE tb_name SET delete = some_value WHERE num_of_votes IN (SELECT num_of_votes FROM tb_name ORDER BY num_of_votes DESC LIMIT 0,100)