SQL练习笔记（一）

　　首先附上一个SQL语句执行顺序的参考：http://t.csdn.cn/Omlhn，感觉了解了之后在写语句的时候，会对整个结构更加有条理地理解。

　　题目来源牛客网SQL篇练习

　　1、题目：现在运营想要取出用户信息表中对应的数据，并先按照gpa、年龄降序排序输出，请取出相应数据。

SELECT device_id,gpa,age
FROM user_profile
ORDER BY gpa DESC,age DESC

　　笔记：ORDER BY 顺序可以分别指定不同的属性

　　2、题目：现在运营想要对每个学校不同性别的用户活跃情况和发帖数量进行分析，请分别计算出每个学校每种性别的用户数、30天内平均活跃天数和平均发帖数量。

SELECT gender,university,COUNT(device_id) user_num,AVG(active_days_within_30) avg_active_day,AVG(question_cnt) avg_question_cnt
FROM user_profile
GROUP BY university,gender

　　笔记：GROUP BY后出现的属性，SELECT语句中必须出现，除了聚合函数，HAVING语句同样也只能出现GROUP BY中的属性。

　　3、题目：现在运营想要分别查看学校为山东大学或者性别为男性的用户的device_id、gender、age和gpa数据，请取出相应结果，结果不去重。

SELECT device_id,gender,age,gpa
FROM user_profile
WHERE university='山东大学'
UNION ALL
SELECT device_id,gender,age,gpa
FROM user_profile
WHERE gender='male'

　　笔记：要是想要的两个部分的数据结果有重合，那么为避免去重要使用UNION ALL而非UNION，UNION是会自动去重的。

　　4、题目：现在运营想要将用户划分为25岁以下和25岁及以上两个年龄段，分别查看这两个年龄段用户数量。本题注意：age为null 也记为 25岁以下

SELECT age_cut,COUNT(device_id) number
FROM (SELECT IF(age>=25,'25岁及以上','25岁以下') age_cut,device_id
      FROM user_profile) user_profile1
GROUP BY age_cut

　　多年龄段分布可用CASE语句来返回：

SELECT device_id,gender,(CASE  
        WHEN age>=25 THEN '25岁及以上'
        WHEN age BETWEEN 20 AND 24 THEN '20-24岁'
        WHEN age<=20 THEN '20岁以下'
        ELSE '其他'
        END) age_cut
FROM user_profile

　　笔记：IF语句的使用，（条件，真返回，假返回），构建一个虚表进行所需数据操作。问题用>=25和else巧妙避开了null也记为25岁以下的判断。

　　5、题目：现在运营想要查看用户在某天刷题后第二天还会再来刷题的平均概率。请你取出相应数据。

SELECT AVG(IF(a2.device_id IS NOT NULL,1,0))
FROM (SELECT DISTINCT device_id,date
      FROM question_practice_detail) a1
      LEFT JOIN (SELECT DISTINCT device_id,date_sub(date,interval 1 day) date
      FROM question_practice_detail) a2
      ON a1.device_id=a2.device_id AND a1.date=a2.date

　　笔记：思路是创建一个虚表a2，其他属性都和a1一样，除了date属性每一行都减了1天。这样再进行左外连接，如果a1里面的日期和用户属性能在a2里面找到，其实就说明了该用户连续来了至少两天，找不到就会返回NULL。因此，只需要找到那些a2表中NOT NULL的用户即可。

　　6、题目：现在运营举办了一场比赛，收到了一些参赛申请，表数据记录形式如下所示，现在运营想要统计每个性别的用户分别有多少参赛者，请取出相应结果

SELECT SUBSTRING_INDEX(profile,',',-1) gender,COUNT(device_id) number
FROM user_submit
GROUP BY gender

　　笔记：字符串函数SUBSTRING_INDEX，只需要切中间部分的话，可以嵌套切两次。

　　7、题目：现在运营想要找到每个学校gpa最低的同学来做调研，请你取出每个学校的最低gpa

SELECT up.device_id,up1.university,up1.gpa
FROM (SELECT university,MIN(gpa) gpa FROM user_profile GROUP BY university) up1,user_profile up
WHERE up.gpa=up1.gpa AND up.university=up1.university
ORDER BY university

　　笔记：思路是因为SELECT语句用上了聚合函数和属性，得用上GROUP BY才能运行，于是构建了一个虚表up1，保存每个学校的最低gpa。再就是想在原表中找到和这个gpa以及学校相对应的学生，这个条件写在了WHERE语句中。

　　8、题目：现在运营想要了解复旦大学的每个用户在8月份练习的总题目数和回答正确的题目数情况，请取出相应明细数据，对于在8月份没有练习过的用户，答题数结果返回0

SELECT user_profile.device_id,university,COUNT(question_id) question_cnt,SUM(IF(result='right',1,0)) right_question_cnt
FROM user_profile,question_practice_detail
WHERE user_profile.device_id=question_practice_detail.device_id AND MONTH(date)=8 AND university='复旦大学'
GROUP BY device_id,university
UNION
SELECT user_profile.device_id,university,'0' question_cnt,'0' right_question_cnt
FROM user_profile,question_practice_detail
WHERE university='复旦大学' AND user_profile.device_id NOT IN (SELECT device_id FROM question_practice_detail WHERE MONTH(date)=8)