1607 没有卖出的卖家
# 写法1
# Write your MySQL query statement below
select seller_name
from Seller s left join Orders o
on s.seller_id = o.seller_id and year(sale_date)=2020
where o.customer_id is null
order by seller_name
# 写法2
# Write your MySQL query statement below
select seller_name
from Seller s left join Orders o
on s.seller_id = o.seller_id
where s.seller_id not in (
select distinct seller_id
from Orders o
where sale_date like '2020%'
)
order by seller_name
总结
- 主要考察
left join
后on、and
和where
的区别。left join
后,on
是筛选右边表的记录,不影响左表。where
是对求积之后的表进行筛选。
1407 排名靠前的旅行者
# Write your MySQL query statement below
select name, ifNull(sum(distance),0) as travelled_distance
from Users u
left join Rides r
on u.id = r.user_id
group by u.id
order by travelled_distance desc, name
总结
ifNull
的使用:IFNULL(expression_1,expression_2)
;如果expression_1不是null,返回expression_1,如果为null,返回expression_2。
607 销售员
题目链接:607
select name
from SalesPerson
where sales_id not in (
select o.sales_id
from Orders o
left join Company c
on o.com_id = c.com_id
where name = 'RED'
)
总结
- 当使用join时,我们可能经常使用
left join
,但就效率而言left join
和inner join
的效率需要视情况而定。
1440 计算布尔表达式的值
题目链接:1440
# Write your MySQL query statement below
select e.*,
case
when operator = '=' and v1.value = v2.value then 'true'
when operator = '>' and v1.value > v2.value then 'true'
when operator = '<' and v1.value < v2.value then 'true'
else 'false'
end value
from Expressions e
left join Variables v1
on e.left_operand = v1.name
left join Variables v2
on e.right_operand = v2.name
总结
- case when的用法,详细见case when用法
1212 查询球队积分
# Write your MySQL query statement below
select team_id, team_name,
ifNUll(sum(case
when host_goals > guest_goals then if(team_id=host_team, 3, 0)
when host_goals = guest_goals then if(team_id=host_team, 1, 1)
when host_goals < guest_goals then if(team_id=host_team, 0, 3)
end),0) num_points
from Teams t
left join Matches m on
t.team_id=m.host_team or t.team_id=m.guest_team
group by team_id
order by num_points desc, team_id
总结
- case when的使用和if的使用注意掌握