【MySQL基础刷题】总结题型（二）

1.至少有5名直接下属的经理

注意左连接的使用

select e1.name
from Employee e1 left join Employee e2 on e1.id = e2.managerId
where e2.managerId is not null
group by e1.id
having count(*) >= 5;

2.销售员

有三张表，销售人员表，公司表和订单表



找出没有任何与名为 “RED” 的公司相关的订单的所有销售人员的姓名。

select name
from SalesPerson
where sales_id not in
(
    select sales_id from Orders where com_id in
    (
        select com_id from Company where name = 'RED'
    )
)

3.订单最多的客户

查找下了 最多订单 的客户的 customer_number 。

select customer_number
from Orders 
group by customer_number
order by count(customer_number) desc
limit 1

4.计算布尔表达式的值

给一张variables表和一张expressions表

最后使用variables表找到expressions表中的每一个布尔表达式的值

在这里，一张表要链接两次，都用expressions 与variable连接两次，且都用left join，分别与x，y匹配
然后用case when语句判断 Result中的value

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
) as value
from Expressions e 
left join Variables v1 on e.left_operand = v1.name
left join Variables v2 on e.right_operand = v2.name

5.查询球队积分

规则如下：

• 如果球队赢了比赛(即比对手进更多的球)，就得 3 分。
• 如果双方打成平手(即，与对方得分相同)，则得 1 分。
• 如果球队输掉了比赛(例如，比对手少进球)，就 不得分 。

最后输出球队积分

居然不需要连接…

select t.team_id, t.team_name, ifnull(sum(
    case
        when m.host_goals > m.guest_goals and m.host_team = t.team_id then 3
        when m.host_goals = m.guest_goals and m.host_team = t.team_id then 1
        when m.host_goals < m.guest_goals and m.host_team = t.team_id then 0

        when m.host_goals > m.guest_goals and m.guest_team = t.team_id then 0
        when m.host_goals = m.guest_goals and m.guest_team = t.team_id then 1
        when m.host_goals < m.guest_goals and m.guest_team = t.team_id then 3
    end
), 0) as num_points
from Teams t, Matches m 
group by t.team_id
order by num_points desc, t.team_id asc

6.苹果和桔子

问每一条苹果和桔子销售数量的差值

很常见但也很巧的思路…

select sale_date, sum(case when fruit='apples' then sold_num else -sold_num end) as diff
from Sales
group by sale_date
order by sale_date

7.两人之间的通话次数

题目要求输出person1 < person2

select
    if(from_id < to_id, from_id, to_id) person1,
    if(from_id > to_id, from_id, to_id) person2,
    count(*) as call_count,
    sum(duration) as total_duration 
from Calls
group by person1, person2

8.确认率

用户的 确认率 是 ‘confirmed’ 消息的数量除以请求的确认消息的总数。没有请求任何确认消息的用户的确认率为 0 。确认率四舍五入到 小数点后两位 。

输出

求比率的问题可以用avg（if）来解决
avg 函数计算所有输入值的平均值。当输入由1和0组成时，平均值实际上反映了1的值所占的比例，即条件为真的比例。

select s.user_id, round(avg(if(c.action = 'confirmed',1,0)),2) as confirmation_rate
from Signups s left join Confirmations c on s.user_id = c.user_id
group by s.user_id

9.各赛事的用户注册率

编写解决方案统计出各赛事的用户注册百分率，保留两位小数。

返回的结果表按 percentage 的 降序 排序，若相同则按 contest_id 的 升序 排序。


查询嵌套子查询，新颖

select contest_id, round(count(user_id) * 100 / (select count(*) from Users), 2) as percentage
from Register
group by contest_id
order by percentage desc, contest_id asc;