mysql牛客网网刷题函数总结57--CSDN博客

本文链接：https://blog.csdn.net/demon_xi/article/details/119390470

SQL57 使用含有关键字exists查找未分配具体部门的员工的所有信息。

用exists：
select * from employees e
where not exists
(select emp_no from dept_emp d where d.emp_no = e.emp_no);
不用exists:
select * from employees e
left join dept_emp d on d.emp_no = e.emp_no
where d.emp_no is null;

SQL59 获取有奖金的员工相关信息。

方法）CASE WHEN 求出bonus
给出emp_no、first_name、last_name、奖金类型btype、对应的当前薪水情况salary以及奖金金额bonus
bonus类型btype为1其奖金为薪水salary的10%，btype为2其奖金为薪水的20%，其他类型均为薪水的30%
当前薪水表示to_date='9999-01-01'

SELECT e.emp_no, e.first_name, e.last_name, eb.btype, s.salary,
CASE WHEN eb.btype = 1 THEN s.salary * 0.1
WHEN eb.btype = 2 THEN s.salary * 0.2
ELSE s.salary * 0.3
END bonus
FROM emp_bonus AS eb, employees AS e, salaries AS s
WHERE eb.emp_no = e.emp_no
AND s.emp_no = e.emp_no
AND s.to_date='9999-01-01';

SQL 59) -CASE WHEN 给出员工及其奖金信息

（方法）CASE WHEN 求出bonus
给出emp_no、first_name、last_name、奖金类型btype、对应的当前薪水情况salary以及奖金金额bonus
bonus类型btype为1其奖金为薪水salary的10%，btype为2其奖金为薪水的20%，其他类型均为薪水的30%
当前薪水表示to_date=‘9999-01-01’

SELECT e.emp_no, e.first_name, e.last_name, eb.btype, s.salary,
CASE WHEN eb.btype = 1 THEN s.salary * 0.1
WHEN eb.btype = 2 THEN s.salary * 0.2
ELSE s.salary * 0.3
END bonus
FROM emp_bonus AS eb, employees AS e, salaries AS s
WHERE eb.emp_no = e.emp_no
AND s.emp_no = e.emp_no
AND s.to_date='9999-01-01';

SQL60 统计salary的累计和running_total

难点running_total为前N个当前( to_date = ‘9999-01-01’)员工的salary累计

select emp_no,salary, sum(salary) over(order by emp_no) as running_total 
from salaries 
where to_date = '9999-01-01'

sum(salary) over(order by emp_no) 能计算前N个员工的累计

其实这里主要的理解清楚 e1.first_name>=e2.first_name，同样都是字符串怎么比较呢？
比如 select ‘a’ > ‘b’ 。可能会看不出来谁大谁小，怎么比较的呢？其实字符串内部是通过转换成 ascii 编码来进行大小比较的。
如我们查询 select ascii(‘a’) , asc(‘b’) 会得到 65 与 66。所以 select ‘a’ > ‘b’ 返回 0 。
那么再回到原题 e1.first_name>=e2.first_name

直接说可能不会懂放两段代码
第一段： select e2.first_name,( select count(*) from employees as e1 where e1.first_name <= e2.first_name ) as rownum
from employees as e2
第二段： select e1.first_name,ascii(e1.first_name),row_number() over(order by first_name)
from employees e1 ;（mysql8 已经内置了窗体函数。）

SQL61 对于employees表中，给出奇数行的first_name

select e1.first_name from employees e1 
where ( select count(*) from employees e2 WHERE
      e1.first.name >=e2first.name)%2=1

对于employees表中，输出first_name排名(按first_name升序排序)为奇数的first_name

row_number(): 同薪不同名，相当于行号，例如3000、2000、2000、1000排名后为1、2、3、4
rank(): 同薪同名，有跳级，例如3000、2000、2000、1000排名后为1、2、2、4
dense_rank(): 同薪同名，无跳级，例如3000、2000、2000、1000排名后为1、2、2、3
ntile(): 分桶排名，即首先按桶的个数分出第一二三桶，然后各桶内从1排名，实际不是很常用
partition by,按某字段切分
order by，与常规order by用法一致，也区分ASC(默认)和DESC，因为排名总得有个依据

SQL62 出现三次以上相同积分的情况

方法一：要三次以上的积分，那么肯定要查找3个id不同但是积分相同的情况，怎么比较一列和另外一列是否相等呢?在一个表里面感觉无法做到，那么最简单就是利用笛卡尔积了，1个表看成3个表，联立三个表number相同的部分
，笛卡尔积如下:

第1个表		第2个表		第3个表
id	number	id	number	id	number
1	111	1	111	1	111
2	333	2	333	2	333
3	111	3	111	3	111
4	111	4	111	4	111
5	333	5	333	5	333

那么可能就是举例一种情况就是寻找第1个表id为1的111，寻找第2个表id为3的111，寻找第3个表id4为的111，那么就找到一个111，输出111
但是上面这种找法可能会有重复的，比如第1个表id为3的111，寻找第2个表id为1的111，寻找第3个表id4为的111，那么又找到一个111，所以要去重。代码如下:

select distinct g1.number as times 
from 
	grade g1,
	grade g2,
	grade g3
where 
	g1.id != g2.id
	and g2.id != g3.id
	and g1.id != g3.id
	and g1.number = g2.number 
	and g2.number = g3.number ;

方法二：
使用group by 将积分分组，然后用having找到积分的个数大于等于3的:

select number from grade group by number having count(*)>=3

==count 专栏 ==

比较count(*)和count(字段名)的区别：前者对行的数目进行计算，包含null，后者对特定的列的值具有的行数进行计算，不包含null，得到的结果将是除去值为null和重复数据后的结果。
count（1）跟count（主键）一样，只扫描主键
count（*）和count（主键）使用方式一样，但是在性能上有略微的区别，mysql对前者做了优化。
count（主键）不一定比count（其余索引快）。
count（字段），该字段非主键的情况最好不要出现，因为该方式不走索引。

QL63 刷题通过的题目排名

方法一：dense_rank()函数

row_number()对应唯一排序：1、2、3、4
dense_rank()对应相同次序可重复，但不跳过下一个次序值：1、2、2、3
rank()对应相同次序可重复，并且跳过下一个次序值：1、2、2、4

select id, number,
  dense_rank() over (order by number desc) t_rank
from passing_number

方法二：使用where子查询
注意：筛选条件需要使用distinct去重才能得到真正的dense_rank排名，如果没有去重，得到的是rank的排名。
select p1.id ,p1.number,
(select count(distinct p2.number) from passing_number as p2 where p2.number >= p1.number) as t_rank
from passing_number as p1
order by number desc,id asc

SQL64 找到每个人的任务

找到每个人的任务情况，并且输出出来，没有任务的也要输出，而且输出结果按照person的id升序排序。
没什么好说的，左右连接搞定。注意person.id对应task.person_id。

select p.id,p.name,t.content
from person p left join task t
    on p.id=t.person_id
order by p.id

SQL65 异常的邮件概率难

要想到题目要求求的是正常用户发送邮件失败的数目/正常用户发送邮件总数目，所以必然要连接user表排除掉黑名单用户,所以第一时间写出来的是

select xxx 
from email
join user as u1 on (email.send_id=u1.id and u1.is_blacklist=0)
join user as u2 on (email.receive_id=u2.id and u2.is_blacklist=0)

这里连了两张表，发送用户和接收用户都被排除
这样就联立user表并且排除掉了所有黑名单用户，

然后就是找到日期，和每个日期邮件发送的成功率,
日期就select email.date就行了，但是成功率不太好写，
我们这里用一个函数叫case…when …then …else …end
就很简单了，如下

sum(case email.type when'completed' then 0 else 1 end)

当email.type为completed时，就为0，如果不为completed就为1，然后把这一列所有的和加起来，我们就得到了失败的发送邮件的数目，然后使用round函数保留后面3位:

round
(
    sum(case email.type when'completed' then 0 else 1 end)*1.0/count(email.type),3
)

最后再按照日期分组，得到每一天的数据，并且按照日期升序排序:group by email.date order by email.date;

select email.date, round(
    sum(case email.type when'completed' then 0 else 1 end)*1.0/count(email.type),3
) as p
from email
join user as u1 on (email.send_id=u1.id and u1.is_blacklist=0)
join user as u2 on (email.receive_id=u2.id and u2.is_blacklist=0)
group by email.date order by email.date;

SQL66 牛客每个人最近的登录日期(一)

大家多数都是用MAX () 看着代码很简洁
我写点不一样但是也是很容易理解的
我的方法是用Rank 根据 User_ID 在按照日期排序得到序号
最后嵌套抓出第一个也就是最近日期

select user_id ,date
From (

select user_id,date,rank() over(partition by user_id order by date desc ) rk
from login
)
where rk =1
order by user_id

大佬 用的是 rank 然后加了关键字 rows between current row and unbounded following
具体如下
select distinct
user_id,
last_value(date) over(partition by user_id order by date rows between current row and unbounded following) as d
from login;
大写的佩服啊 学习到新的知识了

SQL67 牛客每个人最近的登录日期(二)

题目描述：sql语句查询每个用户最近一天登录的日子，用户的名字，以及用户用的设备的名字，并且查询结果按照user的name升序排序。
考点：group by 子句常见错误：
select 子句中只能存在以下三种元素：常数、聚合函数、group by子句指定列(聚合键)
所以下面这么写法错误：

select u.name as u_n, c.name as c_n, max(l.date) as d  
from login as l
inner join user as u
on l.user_id=u.id
inner join client as c
on l.client_id=c.id
group by u.name
order by u.name asc

正确代码
select u.name as u_n,
       c.name asc_n,
       l.date
from login l join user u on l.user_id=u.id
             join client c on l.client_id=c.id
where (l.user_id,l.date) in (select user_id,max(date) from login group by user_id)
order by u_n