2025-07-03
用户"kjhd30"的第一笔未完成订单
select *
from didi_order_rcd
where cust_uid = 'kjhd30' and year(finish_time) = 1970
order by order_id
limit 1;
2025-07-03
滴滴面试真题（2）打车订单呼叫应答时间
select sum(timestampdiff(second,call_time,grab_time)) / count(1) asavg_response_time_seconds
from didi_order_rcd
where year(grab_time)!= 1970;
2025-07-03
滴滴面试真题（2）打车订单呼叫应答时间
select sum(timestampdiff(second,call_time,grab_time)) / count(1) asavg_response_time_seconds
from didi_order_rcd
where year(finish_time)!= 1970;
2025-05-23
给英语成绩中上水平的学生拔尖
select student_id,subject,score,exam_date
from scores
where exam_date = '2024-06-30' and subject = '英语'
having score between 100 and 110
order by score desc;
2025-05-23
给英语成绩中上水平的学生拔尖
select student_id,score
from scores
where exam_date = '2024-06-30' and subject = '英语'
having score between 100 and 110
order by score desc;
2025-05-23
找出三个班级的女生
select *
from students
where class_code in ('C219','C220','C221') AND gender = 'f'
order by student_id;
2025-05-23
语文数学英语至少1门超过100分的同学
select student_id,chinese,math,english
from subject_score
where chinese > 100 or math >100 or english > 100
order by chinese;
2025-05-23
小结-行转列，展开学生成绩（1）
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;
2025-05-23
HAVING-语数英优异的学生
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;
2025-05-23
HAVING-执教教师超过3人的科目
select subject
from teachers
group by subject
having count(subject) >= 3;
2025-05-23
HAVING-每次成绩都不低于80分的学生
select student_id,max(score),min(score),avg(score)
from scores
group by student_id
having min(score) >= 80
order by student_id;
2025-05-23
聚合函数-比较两位同学的数学成绩
select student_id,max(score),min(score),avg(score)
from scores
where student_id in (460093 , 735011) and subject ='数学'
group by student_id;
2025-05-23
聚合函数-735011学生的语文成绩
select max(score),min(score),avg(score)
from scores
where student_id = 735011 and subject = '语文';
2025-05-23
GROUP BY-年龄最大学生的出生日期
select class_code,min(birth_date)
from students
group by class_code
order by class_code;
2025-05-23
GROUP BY-年龄最大学生的出生日期
select class_code,max(birth_date)
from students
group by class_code
order by class_code;
2025-05-23
GROUP BY-年龄最大学生的出生日期
select grade_code,max(birth_date)
from students
group by grade_code
order by grade_code;
2025-05-23
GROUP BY-各科目最高分、最低分
select subject,max(score),min(score)
from scores
group by subject
order by subject;
2025-05-23
GROUP BY-各科目平均分
select subject,avg(score)
from scores
where exam_date = '2024-06-30'
group by subject
order by subject;
2025-05-23
GROUP BY-各班级人数
select class_code,count(*)
from students
group by class_code;
2025-05-23
条件过滤-没有职称的老教师
select name,subject,class_code,enter_date
from teachers
where year(enter_date) < 2010 and qualification is null
order by enter_date;