Leetcode+牛客+面试 SQL错题整理

200409 之前刷过10道SQL这次再写一遍，不会写的还是不会写，服了😭

🧾 需要注意的题目
🚩200904 leetcode

• 176.Second Highest Salary
• 177.Nth Highest Salary
• 178.Rank Scores
• 185.Department Top Three Salaries

🚩200909 面试题

• 计算连续登陆天数

（这里需要注意的点是：如果跨月、跨年该怎么办? 网上用的day(TIME) - row_number在这个场景下是不可行的）

---

200904

175.Combine Two Tables

176.Second Highest Salary

SELECT MAX(Salary) AS SecondHighestSalary
FROM Employee
WHERE Salary < (SELECT MAX(Salary) FROM Employee)

---

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

知识点：sql 中 limit & offset

select * from table limit 2,1 == select * from table limit 1 offset 2
含义是跳过2条取出1条数据，limit后面是从第2条开始读，读取1条信息，即读取第3条数据

知识点：IFNULL

177.Nth Highest Salary

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
  RETURN (
      SELECT IFNULL(
          (SELECT DISTINCT Salary
          FROM Employee
          ORDER BY Salary DESC
          LIMIT 1 OFFSET (N-1)),NULL));
END

• 报错：check the manual that corresponds to your MySQL server version for the right syntax to use near ‘(N-1)),NULL)); END’ at line 10
• 错在需要声明变量

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)); #也可以写LIMIT M, 1
END

知识点：如何自定义函数

DELIMITER $$
#定义结束符。MySQL默认的结束符是分号，但是函数体中可能用到分号。为了避免冲突，需要另外定义结束符
DROP FUNCTION IF EXISTS 函数名$$
CREATE FUNCTION 函数名（参数名+参数类型）RETURNS 返回类型
BEGIN
	函数体
END $$
DELIMITER ;

函数体：肯定会有return语句，如果没有会报错
	   如果return语句没有放在函数体的最后也不报错，但不建议
       函数体中只有一句话，则可以省略begin end
       使用delimiter语句设置结束标记

178.Rank Scores

补充知识点：MySQL中rank()、row_number()、dense_rank()排序

(但是在leetcode上不能通过)

SELECT S.score, COUNT(S2.score) AS "Rank" 
FROM Scores S,
(SELECT DISTINCT score FROM Scores) S2
WHERE S.score<=S2.score
GROUP BY S.Id #⚠️
ORDER BY S.score DESC;

180.Consecutive Numbers

Write a SQL query to find all numbers that appear at least three times consecutively.

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;
 # 注意这里要分开写
 # 合并写不行l1.Num = l2.Num = l3.Num

---

SELECT DISTINCT num
FROM (SELECT num,LEAD(num) OVER(ORDER BY id) AS lead, 
LAG(num) OVER (ORDER BY id) AS lag
FROM Logs) t
WHERE num=lead and num=lag; # 但是在leetcode上不能通过

知识点：LEAD和LAG函数

LEAD ( scalar_expression [ ,offset ] , [ default ] ) OVER ( [ partition_by_clause ] order_by_clause )，依次为读取的数据，偏移几行，如果没有值默认值是多少，写法如下：

LEAD(Value) 默认offset = 1，default = NULL

181.Employees Earning More Than Their Managers

182.Duplicate Emails

183.Customers Who Never Order

184.Department Highest Salary

185.Department Top Three Salaries

一般选出前n个都是 COUNT(DISTINCT XX) > 某个值
A top 3 salary in this company means there is no more than 3 salary bigger than itself in the company.

select d.Name Department,e1.Name Employee, e1.Salary Salary
from Employee e1
join Department d
on e1.DepartmentId = d.Id
where 
	(select count(distinct e2.Salary)
	from Employee e2
	where e1.Salary <= e2.Salary
	and e1.DepartmentId = e2.DepartmentId) <= 3;

200909

最长的连续登陆天数

SQL语句如何查询各个用户最长的连续登陆天数？
思路可以参考这个大神的第一种做法

DROP TABLE IF EXISTS loadrecord;

CREATE TABLE IF NOT EXISTS `loadrecord` (
  `uid` int(6) unsigned NOT NULL,
  `loadtime` date NOT NULL,
  PRIMARY KEY (`uid`,`loadtime`)
) DEFAULT CHARSET=utf8;

INSERT INTO `loadrecord` (`uid`, `loadtime`) VALUES
  ('201', '2017/1/1'),
  ('201', '2017/1/2'),
  ('202', '2017/1/2'),
  ('202', '2017/1/3'),
  ('203', '2017/1/3'),
  ('201', '2017/1/4'),
  ('202', '2017/1/4'),
  ('201', '2017/1/5'),
  ('202', '2017/1/5'),
  ('201', '2017/1/6'),
  ('203', '2017/1/6'),
  ('203', '2017/1/7'),
  ('203', '2017/1/28'),
  ('203', '2017/1/29'),
  ('203', '2017/1/30'),
  ('203', '2017/1/31'),
  ('203', '2017/2/1'),
  ('201', '2017/12/28'),
  ('201', '2017/12/29'),
  ('201', '2017/12/30'),
  ('201', '2017/12/31'),
  ('201', '2018/1/1');
  
select uid, max(con)
from
	(select uid, 
			(d1-d2) d3, 
			count(*) con
	from 
		(select uid,
				loadtime, 
				datediff(curdate(),loadtime) d1, 
				row_number() over(partition by uid order by loadtime desc) d2
		from loadrecord) temp
	group by uid, d3) temp1
group by uid;