select user_id,
count(user_id) visit_nums
from visit_tb
where user_id in (select distinct user_id from order_tb where date(visit_time)='2022-09-02' and date(leave_time)='2022-09-02' and date(order_time)='2022-09-02')
group by user_id
order by visit_nums desc

子查询在WHERE子句中作为筛选条件出现，首先找出满足条件的id，然后再根据这个id去另一个表找访问记录次数