有个用户表user,里面有字段id,province_id 省份表province,里面有字段id,province_name
求用户最多的三个省份
方法一:
SELECT NAME, count(provice_id) FROM user1 t1 INNER JOIN province t2 ON t1.provice_id = t2.id GROUP BY t2.`name` ORDER BY count(provice_id) DESC LIMIT 3
方法二:
SELECT NAME,user_count.count FROM province, ( SELECT count(*) count, provice_id FROM user1 GROUP BY provice_id ) user_count WHERE user_count.provice_id = province.id ORDER BY user_count.count desc LIMIT 3
还有效率更高的写法网友可以留言。