目录
描述:1667. 修复表中的名字 - 力扣(LeetCode)
描述:1327. 列出指定时间段内所有的下单产品 - 力扣(LeetCode)
描述:1484.按日期分组销售产品
编写解决方案找出每个日期、销售的不同产品的数量及其名称。
每个日期的销售产品名称应按词典序排列。
返回按sell_date
排序的结果表。
结果表结果格式如下例所示。
数据准备:
Create table If Not Exists Activities (sell_date date, product varchar(20))
Truncate table Activities
insert into Activities (sell_date, product) values ('2020-05-30', 'Headphone')
insert into Activities (sell_date, product) values ('2020-06-01', 'Pencil')
insert into Activities (sell_date, product) values ('2020-06-02', 'Mask')
insert into Activities (sell_date, product) values ('2020-05-30', 'Basketball')
insert into Activities (sell_date, product) values ('2020-06-01', 'Bible')
insert into Activities (sell_date, product) values ('2020-06-02', 'Mask')
insert into Activities (sell_date, product) values ('2020-05-30', 'T-Shirt')
分析:
分组求和不难,难点在于连接各组的product
group_concat()
select group_concat(product) from activities;
代码:
select sell_date,
count(distinct product) num_sold,
group_concat(distinct product) products
from Activities
group by sell_date
order by sell_date;
总结:
group_concat()用法
描述:196.删除重复的电子邮箱
编写解决方案 删除 所有重复的电子邮件,只保留一个具有最小
id
的唯一电子邮件。(对于 SQL 用户,请注意你应该编写一个
DELETE
语句而不是SELECT
语句。)运行脚本后,显示的答案是
Person
表。驱动程序将首先编译并运行您的代码片段,然后再显示Person
表。Person
表的最终顺序 无关紧要
数据准备:
Create table If Not Exists Person (Id int, Email varchar(255))
Truncate table Person
insert into Person (id, email) values ('1', 'john@example.com')
insert into Person (id, email) values ('2', 'bob@example.com')
insert into Person (id, email) values ('3', 'john@example.com')
分析:
考虑使用排名函数关于Email分组 然后过滤掉排名大于1 的
select id, email, rank() over (partition by email order by id) r1 from person
代码:
#方式一:
delete
from person
where id in (with t1 as
(select id,
email,
rank() over (partition by email order by id) r1
from person)
select id
from t1
where r1 > 1);
#方式二:
with t1 as (
select
Email,
min(id) as id
from person
group by Email
)
delete from person
where id not in (select id from t1)
;
总结:
①认识到delete在sql中的使用
②删除表和第一个子查询不能是同一个表
描述:1667. 修复表中的名字 - 力扣(LeetCode)
代码:
#数据准备
Create table If Not Exists Users (user_id int, name varchar(40))
Truncate table Users
insert into Users (user_id, name) values ('1', 'aLice')
insert into Users (user_id, name) values ('2', 'bOB')
#代码:
select user_id,
concat(upper(left(name, 1)),
lower(right(name,
length(name) - 1))) name
from users;
总结:
熟悉了以下函数的使用
concat() 连接字符串
left(列,n)/right(列,n) 取左边/右边n个长度的字符
upper()/lower() 转大/小写
描述:1327. 列出指定时间段内所有的下单产品 - 力扣(LeetCode)
代码:
#数据:
Create table If Not Exists Products (product_id int, product_name varchar(40), product_category varchar(40))
Create table If Not Exists Orders (product_id int, order_date date, unit int)
Truncate table Products
insert into Products (product_id, product_name, product_category) values ('1', 'Leetcode Solutions', 'Book')
insert into Products (product_id, product_name, product_category) values ('2', 'Jewels of Stringology', 'Book')
insert into Products (product_id, product_name, product_category) values ('3', 'HP', 'Laptop')
insert into Products (product_id, product_name, product_category) values ('4', 'Lenovo', 'Laptop')
insert into Products (product_id, product_name, product_category) values ('5', 'Leetcode Kit', 'T-shirt')
Truncate table Orders
insert into Orders (product_id, order_date, unit) values ('1', '2020-02-05', '60')
insert into Orders (product_id, order_date, unit) values ('1', '2020-02-10', '70')
insert into Orders (product_id, order_date, unit) values ('2', '2020-01-18', '30')
insert into Orders (product_id, order_date, unit) values ('2', '2020-02-11', '80')
insert into Orders (product_id, order_date, unit) values ('3', '2020-02-17', '2')
insert into Orders (product_id, order_date, unit) values ('3', '2020-02-24', '3')
insert into Orders (product_id, order_date, unit) values ('4', '2020-03-01', '20')
insert into Orders (product_id, order_date, unit) values ('4', '2020-03-04', '30')
insert into Orders (product_id, order_date, unit) values ('4', '2020-03-04', '60')
insert into Orders (product_id, order_date, unit) values ('5', '2020-02-25', '50')
insert into Orders (product_id, order_date, unit) values ('5', '2020-02-27', '50')
insert into Orders (product_id, order_date, unit) values ('5', '2020-03-01', '50')
#代码:
select product_name,sum(unit)unit
from orders join products
on Orders.product_id = Products.product_id
where order_date between '2020-02-01' and '2020-02-29'
group by product_name
having sum(unit) >=100
总结:
过滤日期也可以使用: where order_date like '2020-02%'