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
)
and usr_id = 5201314520 and year(trx_time) = 2024;

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 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 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(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 usr_id,mch_nm,
 sum(trx_amt) as trx_amt,
 count(*) as trx_cnt,
 min(trx_time) as 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,min(trx_time) as first_time,mch_nm
from cmb_usr_trx_rcd
where usr_id = 5201314520 and mch_nm = '红玫瑰按摩保健休闲';

select mch_nm,sum(trx_amt) as sum_trx_amt
from cmb_usr_trx_rcd
where usr_id = 5201314520 and trx_time >= '2024-01-01'
group by mch_nm
order by sum_trx_amt desc;

select name,subject,class_code,enter_date
from teachers
where qualification is null and substring(enter_date,1,4) < 2010
order by enter_date;

select name,subject,class_code,graduate_date
from teachers
where substring(graduate_date,1,4) between 1994 and 1997
order by graduate_date;

select name,subject,class_code,qualification
from teachers
where (fir_degr = '北京大学'or fir_degr ='清华大学') and head_teacher is not null
order by name

select name,subject,class_code,qualification
from teachers
where subject = '数学' and qualification = 'Senior'
order by name;

select student_id,name,birth_date
from students
where gender = 'f' and substring(birth_date,1,4) = 2009
order by birth_date;

select
	case
	when sc.score >= 110 then '[110,120]'
when sc.score >= 90 then '[90,110)'
when sc.score >= 60 then '[60,90)'
else '[0,60)'
	end as score_range,
count(*) as num_students
from students s
join scores sc on s.student_id = sc.student_id
where sc.subject = '数学' and sc.exam_date = '2024-06-30'
group by score_range
order by score_range desc

with rank_score as(
	select s.student_id,s.name,sc.score,
rank()over(partition by s.grade_code order by sc.score desc) as ranking
from students s 
join scores sc on s.student_id = sc.student_id
where s.grade_code = 's1' and sc.subject = '物理'
)
select student_id,name,score,ranking
from rank_score
where ranking <= 10
order by ranking,student_id;

with rank_scores as (
 select s.student_id,
s.name,
sc.score,
row_number()over(partition by s.grade_code order by sc.score desc) as rnk
 from students s
 join scores sc on s.student_id = sc.student_id
 where s.grade_code = 'S1' and sc.subject = '物理'
)
select student_id,name,score,rnk
from rank_scores
where rnk <= 10
order by rnk,student_id

with rank_scores as (
 select s.student_id,
s.name,
sc.score,
row_number()over(partition by s.grade_code order by sc.score) as rnk
 from students s
 join scores sc on s.student_id = sc.student_id
 where s.grade_code = 'S1' and sc.subject = '物理'
)
select student_id,name,score,rnk
from rank_scores
where rnk <= 10
order by rnk,student_id

select left(name,1) as surname,
 count(*) as cnt
from students
group by left(name,1)
order by cnt desc
limit 5;

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;