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

select	year(dt) as Y,
		cast(avg(case when city = 'beijing' then tmp_h else null end) as decimal(10,2)) as 北京,
cast(avg(case when city = 'shanghai' then tmp_h else null end) as decimal(10,2)) as 上海,
cast(avg(case when city = 'shenzhen' then tmp_h else null end) as decimal(10,2)) as 深圳,
cast(avg(case when city = 'guangzhou' then tmp_h else null end) as decimal(10,2)) as 广州
from weather_rcd_china
where year(dt) between 2011 and 2022
group by year(dt)
order 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='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	city,
		sum(case when con like '%雪%' then 1 else 0 end) as snowy_days
from weather_rcd_china
where month(dt) in(12, 1, 2)
group by city
order by snowy_days desc;

select * from cmb_usr_trx_rcd
where usr_id = '5201314520' 
	and
date(trx_time) between '2024-09-01' and '2024-09-30'
and 
((hour(trx_time) >= 22) or (hour(trx_time) between 0 and 5))
order by trx_time;

select * from cmb_usr_trx_rcd
where usr_id = '5201314520' 
	and
date(trx_time) between '2024-09-01' and '2024-09-30'
and 
((hour(trx_time) > 22) or (hour(trx_time) between 0 and 5))
order by trx_time;

select * from cmb_usr_trx_rcd
where usr_id = '5201314520' 
	and
date(trx_time) between '2024-09-01' and '2024-09-30'
and 
(hour(trx_time) > 22 or hour(trx_time) between 0 and 5)
order by trx_time;

select * from cmb_usr_trx_rcd
where usr_id = '5201314520' 
	and
date(trx_time) between '2024-09-01' and '2024-09-30'
and 
(hour(trx_time > 22) or hour(trx_time) between 0 and 5)
order by trx_time;

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

select * from cmb_usr_trx_rcd
where	usr_id = '5201314520'
		and 
date(trx_time) between '2024-09-01' and '2024-09-30'
order by	trx_time;

select * from cmb_usr_trx_rcd
where	usr_id = '5201314520'
		and 
trx_time between '2024-09-01' and '2024-09-30'
order by	trx_time;

select cust_uid, mch_nm from mt_trx_rcd1
where cust_uid = 'MT10000'
group by mch_nm
order by mch_nm desc;

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

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

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

select subject from teachers
group by subject
having count(name)>=3 
order by subject;

select 	student_id, 
		max(score) as max_score, 
min(score) as min_score, 
avg(score) as avg_score 
from scores
group by	student_id
having		min(score)>=80
order by student_id;

select class_code, min(birth_date) as min_birth_date from students
group by class_code
order by class_code;

select subject, max(score) as max_score	, min(score) as min_score from scores
group by subject
order by subject;

select subject, avg(score) as average_score from scores
where exam_date = '2024-06-30'
group by	subject
order by	subject;