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.组合两个表
#左外联结
select firstname,lastname,city,state from Person #没有address
left outer join Address on Person.personId=Address.personId
第586题.订单最多的客户
编写一个SQL查询,为下了 最多订单 的客户查找 customer_number 。
测试用例生成后, 恰好有一个客户 比任何其他客户下了更多的订单。
查询结果格式如下所示。
#写法一
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.上升的温度
627.变更性别
UPDATE salary
SET
sex = CASE sex
WHEN 'm' THEN 'f'
ELSE 'm'
END;
1179. 重新格式化部门表
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.
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. 排名靠前的旅行者
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
select name,sum(amount) as balance
from Users
left join Transactions on Users.account =Transactions.account
group by Transactions.account #当两个表有列名一样的时候需指定
having sum(amount)>=10000