【Ryo】MySQL：刷题训练牛客篇（二）

数据库是进行数据分析的“源泉”，对冗杂的数据进行整理和编排是数据分析的首要任务。同时将来职业生活中，对数据库系统进行DB操作也会是重要任务之一，因此掌握数据库语言刷题刷起来，至少对语言运用和数据库管理有所了解是题中之义。
本篇为牛客题霸的SQL训练题目练习记录（二）。

1. 查找语法

题目描述：
查找字符串’10,A,B’ 中逗号’,'出现的次数cnt。

牛客解答：

#把串 "10,A,B" 中的逗号用空串替代， 变成了 "10AB"，然后原来串的长度 - 替换之后的串的长度，就是被替换的逗号的个数。
select (length("10,A,B") - length(replace("10,A,B",",","")) )
as cnt;

注：本题后台OJ存在bug，可以直接输入2。。

2. 位置函数

题目描述：
获取Employees中的first_name，查询按照first_name最后两个字母，按照升序进行排列

CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` char(1) NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`));

个人解答：

SELECT first_name 
FROM employees 
ORDER BY RIGHT(first_name,2);

3. 聚合函数group_concat

题目描述：
按照dept_no进行汇总，属于同一个部门的emp_no按照逗号进行连接，结果给出dept_no以及连接出的结果employees

CREATE TABLE `dept_emp` (
`emp_no` int(11) NOT NULL,
`dept_no` char(4) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));

牛客解答：

#聚合函数group_concat（X，Y），其中X是要连接的字段，Y是连接时用的符号，可省略，默认为逗号
SELECT dept_no, group_concat(emp_no)AS employees
FROM dept_emp
GROUP BY dept_no;

4. 分页查询

题目描述：
分页查询employees表，每5行一页，返回第2页的数据

CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` char(1) NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`));

个人解答：

select * from employees
order by emp_no
limit 5,5;

大佬的注释：limit offset, size：size是每页几条数据，分页时offset输出页数（page-1）。
size如本题中为第二页数据，则offset为（2-1）*5 。

即可以记为，若每页显示n条记录，要显示第i页数据，则可用 limit n*(i-1),n  

5. 聚合函数

题目描述：

个人解答：

select job, round(avg(score), 3) as avg
from grade
group by job
order by avg
desc

6.经典自关联 或 窗口函数

题目描述：

牛客解答1：

select a.id, a.number, (select count(distinct b.number) 
                        from passing_number as b 
                        where b.number>=a.number )
from passing_number as a 
order by a.number desc, a.id asc;

大佬的去重方法：要得到通过题目的排名，比如你通过了8分,，你同学a也通过了8分，找到大于等于你的成绩，一个9分，一个8分，一个8分，去重复，就一个9，一个8，count一下总数，第2名，如果有三个同学通过了7个呢，同理，9，8，8，7，7，7 后面比这个少的，已经死在了筛选条件，去重，9，8，7，count=3。

牛客解答2：本题用窗口函数dense_rank()是最方便的

SELECT *, dense_rank() over ( ORDER BY number DESC ) as `rank` 
FROM passing_number 
ORDER BY `rank`, id
#dense_rank函数在生成序号时是连续的