这道题一开始我只写出了FROM后子查询的内容,因为忽略了几个细节:
保留两位小数的百分数要注意round(A/B*100,2),用这个数筛选出大于60的,最后再查询,用CONCAT(…,'%')连接这个数和百分号
SELECT tag,concat(avg_play_progress,'%') avg_play_progress
FROM (SELECT tag,
round(AVG((CASE WHEN TIMESTAMPDIFF(second,start_time,end_time)>=duration
THEN 1
ELSE TIMESTAMPDIFF(second,start_time,end_time)/duration END))*100,2) avg_play_progress
FROM tb_user_video_log t1
LEFT JOIN tb_video_info t2 ON t1.video_id=t2.video_id
GROUP BY tag
HAVING avg_play_progress>60
ORDER BY avg_play_progress DESC) t;