leetcode刷题 MySQL 2.28

select Email from Person group by Email having count(Email)>1;

2.寻找2021年具有正收益的客户

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

3.找出从不点东西的顾客

select name as Customers from Customers where not exit(select 1 from Orders Customers.id = Orders=id)

这个是一个子查询，先查出两张表id相同的，就是都点东西的顾客

4.计算每个雇员的奖金。如果一个雇员的 id 是 奇数 并且他的名字不是以 ‘M’ 开头，那么他的奖金是他工资的 100% ，否则奖金为 0

select employee_id,if( employee_id % 2 =1 and substr(name,1,1)!='M',salary,0) as bonus from Employees

不用想update，用if就可以了

5.请你编写解决方案，报告购买了产品 “A”，“B” 但没有购买产品 “C” 的客户的 customer_id 和 customer_name，因为我们想推荐他们购买这样的产品。
返回按 customer_id 排序 的结果表。
返回结果格式如下所示。

SELECT distinct c.customer_id, c.customer_name 
FROM Customers c
INNER JOIN Orders o ON o.customer_id = c.customer_id
WHERE c.customer_id NOT IN (SELECT customer_id FROM Orders WHERE product_name = 'C')
AND c.customer_id IN (SELECT customer_id FROM Orders WHERE product_name = 'A')
AND c.customer_id IN (SELECT customer_id FROM Orders WHERE product_name = 'B');

法2

法2：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 

我觉得这个就很绝，计算sum相乘数量不为0就可以看出AB不为0
6.

SELECT student_id, course_id, grade
FROM (
    SELECT 
        student_id,
        course_id,
        grade,
        ROW_NUMBER() OVER (PARTITION BY student_id ORDER BY grade DESC, course_id) AS  row_num
    FROM Enrollments
) AS ranked
WHERE row_num = 1
ORDER BY student_id;

这个是窗口函数row_number用法