CREATE DATABASE test;
use test;
CREATE TABLE page_log (
uid varchar(10),
time varchar(30),
page_name varchar(30),
refer_page varchar(30)
);
INSERT INTO page_log(uid, time, page_name, refer_page) VALUES
('a','2024-01-01','index_home_page','login_page'),
('a','2024-01-01','app_interest_select','index_home_page'),
('b','2024-01-01','cp_page_live','index_home_page'),
('c','2024-01-01','index_home_page','login_page'),
('e','2024-01-01','app_interest_select','index_home_page'),
('d','2024-01-01','cp_page_live','index_home_page'),
('a','2024-01-02','app_interest_select','cp_page_live'),
('a','2024-01-02','index_home_page','cp_page_live'),
('b','2024-01-02','cp_page_live','index_home_page'),
('c','2024-01-02','index_home_page','login_page'),
('c','2024-01-02','app_interest_select','index_logout_page'),
('d','2024-01-02','cp_page_live','index_home_page');
select uid
from(
select uid,time
from page_log
where page_name='index_home_page' or refer_page='index_home_page'
group by time,uid) as t
group by uid
having count(time)=2;
select uid
from(
select uid,time
from page_log
where page_name='index_home_page'
group by time,uid) as t
group by uid
having count(time)=2;
select uid
from(
select uid,time
from page_log
where refer_page='index_home_page'
group by time,uid) as t
group by uid
having count(time)=2;