力扣-SQL【入门】_力扣sql题库在哪做

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;
1
2
3
4

# 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;
1
2
3
4
5
6
7
8

1757. 可回收且低脂的产品

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

584. 寻找用户推荐人

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

183. 从不订购的客户

# Write your MySQL query statement below
select Name Customers
from Customers
where Customers.Id not in(
    select  CustomerId
    from Orders
);
1
2
3
4
5
6
7

排序 & 修改

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;        
1
2
3
4

627. 变更性别

case when的使用方法:

case 列名

　　　　when   条件值1   then  选项1
　　　　when   条件值2    then  选项2.......
　　　　else     默认值      end
1
2
3
4
5

# Write your MySQL query statement below
update Salary
set sex=
    case sex
    when 'f' then 'm'
    else 'f' end
;
1
2
3
4
5
6
7

# Write your MySQL query statement below
update Salary
set sex=
    case when sex='f'then 'm'
    else 'f' end
;
1
2
3
4
5
6

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;
1
2
3
4
5

字符串处理函数/正则

1667. 修复表中的名字

CONCAT() 函数： CONCAT 可以将多个字符串拼接在一起。

• 使用方法为：CONCAT(str1,str2,…)

LENGTH 函数： 用于返回字符串的字节长度，长度单位为字节。

• 使用方法为：LENGTH(str1)

LOWER 函数： 将字符串中所有字符转为小写。

• 使用方法为：LOWER(str1)

UPPER 函数： 将字符串中所有字符转为大写。

• 使用方法为：UPPER(str1)

LEFT 函数： 从左开始截取字符串，length 是截取的长度。

• 使用方法为：LEFT(str, length)

RIGHT 函数： 从右开始截取字符串，length 是截取的长度。

• 使用方法为：RIGHT(str, length)

SUBSTRING 函数：

• substring(string ，index) substring(被截取的字符串 , 开始位置序号)
• substring(string ，index，len)substring(被截取字符串 ，开始位置，长度)

select user_id,concat( upper(left(name,1)),lower(right(name,length(name)-1)) ) as name
from Users
order by user_id;
1
2
3

1484. 按日期分组销售产品

group_concat函数： 是将分组中括号里对应的字符串进行连接.如果分组中括号里的参数xxx有多行，那么就会将这多行的字符串连接，每个字符串之间会有特定的符号进行分隔。

# Write your MySQL query statement below
select sell_date,count(distinct product) num_sold,group_concat(distinct product) products
from Activities
group by sell_date;
1
2
3
4

1527. 患某种疾病的患者

# Write your MySQL query statement below
select patient_id,patient_name,conditions
from Patients
where conditions like "DIAB1%"
union
select patient_id,patient_name,conditions
from Patients
where conditions like "% DIAB1%";
1
2
3
4
5
6
7
8

# Write your MySQL query statement below
select patient_id,patient_name,conditions
from Patients
where conditions like "DIAB1%" or conditions like "% DIAB1%";
1
2
3
4

组合查询 & 指定选取

1965. 丢失信息的雇员

# Write your MySQL query statement below
select employee_id
from Employees
where employee_id not in(
    select employee_id
    from Salaries
)
union 
select employee_id
from Salaries
where employee_id not in(
    select employee_id
    from Employees
)
order by employee_id;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

1795. 每个产品在不同商店的价格(列转行)

# Write your MySQL query statement below
select product_id,"store1" store,store1 price
from Products
where store1 is not null
union
select product_id,"store2" store,store2 price
from Products
where store2 is not null
union
select product_id,"store3" store,store3 price
from Products
where store3 is not null;
1
2
3
4
5
6
7
8
9
10
11
12

608. 树节点

# Write your MySQL query statement below
select id,"Root" Type
from tree
where p_id is null
union
select id,"Inner" type
from tree
where p_id is not null and id in(
    select p_id
    from tree
    where p_id is not null
)
union
select id,"Leaf" type
from tree
where p_id is not null and id not in(
    select p_id
    from tree
    where p_id is not null
)
order by id;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

176. 第二高的薪水

# Write your MySQL query statement below
select max(salary)  SecondHighestSalary 
from Employee
where salary<(select max(salary) from Employee);
1
2
3
4

合并

175. 组合两个表

左连接

select  firstName,lastname,city,state
from Person
left join Address
on Person.personId=address.personId;
1
2
3
4

1581. 进店却未进行过交易的顾客

# Write your MySQL query statement below
select  customer_id,count(visit_id) count_no_trans
from Visits
where visit_id not in (
    select distinct visit_id
    from Transactions
)
group by customer_id;
1
2
3
4
5
6
7
8

1148. 文章浏览 I

# Write your MySQL query statement below
select distinct author_id id
from Views
where author_id=viewer_id
order by author_id;
1
2
3
4
5

合并

197. 上升的温度

datediff()函数返回两个日期之间的时间。

• datediff(datepart,startdate.endddate)
startdate和enddate是你要计算的开始时间和截止时间
  datepart可以是下面的值：年，月 ，周，日，时，分，秒

# Write your MySQL query statement below
select b.id
from Weather a,Weather b
where datediff(b.recordDate,a.recordDate)=1 and b.temperature>a.temperature;
1
2
3
4

607. 销售员

# Write your MySQL query statement below
select name
from SalesPerson
where sales_id not in(
    select sales_id
    from Orders
    where com_id in(
        select com_id
        from company
        where name="RED"
    )
);
1
2
3
4
5
6
7
8
9
10
11
12