sql语句练习1

1821. 寻找今年具有正收入的客户

编写一个解决方案来报告 2021 年具有 正收入 的客户。
可以以 任意顺序 返回结果表。

select customer_id  from Customers 
       where  year = 2021 and revenue > 0;
select customer_id  from Customers
       where  revenue > 0 and year = 2021;

两种写法有得到是runtime不一样

183. 从不订购的客户

找出所有从不点任何东西的顾客。
以 任意顺序 返回结果表。

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

select c.name as Customers
from Customers as c
left join Orders as o
    on c.id = o.customerId
where o.id is null

SELECT name AS Customers
FROM Customers
WHERE
    NOT EXISTS(
        SELECT 1
        FROM Orders
        WHERE Customers.id = Orders.customerId
    )

这里runtime也有差异，都是下面的好于上面的。

1873. 计算特殊奖金

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

select employee_id,  IF(employee_id % 2 = 1 AND substr(name,1,1) <> 'M', salary, 0) AS bonus  
from Employees order by employee_id;

select employee_id, 
 IF(employee_id % 2 = 1 AND name NOT REGEXP '^M', salary, 0) AS bonus  
from Employees order by employee_id;

mysql 里的不等于 <>、!=

substr（列名，position，length），position 从1开始

1398. 购买了产品 A 和产品 B 却没有购买产品 C 的顾客

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

返回按 customer_id 排序 的结果表。

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' )
   order by customer_id ;

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

1407. 排名靠前的旅行者

返回的结果表单，以 travelled_distance 降序排列 ，如果有两个或者更多的用户旅行了相同的距离, 那么再以 name 升序排列 。

select name,if(distance is null,0,distance) travelled_distance 
from Users left join 
(select user_id,sum(distance) as distance from Rides group by user_id) r 
on Users.id = r.user_id
order by travelled_distance desc,name asc;

select name ,ifnull(sum(Rides.distance),0)travelled_distance  from 
Users left join  Rides 
on Users.id = Rides.user_id  
group by user_id  
order by travelled_distance desc,name asc;

607. 销售员

编写解决方案，找出没有任何与名为 “RED” 的公司相关的订单的所有销售人员的姓名。

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