2025-03-31
城市平均最高气温 
select city,cast(avg(tmp_h) as decimal(4,2))
from weather_rcd_china
where year(dt) = '2021'
group by city
;
2025-03-31
城市平均最高气温 
select city,avg(tmp_h)
from weather_rcd_china
where year(dt) = '2021'
group by city
;
2025-03-31
按照车类统计行程次数 
select d.car_cls,count(*)
from didi_sht_rcd d
group by car_cls
order by count(*)
desc
2025-03-29
HAVING-执教教师超过3人的科目 
select subject
from teachers
group by subject having count(name)>=3
;
2025-03-29
HAVING-每次成绩都不低于80分的学生 
select student_id
from scores
where
score>=80
or score is null
order by student_id desc
;
2025-03-29
HAVING-每次成绩都不低于80分的学生 
select student_id
from scores
where
score>=80
or score is null
order by student_id
;
2025-03-29
HAVING-每次成绩都不低于80分的学生 
select student_id
from scores
where
score>=80
or score is null
;
2025-03-29
CASE WHEN-老中青教师数量 
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
;
2025-03-29
CASE WHEN-男女学生的数量 
SELECT
CASE
WHEN gender = 'm' THEN '男'
WHEN gender = 'f' THEN '女'
END AS gender_text,
COUNT(*) AS student_count
FROM students
GROUP BY gender_text;
2025-03-29
聚合函数-比较两位同学的数学成绩 
select student_id,max(score),min(score),avg(score) from scores
where
student_id in (460093,735011 )
and
subject='数学'
group by student_id
;
2025-03-29
聚合函数-735011学生的语文成绩 
select max(score) ,min(score),avg(score)
from scores
where
student_id=735011
and
subject='语文'
;
2025-03-29
GROUP BY-年龄最大学生的出生日期 
select class_code,min(birth_date)
from students
group by class_code
order by class_code
;
2025-03-29
GROUP BY-各科目最高分、最低分 
select subject,max(score),min(score) from scores
group by subject
order by subject
;
2025-03-29
GROUP BY-各科目平均分 
select subject,avg(score)
from scores
where date(exam_date)='2024-06-30'
group by subject
order by subject
;
2025-03-29
GROUP BY-各班级人数 
select class_code,count(*)
from students
group by class_code
order by count(*) desc
;
2025-03-27
冬季下雪天数 
selectcity,count(*)
from weather_rcd_china
where month(dt) in (1,2,12)
and
con like '%雪%'
group by city
order by count(*) desc
;
2025-03-27
冬季下雪天数 
selectcity,count(*)
from weather_rcd_china
where month(dt) in (1,2,12)
and
con like '%雪%'
group by city
;
2025-03-27
人数最多的学生姓氏 
select left(name,1) ,count(name) from students
group by
left(name,1)
order by count(name) desc
limit 5
;
2025-03-27
德州扑克起手牌- 手对 
select * from hand_permutations
where
left(card1,1)=left(card2,1)
;
2025-03-27
德州扑克起手牌- A花 
select * from hand_permutations
where
left(card1,2)=left(card2,2)
and
(card1 like 'A%'
or card2 like 'A%')
order by id
;