SQL1:
select id
,user_role_id
,pet_id
,pet_lv
,pet_lv_exp
,pet_rank
,pet_rank_exp
,pet_rank_fail
,pet_rank_update_time
,create_time
from
role_pet
where
user_role_id in(
select
case when count(*) >= 3 then user_role_id else '' end as user_role_id
from
role_pet
GROUP BY user_role_id
);
SQL2:
select id
,a.user_role_id
,pet_id
,pet_lv
,pet_lv_exp
,pet_rank
,pet_rank_exp
,pet_rank_fail
,pet_rank_update_time
,create_time
from
role_pet a, (select user_role_id, count(*) petcount from role_pet GROUP BY user_role_id having petcount >= 3) b where a.user_role_id = b.user_role_id
4210 rows in set, 1 warning (57.01 sec)
4210 rows in set (0.03 sec)