mysql - 查询Mysql数据库

Question

我有一个名为 Order 的表

-______________________
|Order_id | customer_id |
|_________|_____________|
|     1   |    a        |
|     2   |    b        |
|     3   |    c        |
|     4   |    a        |
|_________|_____________|

我需要一个查询来查找包含多个订单列表的 Customer_id，并将这些内容存储在一个新表中

Answer 1

Find the Customer_id which contains the list of more than one orders

所以只有 COUNT() 或任何给定 customer_id 的行？像这样:

SELECT COUNT(order_id) AS order_count, customer_id FROM test GROUP BY customer_id

这将返回:

order_count    customer_id
--------------------------
2              a
1              b
1              c

如果您想将结果进一步缩减为仅包含多个订单的结果，只需对其进行过滤即可。我的 SQL 有点生疏，但这应该可以解决问题:

SELECT * FROM
    (SELECT COUNT(order_id) AS order_count, customer_id FROM order GROUP BY customer_id) AS order_counts
WHERE order_count > 1

这会产生:

order_count    customer_id
--------------------------
2              a