10月12号 leetcode SQL复习

最新推荐文章于 2025-06-13 14:49:30 发布

时俗之俗

最新推荐文章于 2025-06-13 14:49:30 发布

阅读量108

点赞数

分类专栏：日记文章标签： sql leetcode oracle

本文链接：https://blog.youkuaiyun.com/weixin_40857308/article/details/120732211

版权

日记专栏收录该内容

24 篇文章

订阅专栏

这篇博客探讨了如何在SQL中实现复杂查询，包括找出部门的第二高薪资、实现员工性别互换以及计算分数排名。通过使用子查询、LIMIT语句和CASE WHEN END语句，可以解决这些问题。同时，还展示了如何查找连续数字序列以及在座位交换场景下更新学生座位的方法。这些技巧对于理解和优化SQL查询至关重要。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

Department Highest Salary

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

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

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;

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)

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

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