mysql - 如何计算拥有超过 1 个订单的用户？

Question

我正在使用 MySQL 数据库，我需要查询数据库并找出拥有多个订单的用户。我尝试使用 COUNT() 但无法正确使用。你能解释一下正确的方法吗？

这是我的表格:

• 用户

  +-------------+----------+------------------+------------+
  | userID      | fName    | email            | phone      |
  +-------------+----------+------------------+------------+
  | adele012    | Adele    | aash@gmail.com   | 0123948498 |
  | ana022      | Anna     | ashow@gmail.com  | 0228374847 |
  | david2012   | David    | north@gmail.com  | 902849302  |
  | jefAlan     | Jeffery  | jefal@gmail.com  | 0338473837 |
  | josquein    | Joseph   | jquein@gmail,com | 0098374678 |
  | jweiz       | John     | jwei@gmail.com   | 3294783784 |
  | jwick123    | John     | jwik@gmail.com   | 0998398390 |
  | kenwipp     | Kenneth  | kwip@gmail.com   | 0112938394 |
  | mathCler    | Maththew | matc@gmail.com   | 0238927483 |
  | natalij2012 | Natalie  | nj@gmail.com     | 1129093210 |
  +-------------+----------+------------------+------------+
  

• 订单

  +---------+------------+-------------+-------------+
  | orderID | date       | User_userID | orderStatus |
  +---------+------------+-------------+-------------+
  |       1 | 2012-01-10 | david2012   | Delivered   |
  |       2 | 2012-01-15 | jweiz       | Delivered   |
  |       3 | 2013-08-15 | david2012   | Delivered   |
  |       4 | 2013-03-15 | natalij2012 | Delivered   |
  |       5 | 2014-03-04 | josquein    | Delivered   |
  |       6 | 2014-01-15 | jweiz       | Delivered   |
  |       7 | 2014-02-15 | josquein    | Delivered   |
  |       8 | 2015-10-12 | jwick123    | Delivered   |
  |       9 | 2015-02-20 | ana022      | Delivered   |
  |      10 | 2015-11-20 | kenwipp     | Processed   |
  +---------+------------+-------------+-------------+
  

Answer 1

select user_userID, count(*) as orders_count from orders 
group by user_userID having orders_count > 1 

如果你想从你的用户表中获取额外的数据，你可以这样做:

select * from user where user_id in (
  select user_userID as orders_count from orders 
  group by user_userID having orders_count > 1
)