需求,查询记录数大于20的user_id的所有数据
第一种 子查询+in查询:
SELECT * FROM
`table_name`
WHERE user_id in (SELECT user_id FROM `table_name` group by user_id having count(user_id) > 10)
第二种 子查询+join查询:
SELECT * FROM
`table_name` AS T
JOIN (SELECT user_id, count(1) as num FROM `table_name` group by user_id having num > 10) AS M ON T.user_id = M.user_id
经过测试,子查询+join查询效率更高!