牛客sql题笔记1--清扫知识盲区

flag：坚持不看题解和讨论区完成答题~

第一板块

SQL25 union all 用法

union：将2个查找结果拼接起来时，自带去重；

union all：将2个查找结果拼接起来，不去重；

SQL26 case when的数据可以用来group by

select case when age<25 then '25岁以下'

when age is null then '25岁以下'

when age>=25 then '25岁及以上'

end as 'age_cut' ,count(*) as number

from user_profile group by age_cut;

原来可以group by ‘case when的数据’~

SQL29 困难题记录（就硬写..[苦笑.jpg]）

计算用户的平均次日留存率_牛客题霸_牛客网

select count(distinct q1.device_id,q1.date)/count(distinct q3.device_id,q3.date)

from question_practice_detail q1 join question_practice_detail q2 join question_practice_detail q3

on q1.device_id=q2.device_id and datediff(q1.date,q2.date)=1;#复制3个表，前2表计算分子，表3计算分母

在解答和评论区看到大佬的解法，直呼大佬！

select avg(if(b.device_id is notnull,1,0)) as avg_ret from

(selectdistinctdevice_id,date from question_practice_detail) a left join

(select distinct device_id,date_sub(date,interval 1 day) as date from question_practice_detail) b

on a.device_id = b.device_id and a.date= b.date；

SQL30-32 substring_index函数

统计每种性别的人数_牛客题霸_牛客网

substring_index(被切割对象,切割识别字符,要提取的字符串位置)

如：

profile

substring_index(profile,',',-1) #提取切割profile中,分割开的倒数第1个字符(串) 输出：male

substring_index(profile,',',-2) #输出：27,male

substring_index(substring_index(profile,',',-2),',',1) #输出：27

补充：

substring(profile,2) #输出：0cm,75kg,27,male

substring(profile,-4,3) #输出：mal

left(profile,2) #输出：18

right(profile,3) #输出：ale

SQL34(hard题) 我的暴力解法和大佬的灵巧思路

统计复旦用户8月练题情况_牛客题霸_牛客网

#我的暴力解法

select u.device_id,university,sum(if(result is not null,1,0)),sum(if(result='right',1,0)) from user_profile u,question_practice_detail q where u.device_id=q.device_id and university='复旦大学'and month(date)=8 group by device_id

union

select u.device_id,university,0,0 from user_profile u where university='复旦大学'and device_id not in (select u.device_id from user_profile u,question_practice_detail q where u.device_id=q.device_id and university='复旦大学'and month(date)=8group by device_id) group by device_id；

#大佬灵巧解法

select u.device_id,university,count(q.question_id),sum(if(result='right',1,0))

from user_profile u left join question_practice_detail q on u.device_id=q.device_id

where university='复旦大学' and (month(date)=8 or month(date) is null) group by u.device_id;

至此，40到入门题（其中居然3道hard）就全部解决了。很开心，40道题都是自己写的，遇到hard或比较难的mediun会写完后再去评论区看大家怎么想，每次看完都会感叹“好厉害，原来还可以这样”~

接着开始第二部分刷题啦~

题目来源：牛客网