Sqlite - 使用 GROUP BY 选择 DISTINCT

Question

我有一个 SQLite 表，我想在其中过滤购买了 2 个或更多 DISTINCT 项目的客户。该表如下所示:

customer  | product | quantity | date of purchase
---------------------------------------------
John      | coffee  |    1     |    02.20.2019
Bob       | coffee  |    2     |    01.13.2018
Chris     | milk    |    1     |    02.03.2012
Alice     | bread   |    1     |    05.01.2019
John      | banana  |    2     |    08.04.2015
Mary      | orange  |    3     |    12.16.2018
Mary      | orange  |    2     |    04.21.2019
Chris     | pie     |    1     |    07.02.2004

我设法通过使用 GROUP BY 查询(客户和产品)中的两行对客户进行分组来选择客户:

SELECT * FROM test_table GROUP BY customer, product

这(几乎)给了我最初想要的所有客户，除了它还包括 Mary，这不是所需的输出(她没有购买两种不同的产品)。我尝试使用 COUNT DISTINCT 函数只显示购买了两种或更多不同产品的客户，如下所示:

SELECT customer, product, COUNT(DISTINCT(product)) AS counter FROM test_table
GROUP BY customer, product HAVING counter>1

该查询成功返回了 Chris 和 John 而不带 Mary，但是它只输出一次，只显示他们购买的产品之一。有什么方法可以让我让 Chris 和 John 购买他们购买的所有不同产品？查询的预期输出:

John      | coffee  |    1     |    02.20.2019
John      | banana  |    2     |    08.04.2015
Chris     | milk    |    1     |    02.03.2012
Chris     | pie     |    1     |    07.02.2004

Answer 1

使用聚合:

SELECT *
FROM test_table
WHERE customer IN (SELECT customer
                   FROM test_table
                   GROUP BY customer
                   HAVING MIN(product) <> MAX(product));

HAVING上面的条款断言“最小”和“最大”产品是不同的，这意味着每个匹配的客户至少有两种不同的产品。
对于更复杂的解决方案，我们可以在这里使用分析函数:

WITH cte AS (
    SELECT *, MIN(product) OVER (PARTITION BY customer) AS min_product,
              MAX(product) OVER (PARTITION BY customer) AS max_product
    FROM test_table
)

SELECT customer, product, quantity, date_of_purchase
FROM cte
WHERE min_product <> max_product;