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

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

select trx_amt,count(trx_amt) as total_trx_cnt,count(distinct usr_id) as unique_usr_cnt,count(trx_amt)/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-06-30')
group by trx_amt
order by avg_trx_per_user desc
limit 5;

select CASE 
    WHEN enter_date >= '2010-01-01' THEN '青年教师'
    WHEN enter_date < '2000-01-01' THEN '资深教师'
    ELSE '中年教师'
    END AS '类型',
    COUNT(*) AS 'number'
FROM teachers
GROUP BY 类型

select
	case 
	when enter_date >= '2010-01-01' then '青年教师'
when enter_date < '2000-01-01' then '资深教师'
else '中年教师' end as type,
count(teacher_id) as number
from teachers
group by type

select case when gender = 'm' then '男' when gender = 'f' then '女' else 'none' endas '性别',count(student_id) '数量' from students
group by gender

select year(dt) as Y
    ,cast(avg(case when city='beijing' then tmp_h else null end) as decimal(4,2)) as '北京'
    ,cast(avg(case when city='shanghai' then tmp_h else null end) as decimal(4,2)) as 上海
    ,cast(avg(case when city='shenzhen' then tmp_h else null end) as decimal(4,2)) as 深圳
	,cast(avg(case when city='guangzhou' then tmp_h else null end) as decimal(4,2)) as 广州
from weather_rcd_china
where year(dt) between 2011 and 2022
group by year(dt)

select year(dt) as Y
    ,cast(avg(case when city='beijing' then tmp_h else null end) as decimal(4,2)) as '北京'
    ,cast(avg(case when city='shanghai' then tmp_h else null end) as decimal(4,2)) as 上海
 		 ,cast(avg(case when city='guangzhou' then tmp_h else null end) as decimal(4,2)) as 广州
    ,cast(avg(case when city='shenzhen' then tmp_h else null end) as decimal(4,2)) as 深圳
from weather_rcd_china
where year(dt) between 2011 and 2022
group by year(dt)

select year(dt) as Y
    ,cast(avg(case when city='beijing' then tmp_h else null end) as decimal(4,2)) as '北京'
    ,cast(avg(case when city='shanghai' then tmp_h else null end) as decimal(4,2)) as 上海
,cast(avg(case when city='guangzhou' then tmp_h else null end) as decimal(4,2)) as 广州
    ,cast(avg(case when city='shenzhen' then tmp_h else null end) as decimal(4,2)) as 深圳
from weather_rcd_china
where year(dt) between 2011 and 2022
group by year(dt)

select * from scores
where exam_date = '2024-06-30'
and ((subject = '历史' and score >= 90) or (subject = '地理' and score >= 90) or (subject = '政治' and score >= 90))
order by score desc ,student_id,subject

select * from scores
where exam_date = '2024-06-30' and subject = '英语' and score between 100 and 110
order by score desc

select * from students
where class_code in ('C219','C220','C221') and gender = 'f'
 order by student_id

select student_id,chinese,math,english from subject_score
where chinese >100 or math >100 or english >100 
 order by chinese asc

select student_id,sum(score) total_score from scores
where subject in ('语文', '数学', '英语')and exam_date = '2024-06-30'
group by student_id
having sum(score) > 330

select subject from teachers
group by subject
having count(teacher_id)>=3

select student_id,max(score),min(score),avg(score) from scores
where student_id in (460093,735011) and subject = '数学'
group by student_id

select max(score),min(score),avg(score) from scores
where subject = '语文' and student_id = '735011'

select subject,max(score),min(score),avg(score) from scores
where subject = '语文' and student_id = '735011'

select class_code,min(birth_date) from students
group by class_code
order by class_code asc

select class_code,max(birth_date) from students
group by class_code
order by class_code asc