sql业务场景分析思路参考

1、时间可以进行排序，也可以用聚合函数对时间求最大值max（时间）

例如下面的例子：取最晚入职的人，那就是将入职时间倒序排序，然后limit 1

表：

 场景：查找最晚入职员工的所有信息

select * from employees order by hire_date desc limit 1

方法二：子查询 

select * from employees where hire_date=(select max(hire_date) from employees)

2、 limit可以单独两个参数使用，也可以和offset一起使用

例如：

表：

业务需求： 

代码：limit两个参数单独使用：倒序排序，从第三行开始显示，显示一行数据

select * from employees order by hire_date desc limit 2,1

或者：结合offset使用：

select * from employees order by hire_date desc limit 1 offset 2

3、内连接、左连接、右连接的连接后表的合并情况：

（1）两表内连接的结果是将后面的表拼接在前面的表后面

看例子：两个表：

内连接代码：

select *
from salaries sa join dept_manager  de on sa.emp_no=de.emp_no

结果： 

 （2）右连接

两个表：

 上面的表右连接下面的表，代码如下：

select *
from employees e right join dept_emp d on e.emp_no=d.emp_no

结果：

虽然是右连接，但是上面的表在right join的前面，前面的表右连接后面的表，那前面的表就在最后的结果中在前面展示，后面的表在后面展示，但是右连接是保留后面的表的所有行

4、新型思路：找第二大的数据，先把第一大的数据筛选掉，然后再在表格中找第一大也就是原本表格的第二大的数据了

表：

需求： 

代码：

select e.emp_no,s.salary,e.last_name,e.first_name
from employees e join salaries s on e.emp_no=s.emp_no
where s.salary=(select max(s2.salary)from salaries s2where s2.salary<(select max(salary) from salaries) 
)

 题解：

结果：

5、两个表连接，输出在职员工自入职以来的薪水涨幅情况

业务需求：

表：

思路：

用两次join，连接三个表，先连接前两个表，后连接第三个表，然后用当前工资减去入职工资。

结果：

6、MySql在处理group by和非聚合列的关系上有变动：一定的版本或者关系允许，使用group by时有select后面有非聚合列的出现：

官方解释：

官网链接：MySQL :: MySQL 5.7 Reference Manual :: 12.19.3 MySQL Handling of GROUP BY 

例子：

表：

业务需求：

代码：

select de.dept_no,dep.dept_name,count(de.dept_no)
from dept_emp de 
right join departments dep on de.dept_no=dep.dept_no
join salaries s on de.emp_no=s.emp_no
group by de.dept_no
order by de.dept_no

结果：

解释：

7、字段按照窗口函数倒序排序，窗口内的本字段值相同的按照另外一个字段升序排序

也就是用dense_rank()排序，出现1223的情况，那22这两个字段值是相等的，就按照另外一个字段排序，就是dense_rank只能保证整体按照一个字段排序，但是相同的它不能管是不是按照另外一个字段升序排序，所以要用到order by 进行整体升序排序，然后按另外一个字段升序排序

例如下面这道题：

业务需求：

表：

代码： 

select emp_no,salary,dense_rank()over(order by salary desc) r
from salaries
order by r,emp_no asc

 红色框里面是按照工资进行降序排序，但是只能进行工资降序排序，他管不到相同序号里面是否是按照员工编号升序排序，所以用绿色框里order by进行排序，因为order by在窗口函数后执行，直接写order by emp_no asc按照编号排序的话，整个排序就会乱掉，不会按照薪资降序排序了，所以要先按照order by r asc升序排序，保证整个表是先按照薪资降序排序的，然后按照编号升序排序。

结果：

8、group by可以根据两个字段进行分组，是去除重复的，而窗口函数不去重复，是计算累积值：

例子：

业务需求：

原表：

 

 代码：

select dpm.dept_no,dpm.dept_name,t.title,count(t.title)
from departments dpm inner join
dept_emp de on dpm.dept_no=de.dept_no inner join
titles t on t.emp_no=de.emp_no
group by dept_no,title
order by dept_no,title asc

结果：