精讲Mysql各种高难度Sql编写（一）

   在每年的面试高峰期，面试官为了筛选优秀的Java研发人员 ，往往会在面试题里面增加编写sql，普通的sql大家都会写，所以会把sql的难度提高。
   所以，今天这篇高难度sql，是为了解决大家在面试的难题，从sql脚本，数据插入，sql的CRUD，以及高难度查询，基本上面面俱到。相信能给小伙伴们一点帮助！
一、首先，为了让大家能够看懂后面的sql，需要复习一下基础。下面是两表sql查询的几种方式，单表的增删改查就不讲了，相信大家都会。然后，这里，需要强调一下，mysql是不支持full join的，Oracle支持

二、然后说下笛卡尔积，有些小伙伴可能不知道，简单描述一下，就是一张表的每一列与另外一张表的每一列，一 一匹配，形成总数据工作中不推荐，容易产生冗余数据，它跟上面的 inner join的不同是，上面加了where条件
笛卡尔积的三种写法:
select * from t1 join t2;
select * from t1 inner join t2;
select * from t1, t2;

三、我们用LeetCode数据库,第176题作为热身题

sql1

sql脚本
DROP TABLE IF EXISTS employee;
CREATE TABLE employee (
id int(0) NOT NULL AUTO_INCREMENT,
salary decimal(10, 2) NULL DEFAULT NULL,
PRIMARY KEY (id) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

INSERT INTO employee VALUES (1, 100.00);
INSERT INTO employee VALUES (2, 200.00);
INSERT INTO employee VALUES (3, 300.00);

要求:查询第二高的薪水

写法一、使用 IFNull 函数判断是否为空，如果为空返回Null
使用 distinct 关键字对薪水去重

select IFNULL((select distinct salary from employee order by salary desc limit 1,1),NULL) as secondTop

写法二、 利用Max,not in 嵌套查询

select max(salary) from employee where salary not in (select max(salary) from employee)

写法三、使用Mysql函数查询，首先需要打开binlog,函数开关

set global log_bin_trust_function_creators=TRUE;

创建函数，简单说下，dense_rank() Mysql 8.0窗口函数，然后必须搭配 over使用，在over里面增加排序，用where做条件过滤，where后面不要用rank，那是关键字

CREATE FUNCTION getSecondSalary(N INT) RETURNS INT
BEGIN
  RETURN (
        SELECT 
            DISTINCT salary
        FROM 
            (SELECT 
                salary, dense_rank() over(ORDER BY salary DESC) AS ranks
             FROM 
                employee) tmp
        WHERE ranks = N
  );
END

执行函数，查询排名第二的薪水

select getSecondSalary(2)

sql2

LeetCode 180题

要求:编写一个 SQL 查询，查找所有至少连续出现三次的数字

sql 脚本
DROP TABLE IF EXISTS numbers;
CREATE TABLE numbers (
id int(0) NOT NULL AUTO_INCREMENT,
Num int(0) NULL DEFAULT NULL,
PRIMARY KEY (id) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

INSERT INTO numbers VALUES (1, 1);
INSERT INTO numbers VALUES (2, 1);
INSERT INTO numbers VALUES (3, 1);
INSERT INTO numbers VALUES (4, 2);
INSERT INTO numbers VALUES (5, 1);
INSERT INTO numbers VALUES (6, 2);
INSERT INTO numbers VALUES (7, 2);

方法一、官方解法，我只能说牛批，大概的意思是，既然是3个连续的数字，那么我就给3张一样的表，让他们在不同的Id 下，Num相同。也就是说，把每一行当成一个表进行查询

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

方法二、大神解法，当然不是我写的，哈哈哈
我来解读一下吧，相信很多小伙伴们，可能第一次看到这种写法，如果不解释的话，完全懵的。

    先从最后一个SELECT说起，为什么从最后一个说起，因为最后一个SELECT是开头，它定义了一个变量叫做 @pre等同于java里面的 String str,然后 := 就是java的＝,还有一个变量是
@dcount，赋值为1

    然后从第二个SELECT说起，IF里面做判断，if里面的第三个参数的意思是elseif的结果，如果@pre 等于传进来的第一个数字,那么就加1，否则还是为1。

    说实话，FROM上面最近的 @pre := L.num ，我想了好久，才明白其中的意思，它的目的是相当于一次循环，因为IF执行完以后，需要再次判断里面的数据，那么L.num就把值给到@pre,就是相当于一次更新，那么，@pre一更新，IF就需要再判断一次，直到表里面的数据没有为止

    外面这个SELECT就不讲了，明白人都知道

SELECT DISTINCT num as ConsecutiveNums FROM(
SELECT 
  L.`num`,
  IF(
    @pre = L.`num`,
    @dcount := @dcount + 1,
    @dcount := 1
  ) AS dcounts,
  @pre := L.`num` 
FROM
  `numbers` AS L,
  (SELECT 
    @pre := NULL,
    @dcount := 1) AS tmp) as t where t.dcounts >=3 ;

sql3

LeetCode 184题
要求: 查询每个部门工资最高的员工

sql 脚本
DROP TABLE IF EXISTS employee2;
CREATE TABLE employee2 (
Id int(11) DEFAULT NULL,
NAME char(10) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
Salary int(11) DEFAULT NULL,
DepartmentId char(2) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = latin1 COLLATE = latin1_swedish_ci ROW_FORMAT = Compact;

INSERT INTO employee2 VALUES (1, ‘Joe’, 70000, ‘1’);
INSERT INTO employee2 VALUES (2, ‘Hery’, 80000, ‘2’);
INSERT INTO employee2 VALUES (3, ‘Sam’, 60000, ‘2’);
INSERT INTO employee2 VALUES (4, ‘Max’, 90000, ‘1’);

DROP TABLE IF EXISTS department;
CREATE TABLE department (
Id int(11) DEFAULT NULL,
NAME char(10) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = latin1 COLLATE = latin1_swedish_ci ROW_FORMAT = Compact;

INSERT INTO department VALUES (1, ‘IT’);
INSERT INTO department VALUES (2, ‘Sales’);

方法一、说下思路吧，两表内连接然后根据部门分组，用max函数查询薪资最高的，这种数据量比较多时，性能比较低

select c.deptname,c.name,max(salary) from (
select a.*,b.name as deptname from employee2 a inner join Department  b on 
a.DepartmentId=b.id 
)as c GROUP BY c.deptname

方法二、官方解法，我觉得这个非常巧妙,一开始就通过子查询，拿到部门id，然后用内连接加上 in 得到各部门最高薪资，效率极高！

SELECT
    Department.name AS 'Department',
    Employee.name AS 'Employee',
    Salary
FROM
    Employee
        JOIN
    Department ON Employee.DepartmentId = Department.Id
WHERE
    (Employee.DepartmentId , Salary) IN
    (   SELECT
            DepartmentId, MAX(Salary)
        FROM
            Employee
        GROUP BY DepartmentId
	)