力扣sql中等篇练习(十)
1 查询回答率最高的问题
1.1 题目内容
1.1.1 基本题目信息
1.1.2 示例输入输出
1.2 示例sql语句
SELECT t1.question_id survey_log
FROM
(SELECT question_id,count(answer_id)/count(*) rFROM SurveyLogGROUP BY question_idORDER BY r desc,question_id asclimit 0,1
)t1
1.3 运行截图
2 统计各专业学生人数
2.1 题目内容
2.1.1 基本题目信息1
2.1.2 基本题目信息2
2.1.3 示例输入输出
2.2 示例sql语句
SELECT d.dept_name,IFNULL(t1.num,0) student_number
FROM Department d
LEFT JOIN
(SELECT dept_id,count(*) numFROM StudentGROUP BY dept_id
)t1
ON d.dept_id=t1.dept_id
ORDER BY student_number desc,dept_name asc
2.3 运行截图
3 2016年的投资
3.1 题目内容
3.1.1 基本题目信息
3.1.2 示例输入输出
3.2 示例sql语句
SELECT ROUND(SUM(tiv_2016),2) tiv_2016
FROM insurance
WHERE TIV_2015 in
(SELECT TIV_2015FROM insuranceGROUP BY TIV_2015HAVING count(*)>1
)
AND concat(LAT,LON) in
(SELECT concat(LAT,LON)FROM insuranceGROUP BY concat(LAT,LON)HAVING count(*)=1
)
3.3 运行截图