-CDA考试官方模拟题库

备考刷题，请到

CDA认证小程序

某物流公司有一张运单表 delivery，字段：delivery_id（主键）、order_id、driver_id、start_time、end_time。现需要分析每位司机的平均运输时长（分钟），但发现部分记录的 end_time 为空（表示未完成）。业务要求只统计已完成运单（end_time IS NOT NULL）。以下SQL语句中，能正确计算并按平均时长降序排列的是？

A. SELECT driver_id, AVG(TIMESTAMPDIFF(MINUTE, start_time, end_time)) AS avg_minutes FROM delivery WHERE end_time IS NOT NULL GROUP BY driver_id ORDER BY avg_minutes DESC

B. SELECT driver_id, AVG(DATEDIFF(end_time, start_time)*24*60) AS avg_minutes FROM delivery WHERE end_time IS NOT NULL GROUP BY driver_id ORDER BY avg_minutes DESC

C. SELECT driver_id, AVG(end_time - start_time) AS avg_minutes FROM delivery WHERE end_time IS NOT NULL GROUP BY driver_id ORDER BY avg_minutes DESC

D. SELECT driver_id, AVG(TIMESTAMPDIFF(MINUTE, start_time, end_time)) FROM delivery WHERE end_time IS NOT NULL GROUP BY driver_id ORDER BY 2

上一题

下一题

题目解析

题目评论(0)

B错误，DATEDIFF 只返回天数，再乘1440得到分钟，但丢失了小时和分钟部分，不精确。C错误，直接对时间戳做减法在MySQL中返回的是秒数差值（若为datetime类型），但不同数据库差异大，且不直观。D缺少列别名， ORDER BY 2 依赖列顺序，且排序方向不正确。