SELECT class_code, COUNT(student_id) AS student_count
FROM students
GROUP BY class_code
HAVING COUNT(student_id) > 10
ORDER BY student_count DESC;

select class_code ,
 count(1)
from students 
group by class_code
order by class_code asc

select goods_id ,sum(order_gmv) 
from order_info 
where date(order_time)='2024-09-10'
group by goods_id
order by sum(order_gmv) desc
limit 10

select a.mch_nm,a.trx_cnt,b.mch_nm from 
(select mch_nm,count(1) trx_cnt
from cmb_usr_trx_rcd
where usr_id='5201314520' and year(trx_time) in (2023,2024)
group by mch_nm
having count(1)>=20) a 
left join
(select mch_nm, count(1) trx_cnt
from cmb_usr_trx_rcd
where usr_id='5211314521'and year(trx_time) in (2023,2024)
group by mch_nm) b
on a.mch_nm=b.mch_nm
order by a.trx_cnt desc

select a.mch_nm,a.trx_cnt,b.mch_nm from 
(select mch_nm,count(1) trx_cnt
from cmb_usr_trx_rcd
where usr_id='5201314520' and year(trx_time) in (2023,2024)
group by mch_nm
having count(1)>=20) a 
left join
(select mch_nm, count(1) trx_cnt
from cmb_usr_trx_rcd
where usr_id='5211314521'and year(trx_time) in (2023,2024)
group by mch_nm) b
on a.mch_nm=b.mch_nm
order by a.trx_cnt

select a.mch_nm from
(selectdistinct mch_nm
 from cmb_usr_trx_rcd
 where usr_id='5201314520' and year(trx_time)=2024)a
inner join 
(selectdistinct mch_nm
 from cmb_usr_trx_rcd
 where usr_id='5211314521' and year(trx_time)=2024)b
on a.mch_nm=b.mch_nm
order by a.mch_nm desc

select a.mch_nm from
(selectdistinct mch_nm
 from cmb_usr_trx_rcd
 where usr_id='5201314520' and year(trx_time)=2024)a
inner join 
(selectdistinct mch_nm
 from cmb_usr_trx_rcd
 where usr_id='5211314521' and year(trx_time)=2024)b
on a.mch_nm=b.mch_nm
order by a.mch_nm

select a.mch_nm from
(selectdistinct mch_nm
 from cmb_usr_trx_rcd
 where usr_id='5201314520' and year(trx_time)=2024)a
inner join 
(selectdistinct mch_nm
 from cmb_usr_trx_rcd
 where usr_id='5211314521' and year(trx_time)=2024)b
on a.mch_nm=b.mch_nm
order by 1

select a.* from
(selectdistinct mch_nm
 from cmb_usr_trx_rcd
 where usr_id='5201314520' and year(trx_time)=2024)a
inner join 
(selectdistinct mch_nm
 from cmb_usr_trx_rcd
 where usr_id='5211314521' and year(trx_time)=2024)b
on a.mch_nm=b.mch_nm
order by 1

select a.* from
(selectdistinct mch_nm
 from cmb_usr_trx_rcd
 where usr_id='5201314520' and year(trx_time)=2024)a
inner join 
(selectdistinct mch_nm
 from cmb_usr_trx_rcd
 where usr_id='5211314521' and year(trx_time)=2024)b
on a.mch_nm=b.mch_nm

select a.* from
(selectdistinct mch_nm
 from cmb_usr_trx_rcd
 where usr_id='5201314520' and year(trx_time)=2024
)a
inner join 
(selectdistinct mch_nm
 from cmb_usr_trx_rcd
 where usr_id='5211314521' and year(trx_time)=2024
)b
on a.mch_nm=b.mch_nm

select a.* from
(selectdistinct(mch_nm)
 from cmb_usr_trx_rcd
 where usr_id='5201314520' and year(trx_time)=2024
)a
inner join 
(selectdistinct(mch_nm)
 from cmb_usr_trx_rcd
 where usr_id='5211314521' and year(trx_time)=2024
)b
on a.mch_nm=b.mch_nm

select a.mch_nm from
(selectdistinct(mch_nm)
 from cmb_usr_trx_rcd
 where usr_id='5201314520' and year(trx_time)=2024
)a
inner join 
(selectdistinct(mch_nm)
 from cmb_usr_trx_rcd
 where usr_id='5211314521' and year(trx_time)=2024
)b
on a.mch_nm=b.mch_nm

select * 
from cmb_usr_trx_rcd
where 
 trx_amt=(
 select max(trx_amt)
from cmb_usr_trx_rcd 
where usr_id='5201314520'
and year(trx_time)=2024
)
andusr_id='5201314520'
andyear(trx_time)=2024

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

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

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

select count(distinct(mch_nm))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 
casewhen 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.qita'
end as ser_typ,
count(1) trx_cnt,
min(date(trx_time)) first_date
from cmb_usr_trx_rcd
where usr_id='5201314520'
and mch_nm='红玫瑰按摩保健休闲'
group by ser_typ
order by ser_typ