如何使用 MySQL 子查询来计算外表中的行数？

发布于 2024-09-01 07:51:34 字数 288 浏览 3 评论 0原文

我有两个表，users 和 reports。每个用户都没有与其关联的一个或多个报告，并且 reports 表有一个 user_id 字段。

我有以下查询，我需要向每一行添加用户拥有多少个报告的计数：

SELECT *
FROM users
LIMIT 1, 10

我是否需要使用子查询，如果需要，如何有效地使用它？ reports 表有成千上万行。

原文

I have two tables, users and reports. Each user has no, one, or multiple reports associated with it, and the reports table has a user_id field.

I have the following query, and I need to add to each row a count of how many reports the user has:

SELECT *
FROM users
LIMIT 1, 10

Do I need to use a subquery, and if so, how can I use it efficently? The reports table has thousands and thousands of rows.

分享到QQ

分享到微博

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

发布评论

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

如此安好 2024-09-08 07:51:34

不需要子查询：

SELECT users.user_id, COUNT(reports.user_id) AS number_of_reports
FROM users
LEFT JOIN reports ON users.userid = reports.userid
GROUP BY users.user_id

为了使查询更高效，请确保两个表中的 user_id 字段都有索引注释

后续：COUNT 函数不计算空值，因此对于任何具有空值的用户，它将返回 0（如预期）根本没有报告（连接将为reports.user_id返回NULL）。还添加了 GROUP BY 位，第一次忘记了。

There's no need for a subquery:

SELECT users.user_id, COUNT(reports.user_id) AS number_of_reports
FROM users
LEFT JOIN reports ON users.userid = reports.userid
GROUP BY users.user_id

To make the query more efficient, make sure there's indexes on the user_id fields in both tables

comment followup: COUNT function does not count nulls, so it'll return 0 (as expected) for any users which have no reports at all (the join would return a NULL for reports.user_id). Also added the GROUP BY bit, forgot that the first time around.

回复收藏 0 原文

如果没有 2024-09-08 07:51:34

SELECT users.userid,
       SUM( IF( Reports.userid > 0, 1, 0 )) as TotRpts
   FROM 
       users LEFT JOIN reports
          ON users.userid = reports.userid;
   GROUP BY
       users.userid

您可能需要将 IF() 更改为

  IF( Reports.UserID is null, 0, 1 )

SELECT users.userid,
       SUM( IF( Reports.userid > 0, 1, 0 )) as TotRpts
   FROM 
       users LEFT JOIN reports
          ON users.userid = reports.userid;
   GROUP BY
       users.userid

you might need to change the IF() to