select 
s.name, s.class_code, s.grade_code, t.name AS head_teacher_name
from students s 
join teachers t 
on s.class_code = t.head_teacher 
order by s.student_id

select 
* 
from students s 
join teachers t 
on s.class_code = t.head_teacher 
order by s.student_id

select 
ts_code, 
    open_price, 
    close_price
from 
daily_stock_prices
where 
	trade_date = '2023-10-09' 
	and price_change>0.1

select
*
from hand_permutations
where 
 concat(card1,card2) like	"%A%A%"
ORconcat(card1,card2) like	"%A%K%"
ORconcat(card1,card2) like	"%K%K%"
ORconcat(card1,card2) like	"%K%A%"
order by id

select
a.name,a.class_code,a.grade_code,c.name
from students a 
left jointeachers c 
 on a.class_code = c.head_teacher
order by a.student_id

select
a.name,a.class_code,a.grade_code,c.name
from students a 
joinscores b
 ona.student_id= b.student_id
left jointeachers c 
 on a.class_code = c.class_code
order by a.student_id

select
usr_id,mch_nm,trx_time,max(trx_amt)
from cmb_usr_trx_rcd
where usr_id ='5201314520'
and substring(trx_time,1,4) = '2024'
group by 1,2,3
order by 4 desc
limit 1

select
    case 
        when mch_nm like '%按摩保健休闲%' then '按摩保健休闲'
        when lower(mch_nm) rlike '.*(按摩|保健|休闲|spa|养生|会所).*' then '按摩、保健、休闲、养生、SPA、会所'
    end as reg_rules,
    count(distinct mch_nm) as mch_cnt
from
    cmb_usr_trx_rcd
where mch_nm like '%按摩保健休闲%'
   or lower(mch_nm) rlike '.*(按摩|保健|休闲|spa|养生|会所).*'
group by reg_rules
order by mch_cnt desc;

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

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

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

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' endser_typ
 ,sum(1) as trx_cnt,min(date(trx_time)) as first_date
from cmb_usr_trx_rcd 
where usr_id = '5201314520'
and mch_nm = '红玫瑰按摩保健休闲'
group byser_typ
order byser_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 'other' endser_typ
 ,sum(1) as trx_cnt,min(date(trx_time)) as first_date
from cmb_usr_trx_rcd 
where usr_id = '5201314520'
and mch_nm = '红玫瑰按摩保健休闲'
group byser_typ
order byser_typ

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

select
usr_id,mch_nm,sum(trx_amt) as trx_amt ,sum(1) as trx_cnt, min(trx_time) as first_time
from cmb_usr_trx_rcd
where usr_id ='5201314520'
and trx_amt >=288
group by 1,2
order by 4 desc

select
usr_id,mch_nm,sum(trx_amt) as trx_amt ,sum(1) as trx_cnt, min(trx_time) as first_time
from cmb_usr_trx_rcd
where usr_id ='5201314520'
and trx_amt >=288
group by 1,2
order by 4

select
    usr_id,
    min(trx_time) as first_time,  
    mch_nm
from
    cmb_usr_trx_rcd
where
    mch_nm = '红玫瑰按摩保健休闲'
    and usr_id = '5201314520'
group by 1,3

select
    usr_id,
    max(trx_time) as first_time,  
    mch_nm
from
    cmb_usr_trx_rcd
where
    mch_nm = '红玫瑰按摩保健休闲'
    and usr_id = '5201314520'

select
    usr_id,
    max(trx_time) as first_time,  
    mch_nm
from
    cmb_usr_trx_rcd
where
    mch_nm = '红玫瑰按摩保健休闲'
    and usr_id = '5201314520'
group by
    usr_id, mch_nm;

select
usr_id,min(trx_time),mch_nm
from cmb_usr_trx_rcd
where usr_id = '5201314520'
and mch_nm = '红玫瑰按摩保健休闲'