最近要处理几个表的数据统计与错误数据的更新处理,一新来的伙伴写了几条sql更新处理语句,对于小量数据处理没问题,但是如果几万数据处理是非常的慢,只拿来了看看.

联合查询回复数,代码如下:

SELECT ask.id, count( answer.father_id ) , ask.answer_num FROM `表名_ask` AS ask LEFT JOIN `表名_answer` AS answer ON ask.id = answer.father_id GROUP BY answer.father_id ORDER BY ask.id ASC

修改城市ID,代码如下:

UPDATE 表名_ask SET cityid = ( SELECT cityid FROM 表名 WHERE 表名_ask.borough_id = 表名.id ORDER BY id ASC LIMIT 0, 1 )

修改回复数,代码如下:

UPDATE 表名_ask SET answer_num = ( SELECT count( 表名_answer.father_id ) FROM `表名_answer` where 表名_ask.id = 表名_answer.father_id GROUP BY 表 answer.father_id ORDER BY 表名_ask.id ASC LIMIT 0 , 1 ) //开源代码www.xiariboke.net

最后:建议一下如果有可能我们还是把sql分开来处理这样会更好一些,本站原创文章转截必须注明来源http://www.xiariboke.net.