单表查询

SELECT DISTINCT COUNT(*) AS count, lid FROM user_label GROUP BY lid ORDER BY count DESC LIMIT 0, 10;

联表查询user_label 用户标签关联表(uid, lid), label 标签表(lid, name)

SELECT DISTINCT count(u.lid) AS count, u.lid, l.name FROM `label` AS l INNER JOIN `user_label` AS u ON u.lid=l.lid GROUP BY u.lid ORDER BY count DESC LIMIT 0, 10