10月12号 leetcode SQL复习

184. Department Highest Salary

Having 条件是用来筛选group的而不是用来筛选group内部条件的。

176. Second Highest Salary

查询第二多花费得工资。Limit语句

mysql> SELECT * FROM table LIMIT 5,10; // 检索记录行 6-15

如果不存在这种情况，直接会输出空字符，然而我们想让他输出null
如果遇到NULL的情况
再套一层，因为 select null会返回null

# Write your MySQL query statement below
select
    (Select 
        Distinct Salary SecondHighestSalary 
    From 
        Employee 
        order by Salary Desc
        limit 1,1) as SecondHighestSalary

同理,找到第N高的工资：

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
  set N=N-1;
  RETURN (
      # Write your MySQL query statement below.
      select
    (Select 
        Distinct Salary SecondHighestSalary 
    From 
        Employee 
        order by Salary Desc
        limit N,1) as SecondHighestSalary
      
  );
END

627. Swap Salary

update 题目

update Salary 
set 
    sex = if (sex='m','f','m')

推荐使用case when end

UPDATE Salary 
SET sex =
CASE
		sex 
	WHEN 'm' THEN
	'f' ELSE 'm' END;

178. Rank Scores

rank 排序题目：

select Score,dense_rank() over(order by score desc) as "Rank" from scores

dense_rank()
rank()
over(partition by sth order by sth)

180. Consecutive Numbers

连续数的思路

select
    distinct l1.num ConsecutiveNums 
From 
    logs l1,
    logs l2,
    logs l3
where
    l1.Num = l2.num and l2.num= l3.num and l1.id =l2.id-1 and l2.id= l3.id-1

inner join的解法

# Write your MySQL query statement below
SELECT
	DISTINCT M.num ConsecutiveNums 
FROM
	(
	SELECT
		l1.id,
		l1.num 
	FROM
		LOGS l1
		INNER JOIN LOGS l2 ON l1.num = l2.num 
		AND l1.id = l2.id - 1 
	) M
	INNER JOIN LOGS l3 ON M.num = l3.num 
	AND M.id = l3.id - 2

分辨好主体

select distinct(l.Num) ConsecutiveNums 
from Logs l 
inner join Logs l2 on l.Num = l2.Num and  l.id = l2.id+1
inner join Logs l3 on l.Num = l3.Num and  l.id = l3.id+2

626. Exchange Seats

case when的例子：

select 
    (case 
        when id%2=1 and id =(select count(*) from seat) then id
        when id%2=1 then id+1
        else id-1
     end
     ) as id,student
from
    seat
ORDER BY id