当前位置：首页 > news >正文

力扣-SQL【入门】

news 2025/8/23 18:18:38

https://leetcode.cn/study-plan/sql/?progress=xhqm4sjh

选择

595. 大的国家

在这里插入图片描述

# Write your MySQL query statement below
select name,population,area
from World
where area>=3000000 or population>=25000000;

# Write your MySQL query statement below
select name,population,area
from World
where area>=3000000
union 
select name,population,area
from World
where population>=25000000;

1757. 可回收且低脂的产品

在这里插入图片描述

# Write your MySQL query statement below
select product_id
from Products
where low_fats='Y' and recyclable='Y';

584. 寻找用户推荐人

在这里插入图片描述

# Write your MySQL query statement below
select name
from customer
where referee_id<>2 or referee_id is NULL;

183. 从不订购的客户

在这里插入图片描述

# Write your MySQL query statement below
select Name Customers
from Customers
where Customers.Id not in(select  CustomerIdfrom Orders
);

排序 & 修改

1873. 计算特殊奖金

在这里插入图片描述
SQL中IF函数的使用:
IF(expr1,expr2,expr3)

expr1 的值为 TRUE，则返回值为 expr2
expr1 的值为FALSE，则返回值为 expr3

# Write your MySQL query statement below
select employee_id,if(employee_id%2=1 and name not like 'M%',salary,0) bonus
from Employees
order by employee_id;

627. 变更性别

在这里插入图片描述
case when的使用方法:

case 列名when   条件值1   then  选项1when   条件值2    then  选项2.......else     默认值      end

# Write your MySQL query statement below
update Salary
set sex=case sexwhen 'f' then 'm'else 'f' end
;

# Write your MySQL query statement below
update Salary
set sex=case when sex='f'then 'm'else 'f' end
;

196. 删除重复的电子邮箱

在这里插入图片描述

# Please write a DELETE statement and DO NOT write a SELECT statement.
# Write your MySQL query statement below
delete p1
from Person p1,Person p2
where p1.email=p2.email and p1.id>p2.id;