sql刷题技巧与思路分析（易错点笔记）

杂项

JOIN 不加 ON 就是CROSS JOIN。JOIN 加ON 就是INNER JOIN

MySQL 使用三值逻辑 —— TRUE, FALSE 和 UNKNOWN。任何与 NULL 值进行的比较都会与第三种值 UNKNOWN 做比较。这个“任何值”包括 NULL 本身！这就是为什么 MySQL 提供 IS NULL 和 IS NOT NULL 两种操作来对 NULL 特殊判断。

因此，在 WHERE 语句中我们需要做一个额外的条件判断 `referee_id IS NULL’。

Top N 问题

N未知

limit不支持运算，所以不能直接N-1，需要先声明一个int型变量m，并且set他的值为N-1。
另外，这题不需要再为列起别名，因为在一个函数里，这个函数返回的是一个int值，那么后台在调用这个函数时，返回的列名就是——函数名(N)

limit 1 offset m 等同于 limit m,1

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
declare m INT;
SET m = N-1;
  RETURN (
      # Write your MySQL query statement below.
      select ifnull
      (
          (
              select distinct salary
              from employee
              order by salary desc
              limit 1 offset m
          ), null
      )
  );
END

N已知

SELECT
    IFNULL(
      (SELECT DISTINCT Salary
       FROM Employee
       ORDER BY Salary DESC
        LIMIT 1 OFFSET 1),
    NULL) AS SecondHighestSalary

题目要求，如果没有第二高的成绩，返回空值，所以这里用判断空值的函数（ifnull）函数来处理特殊情况。

ifnull(a,b)函数解释：
如果value1不是空，结果返回a
如果value1是空，结果返回b

isnull(expr) 的用法： 如expr 为null，那么isnull() 的返回值为 1，否则返回值为 0。 mysql> select isnull(1+1); -> 0 mysql> select isnull(1/0); -> 1 使用= 的null 值对比通常是错误的。
isnull() 函数同 is null比较操作符具有一些相同的特性。请参见有关is null 的说明。
IFNULL(expr1,expr2)的用法：

假如expr1 不为 NULL，则 IFNULL() 的返回值为 expr1; 否则其返回值为 expr2。IFNULL()的返回值是数字或是字符串，具体情况取决于其所使用的语境。

limit n子句表示查询结果返回前n条数据

offset n表示跳过x条语句

limit y offset x 分句表示查询结果跳过 x 条数据，读取前 y 条数据

使用limit和offset，降序排列再返回第二条记录可以得到第二大的值。

查找重复项

查找重复项
使用 GROUP BY 和 HAVING 条件
向 GROUP BY 添加条件的一种更常用的方法是使用 HAVING 子句，该子句更为简单高效。

select Email
from Person
group by Email
having count(Email) > 1;

删除重复项

delete p1
from Person p1,person p2
where p1.email=p2.email and p1.id>p2.id;

1、DELETE p1

在DELETE官方文档中，给出了这一用法，比如下面这个DELETE语句

DELETE t1 FROM t1 LEFT JOIN t2 ON t1.id=t2.id WHERE t2.id IS NULL;

这种DELETE方式很陌生，竟然和SELETE的写法类似。它涉及到t1和t2两张表，DELETE t1表示要删除t1的一些记录，具体删哪些，就看WHERE条件，满足就删；

这里删的是t1表中，跟t2匹配不上的那些记录。

所以，官方sql中，DELETE p1就表示从p1表中删除满足WHERE条件的记录。

2、p1.Id > p2.Id

a. 从驱动表（左表）取出N条记录；
b. 拿着这N条记录，依次到被驱动表（右表）查找满足WHERE条件的记录；

a. 从表p1取出3条记录；
b. 拿着第1条记录去表p2查找满足WHERE的记录，代入该条件p1.Email = p2.Email AND p1.Id > p2.Id后，发现没有满足的，所以不用删掉记录1；
c. 记录2同理；
d. 拿着第3条记录去表p2查找满足WHERE的记录，发现有一条记录满足，所以要从p1删掉记录3；
e. 3条记录遍历完，删掉了1条记录，这个DELETE也就结束了。

排序问题

rank函数知识点
rank()：是并列排序，会跳过重复序号
dense_rank()：是并列排序，不会跳过重复序号
row_number()：是顺序排序，不跳过任何一个序号，就是行号
用法：dense_rank() over (order by score desc)，达到该列值按照score自增若score相同自动重复的效果

select score, dense_rank() over (order by score desc) as 'rank'
from scores;

连续出现的问题

连续出现的意味着相同数字的 Id 是连着的
然后我们从上表中选择任意的 Num 获得想要的答案。同时我们需要添加关键字 DISTINCT ，因为如果一个数字连续出现超过 3 次，会返回重复元素。

SELECT DISTINCT
    l1.Num AS ConsecutiveNums
FROM
    Logs l1,
    Logs l2,
    Logs l3
WHERE
    l1.Id = l2.Id - 1
    AND l2.Id = l3.Id - 1
    AND l1.Num = l2.Num
    AND l2.Num = l3.Num
;

时间换算

select a.ID, a.date
from weather as a cross join weather as b 
     on datediff(a.date, b.date) = 1
where a.temp > b.temp;


select a.ID, a.date
from weather as a cross join weather as b 
     on timestampdiff(day, a.date, b.date) = -1
where a.temp > b.temp;