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

select si.singer_id, si.singer_name, ai.album_id, ai.album_name, count(lr.id) as listen_cnt from singer_info si 
join album_info ai on si.singer_id = ai.singer_id
left join song_info sinfo on si.singer_id = sinfo.origin_singer_id
left join listen_rcd lr on sinfo.song_id = lr.song_id
group by si.singer_id, ai.album_id, sinfo.song_id, lr.song_id
having listen_cnt = 0

select si.singer_name, ai.album_name, count(lr.id) as listen_cnt from singer_info si 
join album_info ai on si.singer_id = ai.singer_id
left join song_info sinfo on si.singer_id = sinfo.origin_singer_id
left join listen_rcd lr on sinfo.song_id = lr.song_id
group by si.singer_id, ai.album_id, sinfo.song_id, lr.song_id
having listen_cnt = 0

select user_id, dayname(start_time) as day_of_week, count(*) as listen_per_day from listen_rcd
 group by user_id, day_of_week
 order by user_id, day_of_week asc

select tpar.prd_id, tpm.prd_nm, count(tpar.cust_uid) as cnt_cu from tb_pg_act_rcd tpar left join tb_prd_map tpm on tpar.prd_id = tpm.prd_id
where tpar.if_snd = 1
group by tpar.prd_id, tpm.prd_nm
order by cnt_cu desc 
limit 1;

select tpar.prd_id, tpm.prd_nm, count(tpar.cust_uid) as cnt_cu from tb_pg_act_rcd tpar left join tb_prd_map tpm on tpar.prd_id = tpm.prd_id
where tpar.if_snd = 1
group by tpar.prd_id, tpm.prd_nm
order by cnt_cu desc

select dsr.cust_uid, dsr.start_loc, dsr.end_loc, dsr.start_tm, dsr.car_cls from didi_sht_rcd dsr left join loc_nm_ctg lnc on dsr.end_loc = lnc.loc_nm
where loc_ctg = '餐饮'
order by dsr.start_tm asc

select t1.live_id, t2.live_nm, count(t1.enter_time) as enter_cnt from ks_live_t1 t1 left join ks_live_t2 t2 on t1.live_id = t2.live_id
 where date(t1.enter_time) = '2021-09-12'
 and hour(t1.enter_time) = 23
 group by t1.live_id, t2.live_nm
 order by enter_cnt desc
 limit 5;

select t1.live_id, t2.live_nm, count(t1.enter_time) as enter_cnt from ks_live_t1 t1 left join ks_live_t2 t2 on t1.live_id = t2.live_id
 where date(t1.enter_time) = '2021-09-12'
 and hour(t1.enter_time) = 23
 group by t1.live_id, t2.live_nm
 order by enter_cnt desc

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

select * from scores
where(
(subject = '历史' and score >= 90)
or (subject = '地理' and score >= 90)
or (subject = '政治' and score >= 90))
and date(exam_date) = '2024-09-30'
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 * from students
where class_code in ('C219', 'C220', 'C221')
and gender - 'f'
order by student_id

select * from subject_score
where(
chinese > 100
or math >100
or english > 100)
order by chinese

select exam_date,
max(case when subject = '语文' then score else null end)as chinese_score,
max(case when subject = '数学' then score else null end)as math_score,
max(case when subject = '英语' then score else null end)as english_score
from scores
where student_id = '460093' and subject in ('语文','数学','英语')
group by exam_date
order by exam_date

select sc.student_id, sum(sc.score) as total_score from students s left join scores sc on s.student_id = sc.student_id
where date(sc.exam_date) = '2024-06-30'
and sc.subject in ('语文', '数学', '英语')
group by sc.student_id
having sum(sc.score) > 330
order by sc.student_id asc

select s.name, sum(sc.score) as total_score from students s left join scores sc on s.student_id = sc.student_id
where date(sc.exam_date) = '2024-06-30'
and sc.subject in ('语文', '数学', '英语')
group by sc.student_id
having sum(sc.score) > 330
order by total_score desc

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

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