牛客SQL初级总结

牛客SQL初级总结：

1、distinct ：单列去重

2、limit 偏移量,显示个数：偏移量可不写

3、n between a and b : a <= n <= b

4、in(条件1，条件2...) : 有限个多选

5、like ：匹配

6、max\count\avg\sum

7、 group by ... having ... ：

1）是否需要对多个条件进行分组。
2）对分组进行条件筛选。

8、 order by （注意， order by 执行顺序在 select 之后）

9、 join ... on ... : 多表联查

1）若题目明显要求联查，请先全部 join ，然后 select * 查看join后的表结构，并以此为基础进行下一步思考。
2）思考是否有必要使用 left join \ right join，以及使用后是否存在存在 '拼接字段在某表中不存在' 的情况。

10、 union all ：不去重拼接。（ union 去重拼接）

11、 case when 判断条件a then 若a正确 else 若a错误 end

12、 case when 判断条件a then 若a正确 when 判断条件b then 若b正确 ... end

13、 day\month\year 14、 substr(str,起始位置,截取长度) ：

1）若不写截取长度，默认撸到头。
2）若起始位置为负数，则是从右往左进行截取。

15、 substr_index(str,分隔符,位置) ：位置可选择正负，即第几个字符串

16、排名问题：

1）不一定非要使用开窗函数，可以先思考 order by + limit 的方式解决。
2）rank、dense_rank、row_number，思考题目具体要求。
3）over()内部 partition by 、 order by 写什么，怎么写。
4）包含开窗函数的子查询， select 要选哪些列。

17、OJ是辣鸡，有些报错纯属系统抽风，若本地可通过，不要浪费时间在硬肛OJ的报错上。

总结完毕

然后是最后两道题，存在些许问题未解决，暂存。

SQL34 统计复旦用户8月练题情况


SELECT
b.device_id,
a.university,
sum(if(result is not null,1,0)),
sum(if(result='right',1,0))
from(
select
device_id,
university
FROM
user_profile
WHERE
university='复旦大学'
) a
/*  
a 选择结果:
3214|复旦大学
4321|复旦大学
*/
left JOIN question_practice_detail b
on a.device_id = b.device_id
/*
left join 结果
3214|复旦大学|15|3214|113|wrong|2021-08-18
3214|复旦大学|14|3214|112|wrong|2021-08-16
3214|复旦大学|9|3214|113|wrong|2021-08-15
3214|复旦大学|8|3214|112|wrong|2021-05-09
3214|复旦大学|3|3214|113|wrong|2021-06-15
3214|复旦大学|2|3214|112|wrong|2021-05-09
4321|复旦大学|None|None|None|None|None
*/
where month(b.date)=8 or b.date is null
/*
where后 结果
3214|复旦大学
3214|复旦大学
3214|复旦大学
None|复旦大学 -----> ？？？？？
*/
group by b.device_id


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

反思：
1、没有考虑到拼接时存在 '拼接字段在某表中不存在' 的情况。
2、筛选条件顺序错误，究竟该如何筛选才能做到满足需求。
3、group by 与 select 报错的问题，依然没有找到问题核心。
	（ group by 必须包含所有 select 的字段？）

SQL35 浙大不同难度题目的正确率


SELECT
difficult_level,
sum(c.rc)/SUM(c.count_qid) rate
from(
select
b.question_id,
a.university,
a.device_id,
count(b.result) count_qid,
sum( case b.result when 'right' then 1 else 0 end ) rc
from
user_profile a
left join question_practice_detail b on a.device_id = b.device_id
where a.university = '浙江大学'
GROUP BY b.question_id
) c
/*
111	浙江大学	1236	2	2
113	浙江大学	1235	1	0
115	浙江大学	1236	3	3
116	浙江大学	1237	3	3
117	浙江大学	1237	3	0
*/
join question_detail d on d.question_id = c.question_id

/*
111	浙江大学	1236	2	2	1	111	hard
113	浙江大学	1235	1	0	3	113	easy
115	浙江大学	1236	3	3	4	115	easy
116	浙江大学	1237	3	3	5	116	medium
117	浙江大学	1237	3	0	6	117	easy
*/
group by difficult_level
order by rate
以上本地可运行，结果无误，但OJ报错：
SQL_ERROR_INFO: "Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'a.device_id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by"

题解：
SELECT   
    difficult_level,
    SUM(IF(result = "wrong", 0, 1)) / count(*) AS correct_rate
FROM
    question_practice_detail q
    LEFT JOIN user_profile u ON q.device_id = u.device_id
    LEFT JOIN question_detail q2 ON q.question_id = q2.question_id
WHERE
    university = "浙江大学"
GROUP BY
    difficult_level
ORDER BY
    correct_rate;

反思：
1、未能想到多次使用 left join。
2、select的列自己在瞎写，完全没有必要自己选择列
3、OJ是辣鸡，有些报错纯属系统抽风，若本地可通过，不要浪费时间在硬肛OJ的报错上

***********************************************************************