1. 购买了产品 A 和 B 但没有买产品 C 的顾客
题目描述:
`Customers`
表:
+---------------------+---------+ | Column Name | Type | +---------------------+---------+ | customer_id | int | | customer_name | varchar | +---------------------+---------+ customer_id 是这张表中具有唯一值的列。 customer_name 是顾客的名称。
`Orders`
表:
+---------------+---------+ | Column Name | Type | +---------------+---------+ | order_id | int | | customer_id | int | | product_name | varchar | +---------------+---------+ order_id 是这张表中具有唯一值的列。 customer_id 是购买了名为 "product_name" 产品顾客的id。
请你编写解决方案,报告购买了产品 "A","B" 但没有购买产品 "C" 的客户的 customer_id 和 customer_name,因为我们想推荐他们购买这样的产品。
返回按 customer_id
排序 的结果表。
返回结果格式如下所示。
示例 1:
输入: Customers table: +-------------+---------------+ | customer_id | customer_name | +-------------+---------------+ | 1 | Daniel | | 2 | Diana | | 3 | Elizabeth | | 4 | Jhon | +-------------+---------------+ Orders table: +------------+--------------+---------------+ | order_id | customer_id | product_name | +------------+--------------+---------------+ | 10 | 1 | A | | 20 | 1 | B | | 30 | 1 | D | | 40 | 1 | C | | 50 | 2 | A | | 60 | 3 | A | | 70 | 3 | B | | 80 | 3 | D | | 90 | 4 | C | +------------+--------------+---------------+ 输出: +-------------+---------------+ | customer_id | customer_name | +-------------+---------------+ | 3 | Elizabeth | +-------------+---------------+ 解释: 只有 customer_id 为 3 的顾客购买了产品 A 和产品 B ,却没有购买产品 C 。
解答:
LEFT JOIN + GROUP BY + SUM:根据 customer_id 将 customers 表与 orders 表左连接,然后根据 customer_id 分组,最后对 product_name 的值求和,要求A>0,B>0且C=0。
SELECT c.customer_id, c.customer_name
FROM Customers c LEFT JOIN Orders o
ON c.customer_id = o.customer_id
GROUP BY c.customer_id
HAVING
SUM(o.product_name='A')>0 AND
SUM(o.product_name='B')>0 AND
SUM(o.product_name='C')=0;
2. 每位学生最高成绩
题目描述:
表:`Enrollments`
+---------------+---------+ | Column Name | Type | +---------------+---------+ | student_id | int | | course_id | int | | grade | int | +---------------+---------+ (student_id, course_id) 是该表的主键(具有唯一值的列的组合)。 grade 不会为 NULL。
编写解决方案,找出每位学生获得的最高成绩和它所对应的科目,若科目成绩并列,取 course_id
最小的一门。查询结果需按 student_id
增序进行排序。
以 任意顺序 返回结果表。
查询结果格式如下所示。
示例 1:
输入: Enrollments 表: +------------+-------------------+ | student_id | course_id | grade | +------------+-----------+-------+ | 2 | 2 | 95 | | 2 | 3 | 95 | | 1 | 1 | 90 | | 1 | 2 | 99 | | 3 | 1 | 80 | | 3 | 2 | 75 | | 3 | 3 | 82 | +------------+-----------+-------+ 输出: +------------+-------------------+ | student_id | course_id | grade | +------------+-----------+-------+ | 1 | 2 | 99 | | 2 | 2 | 95 | | 3 | 3 | 82 | +------------+-----------+-------+
解答:
使用窗口函数:根据student_id分组,grade降序,course_id升序,然后取第一个组合
SELECT student_id,course_id,grade
FROM
(SELECT *
,ROW_NUMBER()
OVER(PARTITION BY student_id ORDER BY grade DESC,course_id ASC)
AS rk From Enrollments) a
WHERE rk = 1
ORDER BY student_id;