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

select name, subject, class_code,qualification
from teachers
where head_teacher like 'C%'
and (fir_degr = '北京大学' or fir_degr = '清华大学')
order by name;

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

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

select student_id, name, birth_date
from students
where birth_date between '2009-1-1' and '2009-12-31'
and gender = 'f'
order by birth_date;

with ranked_scores as (
select
	a.student_id,
a.name,
b.score,
rank() over(order by b.score desc) as ranking
from 
	students a left join scores b on a.student_id = b.student_id
where
	a.grade_code = 'S1'
and b.subject = '物理'
)
select student_id, name, score, ranking
from ranked_scores
where ranking <= 10
order by ranking

select
	a.student_id,
a.name,
b.score,
rank() over(order by b.score desc) as ranking
from 
	students a left join scores b on a.student_id = b.student_id
where
	a.grade_code = 'S1'
and b.subject = '物理'
order by b.score desc
limit 10;

select 
	a.student_id,
a.name,
b.score,
row_number()over(partition by a.grade_code order by b.score desc) as rnk
from students a left join scores b on a.student_id = b.student_id
where b.subject = '物理' and a.grade_code = 'S1'
order by b.score desc
limit 10;

select 
	a.student_id,
a.name,
b.score,
row_number()over(partition by a.grade_code order by b.score desc) as rnk
from students a left join scores b on a.student_id = b.student_id
where b.subject = '物理' and a.grade_code = 'S1'
order by b.score desc;

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

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

select a.name, a.class_code, a.grade_code, b.name as head_teacher_name
from students a inner join teachers b
where a.class_code = b.head_teacher
order by a.student_id
;

select a.name, a.class_code, a.grade_code, b.name as head_teacher_name
from students a inner join teachers b
where a.class_code = b.head_teacher
order by a.student_id
limit 5;

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

select
	goods_id as oods_id,
sum(distinct order_gmv) as total_gmv
from order_info 
where date(order_time) = '2024-9-10'
group by oods_id
order by total_gmv desc
limit 10;

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

select 
	case
	when mch_nm like '%按摩保健休闲%' then '按摩保健休闲'
when 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 byreg_rules
order by mch_cnt desc;

select * from cmb_usr_trx_rcd 
where usr_id = 5201314520
	and date(trx_time) between '2024-9-1' and '2024-9-30'
and (
(hour(trx_time) between 22 and 23)
or (hour(trx_time) between 0 and 5)
)
;

select * from cmb_usr_trx_rcd 
where usr_id = 5201314520
	and date(trx_time) between '2024-9-1' and '2024-9-30'
and (
(hour(trx_time) between 22 and 23)
or (hour(trx_time) between 1 and 5)
)
;

select * from cmb_usr_trx_rcd 
where date(trx_time) between '2024-9-1' and '2024-9-30'
and hour(trx_time) between 1 and 5
and usr_id = 5201314520;