select * from hand_permutations 
where left (card1,1)=left(card2,1)

select * from hand_permutations
 where right(card1,1)=right(card2,1)
 and (card1 like "A%" or card2 like "A%")

select * from hand_permutations
where (card1 like "A%" and card2 like "K%")
or (card1 like "A%" and card2 like "A%")
or (card1 like "K%" and card2 like "A%")
or (card1 like "K%" and card2 like "K%")

select *
from cmb_usr_trx_rcd
where usr_id="5201314520"
and date(trx_time) between "2024-9-1" and "2024-9-30"

select 
case when mch_nm rlike "按摩保健休闲" then "按摩保健休闲"
when mch_nm rlike "按摩|保健|休闲|养生|SPA|会所" then "按摩、保健、休闲、养生、SPA、会所"
else null
end as reg_rules,
count(distinct 	mch_nm) mch_cnt
from cmb_usr_trx_rcd
group by reg_rules
having reg_rules is not null

select "按摩、保健、休闲、养生、SPA、会所" reg_rules,
count(distinct 	mch_nm) mch_cnt
from cmb_usr_trx_rcd
where 	mch_nm rlike "按摩|保健|休闲|养生|SPA|会所"
union
select "按摩保健休闲" reg_rules,
count(distinct 	mch_nm) mch_cnt
from cmb_usr_trx_rcd
where 	mch_nm rlike "按摩保健休闲"
order by reg_rules

select "按摩、保健、休闲、养生、SPA、会所" reg_rules,
count(distinct 	mch_nm) mch_cnt
from cmb_usr_trx_rcd
where 	mch_nm rlike "按摩|保健|休闲|养生|SPA|会所"
union
select "按摩保健休闲" reg_rules,
count(distinct 	mch_nm) mch_cnt
from cmb_usr_trx_rcd
where 	mch_nm rlike "按摩保健休闲"
order by reg_rules desc

select count(distinct mch_nm) as mch_cnt
from cmb_usr_trx_rcd
where mch_nm like "%按摩保健休闲%"

select count(distinct mch_nm like "%按摩保健休闲%") mch_cnt
from cmb_usr_trx_rcd

select count(distinct mch_nm like "%按摩保健休闲%")
from cmb_usr_trx_rcd

select 
case when 	trx_amt=288 then "1.WithHand"
when trx_amt=388 then "2.WithMimi"
when trx_amt=588 then "3.BlowJobbie"
when trx_amt=888 then "4.Doi"
when trx_amt=1288 then "5.DoubleFly"
else "6.other"
end as ser_typ,
count(*) trx_cnt, 
min(date(trx_time)) first_date
from cmb_usr_trx_rcd
where 	mch_nm like "红玫瑰按摩保健休闲"
and usr_id=5201314520
group by ser_typ
order by ser_typ

select 
case when 	trx_amt=288 then "1.WithHand"
when trx_amt=388 then "2.WithMimi"
when trx_amt=588 then "3.BlowJobbie"
when trx_amt=888 then "4.Doi"
when trx_amt=1288 then "5.DoubleFly"
else "6.other"
end as ser_typ,
count(*) trx_cnt, 
min(date(trx_time)) first_date
from cmb_usr_trx_rcd
where 	mch_nm like "红玫瑰按摩保健休闲"
group by ser_typ
having ser_typ!="6.other"
order by ser_typ

select usr_id,
mch_nm,
sum(trx_amt),
count(*) trx_cnt,
min(trx_time) first_time
from cmb_usr_trx_rcd
where usr_id=5201314520
and trx_amt>=288
group by mch_nm
order by trx_cnt desc

select 
usr_id,
	trx_time first_time,
mch_nm
from cmb_usr_trx_rcd 
where mch_nm="红玫瑰按摩保健休闲"
and usr_id=5201314520
order by trx_time
limit 1

select 
usr_id,
	trx_time first_time,
mch_nm
from cmb_usr_trx_rcd 
where mch_nm="红玫瑰按摩保健休闲"
order by trx_time
limit 1

select trx_amt,
count(*) trx_cnt,
count(distinct usr_id) usr_cnt,
count(*)/count(distinct usr_id) avg
from cmb_usr_trx_rcd
where mch_nm="红玫瑰按摩保健休闲"
and date(trx_time) between "2023-01-01" and "2024-06-30"
group by trx_amt
order by avg desc
limit 5

select trx_amt, 
count(1) as trx_cnt
from cmb_usr_trx_rcd
where date(trx_time) between "2024-01-01" and "2024-07-31"
and mch_nm="红玫瑰按摩保健休闲"
group by trx_amt
order by trx_cnt desc
limit 5

select trx_amt, 
count(1) as trx_cnt
from cmb_usr_trx_rcd
where date(trx_time) between "2024-01-01" and "2024-07-31"
group by trx_amt
order by trx_cnt desc
limit 5

select trx_amt, 
count(*) as trx_cnt
from cmb_usr_trx_rcd
where date(trx_time) between "2024-01-01" and "2024-07-31"
group by trx_amt
order by trx_cnt desc
limit 5

select trx_amt, 
count(*) 
from cmb_usr_trx_rcd
where year(trx_time)=2024
and month(trx_time) between 1 and 7
group by trx_amt
order by count(*) desc
limit 5