# leetcode sql刷题之简单题

## 183.找出从不订购的顾客

#做法一
select Name as Customers from Customers
where name not in(
select Name  from Customers,Orders
where Customers.Id=Orders.CustomerID )

#做法二 左外联结
select Customers.Name as Customers from Customers
left outer join Orders on Customers.Id=Orders.CustomerID
where Orders.Id IS NULL


## 173.组合两个表

Leetcode sql.173

#左外联结


## 第586题.订单最多的客户

leetcode sql第586题

#写法一
select customer_number from Orders
group by customer_number
having count(order_number)>=all(  #该用法可以用于表格
select count(order_number)
from Orders
group by customer_number)

#方法二
select customer_number from Orders
group by customer_number
having count(order_number) in #注意这里不能用=
(select max(f) #max函数里只能放列，不能放表格
from(select count(order_number) as f
from Orders
group by customer_number) t #别名
)


## 196.删除重复的电子邮箱

delete from person
where id not in
(
select id
from
(
select min(id) as id --不能直接被选择
from person
group by email
) t
)

最佳
delete a from Person a , Person b
where a.email = b.email  and a.id > b.id


leetcode sql 197

## 627.变更性别

leetcode sql 627

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



## 1179. 重新格式化部门表

1197

SELECT  id,
SUM(CASE WHEN month='Jan' THEN revenue ELSE NULL END ) AS Jan_Revenue,
SUM(CASE WHEN month='Feb' THEN revenue ELSE NULL END ) AS Feb_Revenue,
SUM(CASE WHEN month='Mar' THEN revenue ELSE NULL END ) AS Mar_Revenue,
SUM(CASE WHEN month='Apr' THEN revenue ELSE NULL END ) AS Apr_Revenue,
SUM(CASE WHEN month='May' THEN revenue ELSE NULL END ) AS May_Revenue,
SUM(CASE WHEN month='Jun' THEN revenue ELSE NULL END ) AS Jun_Revenue,
SUM(CASE WHEN month='Jul' THEN revenue ELSE NULL END ) AS Jul_Revenue,
SUM(CASE WHEN month='Aug' THEN revenue ELSE NULL END ) AS Aug_Revenue,
SUM(CASE WHEN month='Sep' THEN revenue ELSE NULL END ) AS Sep_Revenue,
SUM(CASE WHEN month='Oct' THEN revenue ELSE NULL END ) AS Oct_Revenue,
SUM(CASE WHEN month='Nov' THEN revenue ELSE NULL END ) AS Nov_Revenue,
SUM(CASE WHEN month='Dec' THEN revenue ELSE NULL END ) AS Dec_Revenue
FROM Department
GROUP BY id


select id,
max(if(month='Jan',revenue,null))as Jan_Revenue,
max(if(month='Feb',revenue,null))as Feb_Revenue,
max(if(month='Mar',revenue,null))as Mar_Revenue,
max(if(month='Apr',revenue,null))as Apr_Revenue,
max(if(month='May',revenue,null))as May_Revenue,
max(if(month='Jun',revenue,null))as Jun_Revenue,
max(if(month='Jul',revenue,null))as Jul_Revenue,
max(if(month='Aug',revenue,null))as Aug_Revenue,
max(if(month='Sep',revenue,null))as Sep_Revenue,
max(if(month='Oct',revenue,null))as Oct_Revenue,
max(if(month='Nov',revenue,null))as Nov_Revenue,
max(if(month='Dec',revenue,null))as Dec_Revenue
from Department
group by 1 #select的第一个数


1.if函数可以作为casewhen 函数的二元简化版本，1158题也出现过。
2.必须要用聚合函数的原因

## 1667.修复表中的名字

select user_id,
concat(upper(left(name,1)),lower(substr(name,2))) as name
#concat函数的使用与substr的用法
from Users
order by user_id



## 1484.按日期分组销售产品

select sell_date,
count(sell_date) as num_sold,
group_concat(distinct product order by product separator ',') as products
#group_concat的使用，加入分隔符","  ,distinct
from Activities
group by sell_date
order by sell_date;


## 1795.

leetcode 1795

select product_id, 'store1' as store, store1 as price
from Products
where store1 is not null
union
select product_id, 'store2' as store, store2 as price
from Products
where store2 is not null
union
select product_id, 'store3' as store, store3 as price
from Products
where store3 is not null;


## 1407. 排名靠前的旅行者

leetcode sql 1407

select name,ifnull(sum(distance),0) as travelled_distance
#外联结这行不用管前缀
from Users
left outer join Rides on Users.id=Rides.user_id
group by user_id #防止重名的情况
order by travelled_distance desc,name #用别名，desc是降序


ifnull函数用来给null赋值为0

## 1587. 银行账户概要 II

leetcode sql 1587

select name,sum(amount) as balance
from Users
left join Transactions on Users.account =Transactions.account
group by Transactions.account #当两个表有列名一样的时候需指定
having sum(amount)>=10000

• 1
点赞
• 0
收藏
觉得还不错? 一键收藏
• 打赏
• 3
评论

### “相关推荐”对你有帮助么？

• 非常没帮助
• 没帮助
• 一般
• 有帮助
• 非常有帮助

¥2 ¥4 ¥6 ¥10 ¥20

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、C币套餐、付费专栏及课程。