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用法