使用子查询从两个表中选择

发布于 2024-10-30 01:01:05 字数 795 浏览 0 评论 0原文

我有一个包含两个表用户和订单的数据库

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

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

订单表仅包含提交订单的用户。

我需要知道如何选择所有用户及其订单数量（如果他们有订单或没有订单）。

作为答案发布的附加信息...

用户表：

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

订单表：

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

原文

I have a database with two tables users and orders

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

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

The orders table have only users who submitted orders.

I need to know how to select all users with the count of their orders if they have orders or don't.

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

how to select all users table columns plus orders count for a and b so the new table will be:

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

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

我一向站在原地 2024-11-06 01:01:05

   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

   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

回复收藏 0 原文

欢你一世 2024-11-06 01:01:05

根据更新的信息，使用：

   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

Based on the updated information, use:

   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

回复收藏 0 原文

小红帽 2024-11-06 01:01:05

您的更新基本上是在请求 pcofre 的答案已经为您提供的内容。您只需将其他必需的列添加到您的选择列表中，并为聚合提供一个列别名。

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*/

Your update is basically requesting what pcofre's answer already gives you. You just need to add the additional required columns to your select list and provide a column alias for the aggregate.

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*/

回复收藏 0 原文

~没有更多了~