Question

我有一个数据库,有两个表格用户和订单。

users: user_id(primary), fname, lname, email, password, gender

orders: order_no(primary), user_id(foriegn), beans, type, extras, city

订单表只有提交订单的用户。

我需要知道,如果所有用户有定购单或无定购单,如何选择其订单。

Additional Info posted as an answer....

users table:

user_id fname lname email password gender
1        a     aa    aaa  123      m
2        b     bb    bbb  34       f

orders table:

order_no user_id bean type extras city
1         2       s    d    rr     ggg
2         2       s    d     rr     ggg

如何选择所有用户表栏,加上对a和b的定购单,因此,新表格将:

user_id fname lname email password gender orders_count
1       a      aa    aaa   123      m      0
2       b      bb    bbb   34       f      2

Answer 1

   select U.user_id,
          COUNT(O.user_id)
     from users U 
left join orders O on U.user_id=O.user_id
 group by U.user_id

Answer 2

根据最新资料,使用:

   SELECT u.*,
          COALESCE(x.orders_count, 0) AS orders_count
     FROM USERS u
LEFT JOIN (SELECT o.user_id, 
                  COUNT(*) AS orders_count
             FROM ORDERS o
         GROUP BY o.user_id) x ON x.user_id = u.user_id
 ORDER BY u.user_id

Answer 3

您的更新基本上要求以下网站:https://stackoverflow.com/questions/5533033/select- from-second-tables-using-subquery/5533065#5533065>pcofre s accreditation/a>。你只需在<条码>电子数据清单中添加必要的栏目,并提供合计数的栏目。

SELECT U.user_id,
       U.fname,
       U.lname,
       U.email,
       U.password,
       U.gender,
       COUNT(O.user_id) AS orders_count
FROM   users U
       LEFT JOIN orders O
         ON U.user_id = O.user_id
GROUP  BY U.user_id  /*<-- Don t need to add other users 
                          columns to GROUP BY in MySQL*/

Additional Info posted as an answer....

users table:

orders table:

友情链接