链接：www.nowcoder.com/ta/sql?page…
计算排名
筛选出现3次以上的特征，group by分组后having筛选
计算排名，并列排名distinct / 使用窗口函数
计算概率，sum() case when
- 对于employees表中，输出first_name排名(按first_name升序排序)为奇数的first_name 错误：
select a.first_name
from employees a
where (select count(*) from employees b where a.first_name <= b.first_name)%2=1
报错：
修正：
select a.first_name
from employees a
where (select count(*) from employees b where a.first_name >= b.first_name)%2=1
计算排名时，与累加求和相反，在表a中进行排名，那么表a中该行的排名=表b中相同行的前面有几个。
- 在牛客刷题的小伙伴们都有着牛客积分，积分(grade)表简化可以如下:
id为用户主键id，number代表积分情况，让你写一个sql查询，积分表里面出现三次以及三次以上的积分，查询结果如下:
错误：
select number from grade
where count(number) >= 3
select number from grade
where count(id) >= 3
group by number
修正：
select number from grade
group by number
having count(id)>=3
将积分分组，在分组结果的基础上查询，需要用having,而不是在where子句中筛选。
- 在牛客刷题有一个通过题目个数的(passing_number)表，id是主键，简化如下:
第1行表示id为1的用户通过了4个题目;
.....
第6行表示id为6的用户通过了4个题目;
请你根据上表，输出通过的题目的排名，通过题目个数相同的，排名相同，此时按照id升序排列，数据如下:
id为5的用户通过了5个排名第1，
id为1和id为6的都通过了2个，并列第2
select a.id, a.number, (select count(distinct number) from passing_number b where a.number<=b.number ) rank
from passing_number a
order by a.number desc,a.id
在通过的代码中看到下面这种写法
select id,number,dense_rank()over(order by number desc)as rank
from passing_number
order by number desc,id
MYSQL实现窗口函数详情简介
- 有一个person表，主键是id，如下:
有一个任务(task)表如下，主键也是id，如下:
请你找到每个人的任务情况，并且输出出来，没有任务的也要输出，而且输出结果按照person的id升序排序，输出情况如下:
select p.id, name,content
from person p
left join task t on p.id=t.person_id
order by p.id
- 现在有一个需求，让你统计正常用户发送给正常用户邮件失败的概率:
有一个邮件(email)表，id为主键， type是枚举类型，枚举成员为(completed，no_completed)，completed代表邮件发送是成功的，no_completed代表邮件是发送失败的。简况如下:
第1行表示为id为2的用户在2020-01-11成功发送了一封邮件给了id为3的用户;
...
第3行表示为id为1的用户在2020-01-11没有成功发送一封邮件给了id为4的用户;
...
第6行表示为id为4的用户在2020-01-12成功发送了一封邮件给了id为1的用户;
下面是一个用户(user)表，id为主键，is_blacklist为0代表为正常用户，is_blacklist为1代表为黑名单用户，简况如下:
第1行表示id为1的是正常用户;
第2行表示id为2的不是正常用户，是黑名单用户，如果发送大量邮件或者出现各种情况就会容易发送邮件失败的用户
...
第4行表示id为4的是正常用户
现在让你写一个sql查询，每一个日期里面，正常用户发送给正常用户邮件失败的概率是多少，结果保留到小数点后面3位(3位之后的四舍五入)，并且按照日期升序排序，上面例子查询结果如下:
结果表示: 2020-01-11失败的概率为0.500，因为email的第1条数据，发送的用户id为2是黑名单用户，所以不计入统计，正常用户发正常用户总共2次，但是失败了1次，所以概率是0.500; 2020-01-12没有失败的情况，所以概率为0.000. (注意: sqlite 1/2得到的不是0.5，得到的是0，只有1*1.0/2才会得到0.5，sqlite四舍五入的函数为round)
错误：
select e.date, round(select count(*) from e where type='no_completed' *1.0/ count(*))
from email e
join user u1 on e.send_id=u1.id
join user u2 on e.receive_id=u2.id
where u1.is_blacklist=0 and u2.is_blacklist=0
group by e.date
order by e.date
修改：
select e.date, round(sum(case e.type when 'no_completed' then 1 else 0 end) *1.0/ count(*),3)
from email e
join user u1 on e.send_id=u1.id
join user u2 on e.receive_id=u2.id
where u1.is_blacklist=0 and u2.is_blacklist=0
group by e.date
order by e.date
3个值得注意的点：
- 条件筛选后计数可以用 sum() 搭配 case when
- 连接同一张表2次
- round()的用法
