select
g1.gd_id,gd_nm,gd_typ
from
gd_inf g1
join
xhs_pchs_rcd g2
on g1.gd_id=g2.mch_id
left join
xhs_fav_rcd g3
on g1.gd_id=g3.mch_id
where g3.cust_uid isnull
group by 1,2,3;
select
g1.gd_id,gd_nm,gd_typ
from
gd_inf g1
join
xhs_pchs_rcd g2
on g1.gd_id=g2.mch_id
left join
xhs_fav_rcd g3
on g1.gd_id=g3.mch_id
where g3.cust_uid is not null
group by 1,2,3;
select
g1.gd_id,gd_nm,gd_typ
from
gd_inf g1
join
xhs_pchs_rcd g2
on g1.gd_id=g2.mch_id
left join
xhs_fav_rcd g3
on g1.gd_id=g3.mch_id
where g3.cust_uid is not null
;
select
g1.gd_id,g1.gd_nm,g1.gd_typ
from
gd_inf g1
left join
xhs_fav_rcd g2
on g1.gd_id=g2.mch_id
left join
xhs_pchs_rcd g3
on g1.gd_id=g3.mch_id
where
g2.cust_uid is not null
and g3.cust_uid is null
group by 1,2,3
;
select
g1.gd_id,g1.gd_nm,count(cust_uid)
from
gd_inf g1
left join
xhs_fav_rcd g2
on g1.gd_id=g2.mch_id
group by
g1.gd_id,g1.gd_nm
order by 3 desc
limit 1
;
select
r1.cust_uid,r2.cust_uid
from
mt_trx_rcd1 r1
left join mt_trx_rcd1 r2
on
r1.cust_uid='MT10000'
and
r1.mch_nm=r2.mch_nm
and
r1.cust_uid!=r2.cust_uid
group by 1,2
having count(distinct r2.mch_nm)>=14
;
select
r1.cust_uid,
r2.cust_uid
from mt_trx_rcd1 r1
left join mt_trx_rcd1 r2
on r1.cust_uid='MT10000' and r2.cust_uid!='MT10000'
group by 1,2
having
max(case when r2.mch_nm='庄家界(千灯店)'then 1 else 0 end )+
max(case when r2.mch_nm='黄记烘培宫廷桃酥王'then 1 else 0 end )+
max(case when r2.mch_nm='品众素心素食餐厅'then 1 else 0 end )+
max(case when r2.mch_nm='一枚帅哥做的菜'then 1 else 0 end )=4
select
r1.cust_uid,
r2.cust_uid
from mt_trx_rcd1 r1
left join mt_trx_rcd1 r2
on r1.cust_uid='MT10000' and r2.cust_uid!='MT10000'
group by 1,2
having max(case when r2.mch_nm='庄家界(千灯店)'then 1 else 0 end )+max(case when r2.mch_nm='黄记烘培宫廷桃酥王'then 1 else 0 end )+max(case when r2.mch_nm='品众素心素食餐厅'then 1 else 0 end )=3
select
r1.cust_uid,
r2.cust_uid
from mt_trx_rcd1 r1
left join mt_trx_rcd1 r2
on r1.cust_uid='MT10000'
group by 1,2
having max(case when r2.mch_nm='庄家界(千灯店)'then 1 else 0 end )+max(case when r2.mch_nm='黄记烘培宫廷桃酥王'then 1 else 0 end )+max(case when r2.mch_nm='品众素心素食餐厅'then 1 else 0 end )=3
with uid2 as (
select cust_uid
from
mt_trx_rcd1
where
mch_nm like '庄家界(千灯店)' or mch_nm like '黄记烘培宫廷桃酥王'
group by 1
having count(distinct mch_nm)>=2
)
select
r1.cust_uid,r2.cust_uid
from
mt_trx_rcd1 r1
inner join
uid2 r2
on r1.cust_uid='MT10000' and r2.cust_uid!='MT10000'
group by 1,2
with uid2 as (
select cust_uid
from
mt_trx_rcd1
where
mch_nm rlike '庄家界(千灯店)|黄记烘培宫廷桃酥王'
group by 1
having count(1)>=2
)
select
r1.cust_uid,
r2.cust_uid
from mt_trx_rcd1 r1
inner join uid2 r2
on r1.cust_uid='MT10000'
group by 1,2
order by 2 asc
select
r1.cust_uid,r2.cust_uid
from
mt_trx_rcd1 r1
inner join
mt_trx_rcd1 r2
on
r1.cust_uid='MT10000'
and
r2.mch_nm rlike '庄家界(千灯店)|黄记烘培宫廷桃酥王'
group by 1,2
order by 2 asc;
select
r1.cust_uid,r2.cust_uid
from
mt_trx_rcd1 r1
inner join
mt_trx_rcd1 r2
on
r1.cust_uid='MT10000'
and
r2.mch_nm rlike '庄家界(千灯店)|黄记烘培宫廷桃酥王'
group by 1,2;
select
r1.cust_uid,r2.cust_uid,r1.mch_nm
from mt_trx_rcd1 r1
inner join mt_trx_rcd1 r2
on r1.mch_nm=r2.mch_nm
where
r1.mch_nm='兰州李晓明拉面馆'
and
r1.cust_uid='MT10000'
and
r2.cust_uid!='MT10000'
group by 1,2,3
order by 2 asc;
select
r1.cust_uid,r2.cust_uid,r1.mch_nm
from mt_trx_rcd1 r1
inner join mt_trx_rcd1 r2
on r1.mch_nm=r2.mch_nm
where
r1.mch_nm='兰州李晓明拉面馆'
and
r1.cust_uid='MT10000'
and
r2.cust_uid!='MT10000'
order by 2 asc;
select
r1.cust_uid,r2.cust_uid,r1.mch_nm
from mt_trx_rcd1 r1
inner join mt_trx_rcd1 r2
on r1.mch_nm=r2.mch_nm
where
r1.mch_nm='兰州李晓明拉面馆'
and
r1.cust_uid='MT10000'
and
r2.cust_uid!='MT10000'
;