mysql count函数将两列的结果相乘(应该分别返回每列的计数)

jpfvwuh4 于 2021-06-20 发布在 Mysql

关注(0)|答案(3)|浏览(394)

我有一个用户表、一个任务表和一个提醒表。我想返回每个用户的任务数和提醒数。当我只计算一个或另一个（提醒或任务）时，我可以让它工作，但当我在一个查询中同时计算这两个时，它们出于某种原因会互相相乘。
sqlfiddle:http://www.sqlfiddle.com/#!9/f0d6696/1/0
这是我目前的疑问：

SELECT
  users.name,
  COUNT(reminders.id),
  COUNT(tasks.id)
FROM users
  LEFT JOIN reminders on users.id = reminders.id
  LEFT JOIN tasks on users.id = tasks.id
GROUP BY users.id

我的用户表如下所示：

+---------------------------------------+
| ID | Name       | Email               |
+---------------------------------------+
| 1  | John Smith | jsmith@email.com    |
| 2  | Mark Twain | mtwain@books.com    |
| 3  | Elon Musk  | space-dude@email.com|
+---------------------------------------+

我的任务表如下所示：

+------------------------------------------------+
| ID | Title       | Text            | Status    |
+------------------------------------------------+
| 1  | Dishes      | Kitchen = nasty | incomplete|
| 1  | Library     | drop off books  | complete  |
| 3  | Gym         | get swole dude  | incomplete|
+------------------------------------------------+

我的提醒表如下所示：

+------------------------------------+
| ID | Title       | Text            | 
+------------------------------------+
| 1  | Dishes      | Kitchen = nasty |
| 2  | Library     | drop off books  |
| 1  | Gym         | get swole dude  |
+------------------------------------+

我希望从上述查询中得到以下结果：

+-------------------------------------------+
| Name             | Tasks   | Reminders    |
+-------------------------------------------+
| John Smith       |    2    |      2       |
| Mark Twain       |    1    |      0       |
| Elon Musk        |    0    |      1       |
+-------------------------------------------+

我实际上得到了以下信息：

+-------------------------------------------+
| Name             | Tasks   | Reminders    |
+-------------------------------------------+
| John Smith       |    4    |      4       |   <---2 tasks x 2 reminders?
| Mark Twain       |    1    |      0       |
| Elon Musk        |    0    |      1       |
+-------------------------------------------+

sql mysql Join count Database

来源：https://stackoverflow.com/questions/51888523/mysql-count-function-multiplying-results-of-two-columns-should-be-returning-cou

3条答案

按热度按时间

huwehgph1#

尝试以下查询
选择id、电子邮件、， name ，（选择count（id）from reminders r where r.id=u.id）as reminder，（选择count（id）from tasks t where t.id=u.id）as task from users u

赞(0）回复(0）举报 2021-06-20

jfewjypa2#

使用不同的内部计数尝试以下操作：http://www.sqlfiddle.com/#!2012年9月6日

SELECT
  users.name,

  count(distinct reminders.title) as rtitle,

  count(distinct tasks.title) as ttitle
FROM users
  LEFT JOIN reminders on users.id = reminders.id
  LEFT JOIN tasks on users.id = tasks.id
  group by users.name

赞(0）回复(0）举报 2021-06-20

lf3rwulv3#

你得到了一个交叉连接，每个任务的每一个提醒。
尝试

select 
users.name,
remindercount,
taskcount
FROM users
LEFT JOIN (select id, count(*) as remindercount from reminders group by id) reminders on users.id = reminders.id
LEFT JOIN (select id, count(*) as taskcount from tasks group by id) tasks on users.id = tasks.id

赞(0）回复(0）举报 2021-06-20

我来回答

mysql count函数将两列的结果相乘(应该分别返回每列的计数)

3条答案

相关问题

热门标签

最新问答