代码之家 › 专栏 › 技术社区 › Louis W

计数有条件连接的行时出现问题

join mysql sql

Louis W · 技术社区 · 17 年前

我在MySQL和从联接表中选择列数时遇到了一些问题。我有一种感觉,这将需要一个子选择语句来获取我想加入的行,而不是在我的主条件中这样做。

我正在尝试选择一个链接到某个用户的项目列表。我还想包括分配给此项目和此用户的任务计数(但仅限于具有特定状态的任务)。

+-------------------------+
| projects_to_users       |
+-----------+-------------+
+ user_id   | project_id  |
+-----------+-------------+
+ 1         | 1           |
+-----------+-------------+
+ 1         | 2           |
+-----------+-------------+


+-------------------------+
| projects                |
+--------------+----------+
+ project_id   | name     |
+--------------+----------+
+ 1            | Foo      |
+--------------+----------+
+ 2            | Bar      |
+--------------+----------+

+------------------------------------------------+
| tasks                                          |
+-----------+--------------+----------+----------+
+ task_id   | project_id   | status   | name     |
+-----------+--------------+----------+----------+
+ 1         | 1            | 1        | Do it    |
+-----------+--------------+----------+----------+
+ 2         | 1            | 1        | Do itt   |
+-----------+--------------+----------+----------+
+ 3         | 1            | 9        | Do not   |
+-----------+--------------+----------+----------+



SELECT count( tasks.task_id ) AS task_count, projects.*
FROM (projects)
LEFT JOIN tasks ON tasks.project_id = projects.project_id
LEFT JOIN projects_to_users ON projects.project_id=projects_to_users.project_id
WHERE tasks.status<9
AND tasks.assigned_user_id = '1'
AND projects_to_users.user_id = '1'
GROUP BY projects.project_id


RETURNS:

+--------------+--------------+--------+
+ task_count   | project_id   | name   |
+--------------+--------------+--------+
+ 2            | 1            | Foo    |
+--------------+--------------+--------+

SHOULD RETURN:

+--------------+--------------+--------+
+ task_count   | project_id   | name   |
+--------------+--------------+--------+
+ 2            | 1            | Foo    |
+--------------+--------------+--------+
+ 0            | 2            | Bar    |
+--------------+--------------+--------+

2 回复 | 直到 17 年前

David Andres 17 年前

此外,您没有看到零计数项目的原因是,您的WHERE条件期望通过LEFT JOIN子句进行匹配。如果将WHERE条件移动到连接本身中,这应该对您更有效。

例如:

SELECT count( tasks.task_id ) AS task_count, 
       projects.*
FROM (projects)
LEFT JOIN tasks ON tasks.project_id = projects.project_id AND tasks.status < 9 AND tasks.assigned_user_id = '1'
LEFT JOIN projects_to_users ON projects.project_id=projects_to_users.project_id AND projects_to_users.user_id = '1'
GROUP BY projects.project_id

amischiefr 17 年前

为什么你不能像这样做一个简单的连接:

select count(*) from projects_to_users inner join tasks on projects_to_users.project_id=tasks.project_id;