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

#左外联结
select firstname,lastname,city,state from Person #没有address
left outer join Address on Person.personId=Address.personId

第586题.订单最多的客户

编写一个SQL查询，为下了 最多订单 的客户查找 customer_number 。

测试用例生成后， 恰好有一个客户 比任何其他客户下了更多的订单。

查询结果格式如下所示。

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 

197.上升的温度

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

巨慢，只打败了5%的人，就是锻炼casewhen 的用法

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.必须要用聚合函数的原因
当一个单元格中有多个数据时，case when只会提取当中的第一个数据。以CASE WHEN month=‘Feb’ THEN revenue END 为例，当id=1时，它只会提取month对应单元格里的第一个数据，即Jan，它不等于Feb，所以找不到Feb对应的revenue，所以返回NULL。（可以试试把我上面答案里的sum()统统去掉，执行结果与预期不一样。错就错在当id=1时,Feb_Revenue和Mar_Revenue的值变成了NULL）
要解决单元格内含多个数据的情况，就是使用聚合函数，聚合函数就用来输入多个数据，输出一个数据的。如SUM()或MAX()，而每个聚合函数的输入就是每一个多数据的单元格。

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是降序

遇到null值，sum可以直接输出null
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