select city,cast(avg(tmp_h) as decimal(4,2))
from weather_rcd_china 
where year(dt) = '2021'
group by city
;

select city,avg(tmp_h)
from weather_rcd_china 
where year(dt) = '2021'
group by city
;

select d.car_cls,count(*)
from didi_sht_rcd d
group by car_cls
order by count(*)
 desc

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

select student_id
from scores
where 
score>=80
or score is null
order by student_id desc
;

select student_id
from scores
where 
score>=80
or score is null
order by student_id
;

select student_id
from scores
where 
score>=80
or score is null
;

select 
case when year(enter_date) >= '2010' then '青年教师'
whenyear(enter_date) < '2000' then '资深教师'
else '中年教师'
end as teacher_type,
count(*)
from teachers
group by teacher_type
;

SELECT
    CASE 
        WHEN gender = 'm' THEN '男'
        WHEN gender = 'f' THEN '女'
    END AS gender_text,
    COUNT(*) AS student_count
FROM students
GROUP BY gender_text;

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
student_id=735011
and 
subject='语文'
;

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

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

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

select class_code,count(*) 
from students 
group by class_code
order by count(*) desc
;

selectcity,count(*)
from weather_rcd_china 
where month(dt) in (1,2,12)
and 
con like '%雪%'
group by city 
order by count(*) desc
;

selectcity,count(*)
from weather_rcd_china 
where month(dt) in (1,2,12)
and 
con like '%雪%'
group by city 
;

select left(name,1) ,count(name) from students
group by
left(name,1)
order by count(name) desc
limit 5
;

select * from hand_permutations 
where
left(card1,1)=left(card2,1)
;

select * from hand_permutations 
where
left(card1,2)=left(card2,2)
and
(card1 like 'A%'
or card2 like 'A%')
order by id
;