如何通过计数限制 SQL LEFT JOIN 查询的结果集

Question

我希望使用LEFT JOIN查询从具有一对多关系的两个表中获取一些结果，但基于“子项”的计数限制结果集。我的两个表结构如下：

customers
id    name    ...
1     "bob"   ...
2     "jill"  ...

orders
id    customer_id    ...
100   1              ...
101   2              ...
102   1              ...

(表中其余数据对于此查询无关。)

我想要做的是获取所有客户ID和他们的订单ID，按客户排序，但限制只有下过多于一份订单的客户。在这个例子中，结果应如下所示:

cust_id    order_id
1          100
1          102

我从基本的LEFT JOIN开始，将订单ID与其客户配对，但无法确定如何排除所有未至少下过两次单的客户。

SELECT 
    `customers`.`id` AS `cust_id`,
    `orders`.`id` AS `order_id`
FROM
    `customers`
LEFT JOIN `orders` ON
    `customers`.`id` = `orders`.`customer_id`
ORDER BY
    `cust_id`

感谢大家。

- inspector-g

Answer 1

一种方法是创建一个内联视图，获取有多个订单的客户，然后进行内联接。如果您不喜欢JOIN，也可以使用IN或EXISTS。

SELECT 
    `customers`.`id` AS `cust_id`,
    `orders`.`id` AS `order_id`
FROM
    `customers`
LEFT JOIN `orders` ON
    `customers`.`id` = `orders`.`customer_id`
INNER JOIN 
      (SELECT `customer_id `    
       FROM `orders`
       GROUP BY `customer_id`    
       HAVING COUNT(id) > 1) as `morethanone`
On
    `customer`.`id`  = `morethanone`.`custmor_id`
ORDER BY
    `cust_id`

- Conrad Frix

---

在我说出答案之前就抢先回答了！ - Andrew Willis

谢谢！这似乎给了我想要的结果集。 - inspector-g

很高兴我能帮到你，但你应该注意Marlin Pierce是正确的，因为我们已经排除了任何没有订单的客户，所以LEFT JOIN实际上并没有起到什么作用。 - Conrad Frix

Answer 2

首先，您不需要将连接设置为左外连接，因为与内连接的唯一区别是包括没有订单的客户，而您不需要在结果中包括他们，因为他们少于两个订单。

SELECT customers.id AS `cust_id`, orders.id AS `order_id`
FROM customers
     INNER JOIN orders ON `customers`.`id` = `orders`.`customer_id`
     INNER JOIN orders count_orders ON customers.id = count_orders.customer_id
GROUP BY count_orders.id, orders.id
HAVING count(count_orders.id) >= 2
ORDER BY `cust_id`

- Marlin Pierce

---

这个接近了... Conrad Frix的答案似乎给出了正确的结果集，而你的也给出了相同数量的行；然而，在每个客户ID下面的订单ID是重复的。例如，它给出的是"1, 100; 1, 100"，而不是"1, 100; 1, 102"（扩展我问题中的例子）。 - inspector-g

SQL 确实需要不断尝试和调整，这就是随口给出 SQL 答案的问题所在。 - Marlin Pierce

+1 有趣的解决方案。我从未见过有人通过创建一个笛卡尔平方来解决这个问题，使您可以按相同字段计数和分组。 - Conrad Frix

在FROM子句中多次加入表格是一种常见的SQL技术。 - Marlin Pierce

@MarlinPierce 嗯，加入一个表多次连接没错。这样做是为了创建笛卡尔积，以便您可以进行分组和计数...我记不清是否看到过（而且这个特定问题的变体经常出现）。 - Conrad Frix

Answer 3

嘿，我可能有点慢，但我还是会发表我的答案。它略有不同，但仍然有效。

SELECT `customers`.`id` AS `cust_id` , count( orders.id ) AS count_orders
FROM `customers`
JOIN `orders` ON `customers`.`id` = `orders`.`customer_id`
GROUP BY customers.id
HAVING count( orders.id ) >1
ORDER BY `cust_id`

- effulge

Answer 4

如果你排除那些没有下单的顾客，那么左连接就不必要了，对吧？

由于你想在结果集中获取订单ID，因此计数查询需要先运行。

在SQL Server 2005+中，CTE将为计数查询提供解决方案，然后可以基于该计数查询生成所需的结果集：

WITH customerWithMoreThanOneOrder AS
(
    SELECT 
        `customers`.`id` AS `cust_id`
    FROM
        `customers`
    INNER JOIN `orders` ON
        `customers`.`id` = `orders`.`customer_id`
    GROUP BY 
        `customers`.`id`
    HAVING COUNT(0) > 1
)

SELECT 
    `customers`.`id` AS `cust_id`,
    `orders`.`id` AS `order_id`
FROM
    `customers`
INNER JOIN `orders` ON
    `customers`.`id` = `orders`.`customer_id`
WHERE `customers`.`id` IN (SELECT cust_id FROM customerWithMoreThanOneOrder)
ORDER BY
    `cust_id`

如果您使用的数据库不支持此结构，则只需将计数查询放置在IN子查询的括号中，如下所示：

SELECT 
    `customers`.`id` AS `cust_id`,
    `orders`.`id` AS `order_id`
FROM
    `customers`
INNER JOIN `orders` ON
    `customers`.`id` = `orders`.`customer_id`
WHERE `customers`.`id` IN (SELECT 
                               `customers`.`id` AS `cust_id`
                           FROM
                               `customers`
                           INNER JOIN `orders` ON
                               `customers`.`id` = `orders`.`customer_id`
                           GROUP BY 
                               `customers`.`id`
                           HAVING COUNT(0) > 1)
ORDER BY
    `cust_id`

如果您有任何问题，请告诉我

- jimmym715

---

这是针对MySQL的，所以我无法评论第一个建议是否可行。然而，第二个建议似乎会将mysqld进程发送到一个无限循环中。查询对我来说似乎是合理的，因此可能是MySQL本身的问题。他们的发布版本最近变得越来越有bug了... - inspector-g

Answer 5

除了其他答案，你可以使用IN子句作为替代方案。

SELECT 
    `customers`.`id` AS `cust_id`,
    `orders`.`id` AS `order_id`
FROM
    `customers`
LEFT JOIN `orders` ON
    `customers`.`id` = `orders`.`customer_id`
WHERE `customer_id` IN (SELECT `customer_id` FROM `orders` GROUP BY `customer_id` HAVING COUNT(*) > 1)
ORDER BY
    `cust_id`

- Jack Hughes