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

select trx_amt,
		count(1) as total_trx_cnt,
count(distinct usr_id) as unique_usr_cnt,
 (count(1) / count(distinct usr_id)) as avg_trx_per_user
from cmb_usr_trx_rcd
where mch_nm = '红玫瑰按摩保健休闲'
	and trx_time between '2023-01-01' and '2024-07-01'
group by trx_amt
order by avg_trx_per_user desc
limit 5

select trx_amt,
		count(1) as total_trx_cnt,
count(distinct usr_id) as unique_usr_cnt,
 (count(1) / count(distinct usr_id)) as avg_trx_per_user
from cmb_usr_trx_rcd
where mch_nm = '红玫瑰按摩保健休闲'
	and trx_time between '2023-01-01' and '2024-07-01'
group by trx_amt
order by avg_trx_per_user desc

select trx_amt,
		count(*) as trx_cnt
from cmb_usr_trx_rcd 
where mch_nm ='红玫瑰按摩保健休闲'
and year(trx_time)=2024
and month(trx_time) in(1,2,3,4,5,6,7)
group by trx_amt
order by trx_cnt desc
limit 5

select trx_amt,
		count(*) as trx_cnt
from cmb_usr_trx_rcd 
where mch_nm ='红玫瑰按摩保健休闲'
and month(trx_time) between '2024-01' and '2024-07'
group by trx_amt
order by trx_cnt

select
    date(trx_time) as trx_date, 
    max(trx_amt) as max_trx_amt, 
    min(trx_amt) as min_trx_amt, 
    avg(trx_amt) as avg_trx_amt, 
    sum(trx_amt) as total_trx_amt 
from
    cmb_usr_trx_rcd
where
    mch_nm = '红玫瑰按摩保健休闲' 
    and date(trx_time) between '2024-09-01' and '2024-09-30' 
group by
    date(trx_time) 
order by
    trx_date;

select date(trx_time) as trx_date,
 			max(trx_amt) as max_trx_amt,
min(trx_amt) as min_trx_amt,
avg(trx_amt) as avg_trx_amt,
sum(trx_amt) as total_trx_amt
 from cmb_usr_trx_rcd
 where trx_time between '2024-09-01' and '2024-09-30'
 	andmch_nm = "红玫瑰按摩保健休闲"
 group by date(trx_time)
 order by date(trx_time)

select date(trx_time) as trx_date,
 			max(trx_amt) as max_trx_amt,
min(trx_amt) as min_trx_amt,
avg(trx_amt) as avg_trx_amt,
sum(trx_amt) as total_trx_amt
 from cmb_usr_trx_rcd
 where date(trx_time) between '2024-09-01' and '2024-09-30'
 	andmch_nm = "红玫瑰按摩保健休闲"
 group by date(trx_time)
 order by trx_date

select date(trx_time) as trx_date,
 			max(trx_amt) as max_trx_amt,
min(trx_amt) as min_trx_amt,
avg(trx_amt) as avg_trx_amt,
sum(trx_amt) as total_trx_amt
 from cmb_usr_trx_rcd
 where trx_time between '2024-09-01' and '2024-09-30'
 group by date(trx_time)
 order by date(trx_time)

select student_id,name,score,ranking
from
(select s.student_id,s.name,sc.score,
		rank() over(
order by sc.score desc)as ranking
from students s
join scores sc using(student_id)
where s.grade_code = 'S1' and sc.subject = '物理') as t
where ranking <= 10
order by ranking ,student_id

select s.student_id,s.name,sc.score,
		rank() over(
order by sc.score desc)as ranking
from students s
join scores sc using(student_id)
where s.grade_code = 'S1' and sc.subject = '物理'
order by sc.score desc,student_id
limit 12

select s.student_id,s.name,sc.score,
		rank() over(
order by sc.score desc)as ranking
from students s
join scores sc using(student_id)
where s.grade_code = 'S1' and sc.subject = '物理'
order by sc.score desc,student_id
limit 10

select s.student_id,s.name,sc.score,
		rank() over(
order by sc.score desc)as ranking
from students s
join scores sc using(student_id)
where s.grade_code = 'S1' and sc.subject = '物理'
order by sc.score desc,student_id

SELECT st.student_id,st.name,sc.score,
			row_number()over(
order by sc.score desc
)as rnk
FROM students st
JOIN scores sc USING(student_id)
WHERE sc.subject = '物理' AND st.grade_code = 'S1'
order by sc.score desc,st.student_id 
limit 10

SELECT st.student_id,st.name,sc.score,
			row_number()over(
order by sc.score desc
)as rnk
FROM students st
JOIN scores sc USING(student_id)
WHERE sc.subject = '物理' AND st.grade_code = 'S1'
limit 10

SELECT st.student_id,st.name,sc.score,
			row_number()over(
order by sc.score desc
)as rnk
FROM students st
JOIN scores sc USING(student_id)
WHERE sc.subject = '物理' AND st.grade_code = 'S1'

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

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

select s.name,s.class_code,s.grade_code,
t.name as head_teacher_name
from students s
left join teachers t ons.class_code =t.head_teacher
 order by s.student_id

select s.name,s.class_code,s.grade_code,
t.name as head_teacher_name
from students s
join teachers t ons.class_code in(
		substring(t.class_code,1,4),
		substring(t.class_code,5,4),
substring(t.class_code,9,4)
)
 order by s.student_id