select r.prd_id,m.prd_nm,sum(case when if_snd='1' then 1 else 0 end) as exposure_count
from tb_pg_act_rcd r join tb_prd_map m 
on r.prd_id=m.prd_id
group by m.prd_id,m.prd_nm
order by exposure_count desc
limit 1

select d.* 
from didi_sht_rcd as d 
join loc_nm_ctg as l
on d.end_loc=l.loc_nm
where l.loc_ctg='餐饮'
order by d.start_tm asc

select d.* 
from didi_sht_rcd d 
join loc_nm_ctg l
on d.end_loc=l.loc_nm
where l.loc_nm='餐饮'
order by d.start_tm asc

select t2.live_id,t2.live_nm,count(*) as enter_cnt
from ks_live_t1 as t1 join ks_live_t2 as t2 
on t1.live_id=t2.live_id
where date_format(t1.enter_time, '%Y-%m-%d %H')='2021-09-12 23'
group by t1.live_id,t2.live_nm
order by enter_cnt desc
limit 5

select t2.live_id,t2.live_nm,count(*) as enter_cnt
from ks_live_t1 as t1 join ks_live_t2 as t2 
on t1.live_id=t2.live_id
where date_format(t1.enter_time, '%Y-%M-%D %H')='2021-9-12 23'
group by t1.live_id,t2.live_nm
order by enter_cnt desc
limit 5

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 * from scores 
where exam_date='2024_06_30' and subject in('历史','政治','地理') and score>=90 
order by score desc ,student_id,subject

select * from scores 
where exam_date='2024_06_30' and subject in('历史','政治','地理') and (select max(score)>=90 from scores)
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 
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 
group by exam_date
order by exam_date

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) 总分 from scores
where subject in('语文','数学','英语') and exam_date='2024-6-30'
group by student_id
having 总分>330

select student_id, sum(score) 总分 from scores
where subject in('语文','数学','英语')
group by student_id
having 总分>330

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

select subject, count(*) as teacher_count from teachers
group by subject having teacher_count>=3

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

select student_id, min(score) 
from scores
group by student_id
having min(score)>=80 
order by student_id

select 
case when left(enter_date,4)>=2010 then '青年教师'
when left(enter_date,4)< 2000 then '资深教师'
else '中年教师' 
END AS teacher_type,
    COUNT(*) AS teacher_count
FROM teachers
GROUP BY teacher_type;

select 
case when gender='f' then '女'
when gender='m'then '男'
end as gender_text,
count(*) as student_count
from students
group by gender