【SQL高频练习带刷】day11：查询操作

题目一：寻找今年有正收入的客户

题目要求：

编写一个解决方案来报告 2021 年具有 正收入 的客户。

可以以 任意顺序 返回结果表。

1821. 寻找今年具有正收入的客户 - 力扣（LeetCode）

表结构：

运行结果示例：

思路：

        非常基础的查询练习，只需要限定年份和收入两个查询条件即可。

运行代码示例：

SELECT customer_id
FROM Customers
where year = '2021' and revenue > 0

题目二：从不订购客户

题目要求：

找出所有从不点任何东西的顾客。

以 任意顺序 返回结果表。

183. 从不订购的客户 - 力扣（LeetCode）

表结构：

运行结果示例：

思路：

        使用子查询寻找在订单表中没有出现的客户id。

运行代码示例：

select name as Customers
from Customers
where id not in (
    select customerId from Orders
)

题目三：计算特殊奖金

题目要求：

编写解决方案，计算每个雇员的奖金。如果一个雇员的 id 是 奇数 并且他的名字不是以 'M' 开头，那么他的奖金是他工资的 100% ，否则奖金为 0 。

返回的结果按照 employee_id 排序。

1873. 计算特殊奖金 - 力扣（LeetCode）

表结构：

运行结果示例：

思路：

        本题因为涉及到了字符串的模糊匹配以及条件判断，因此解法非常之多。简单举例几种，例如对于名字的筛选判断，我们可以使用正则表达式name regexp '^[^M]'进行匹配，也可以使用模糊匹配关键字like判断name like "M%"，也可以使用字符串截取函数left（）或者substr（）截取出首位字母，使用!=或者<>通配符进行比较。条件判断语句则可以使用if..else...或者case when或者union进行实现。

运行代码示例：

select employee_id,salary as bonus
from Employees
where employee_id % 2 = 1 and name not like "M%"
union 
select employee_id,0 as bonus
from Employees
where employee_id % 2 = 0 or name like "M%"
order by employee_id

select
    employee_id,
    if(
        employee_id&1 and name regexp '^[^M]',
        salary,
        0
    ) as bonus
from employees
order by employee_id;

select
    employee_id,
    salary * (
        employee_id&1 and substr(name, 1, 1)<>'M'
    ) as bonus
from employees
order by employee_id;

select
    employee_id,
    salary * (
        employee_id&1 and left(name, 1)<>'M'
    ) as bonus
from employees
order by employee_id;

select
    employee_id,
    if(
        employee_id&1 and left(name, 1)<>'M',
        salary,
        0
    ) as bonus
from employees
order by employee_id;
。

select
    employee_id,
    if(
        mod(employee_id, 2)=1 and name not like 'M%',
        salary,
        0
    ) as bonus
from employees
order by employee_id;

select
    employee_id,
    if(
        employee_id%2=1 and name not like 'M%',
        salary,
        0
    ) as bonus
from employees
order by employee_id;

select
    employee_id,
    if(
        employee_id&1 and name not like 'M%',
        salary,
        0
    ) as bonus
from employees
order by employee_id;

题目四：购买了产品A和产品B但是没有购买产品C的用户

题目要求：

请你编写解决方案，报告购买了产品 "A"，"B" 但没有购买产品 "C" 的客户的 customer_id 和 customer_name，因为我们想推荐他们购买这样的产品。

返回按 customer_id 排序 的结果表。

1398. 购买了产品 A 和产品 B 却没有购买产品 C 的顾客 - 力扣（LeetCode）

表结构：

运行结果示例：

思路：

        这道题目我看到的第一想法就是子查询，只不过由于本题涉及到了三个子查询，所以代码量上看非常啰嗦。看了下其他人的题解发现还可以用having子句进行判断。我们可以根据用户id进行分组统计，统计每组记录中产品A、B、C的数量，如果数量不为0说明购买过，为0说明没有购买。

运行代码示例：

select customer_id,customer_name 
from Customers
where customer_id in (select customer_id from Orders where product_name = 'A')
and customer_id in (select customer_id from Orders where product_name = 'B')
and customer_id not in (select customer_id from Orders where product_name = 'C')

# Write your MySQL query statement below


SELECT o.customer_id customer_id,
        c.customer_name customer_name
FROM Orders o
LEFT JOIN Customers c  USING(customer_id)
GROUP BY customer_id
HAVING 
        SUM(IF(product_name='A',1,0))>0 AND
        SUM(IF(product_name='B',1,0))>0 AND
        SUM(IF(product_name='C',1,0))=0

题目五：每位学生的最高成绩

题目要求：

编写解决方案，找出每位学生获得的最高成绩和它所对应的科目，若科目成绩并列，取 course_id 最小的一门。查询结果需按 student_id 增序进行排序。

1112. 每位学生的最高成绩 - 力扣（LeetCode）

表结构：

运行结果示例：

思路：

        这道题目我居然卡在了如何在存在并列值时输出课程号更小的数据上，因为感觉题目本身并不难，着实不想使用窗口函数去做。最后万不得已求助了题解发现...原来只要使用一个min（）函数就能搞定，果然有时候题刷多了就会形成思维定式，很多讨巧的方法反而想不出来。

        需要注意的是本题虽然有并列情况下输出课程号较小的数据这个限定条件，导致我们不得不在分组聚合外面又套了一个分组聚合。即使没有这个条件的限制我们也不能直接一步到位进行查询，而是也需要使用子查询的方式。因为我们在使用group by子句的时候，select语句中查询的内容只能是和分组字段相关的字段或者分组字段本身，如果我们去查询其他字段（比如这里的课程号），那么会随机返回一个课程号，大概率是本组第一条数据对应的值，而非我们希望的与学号和成绩对应的值。

    select distinct student_id,min(course_id) as course_id,max(grade) as grade
    from Enrollments
    group by student_id
    order by student_id

运行代码示例：

select distinct student_id,min(course_id) as course_id,grade as grade 
from Enrollments
where (student_id,grade) in (
    select distinct student_id,max(grade) as grade
    from Enrollments
    group by student_id
)
group by student_id,grade
order by student_id