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

在一个查询中选择2个计数

mysql

Louis W · 技术社区 · 16 年前

尝试在一个查询中获取两个不同的计数。为此,我从一个表中进行选择,并需要加入另一个表。查看下面的(不工作)查询。这些都是我尝试的不同方式——没有成功。

SELECT 
    count( tasks_assigned.task_id ) AS task_assigned_count,
    count( tasks_created.task_id ) AS task_created_count,
FROM projects
LEFT JOIN tasks AS tasks_assigned 
    ON tasks_assigned.project_id = projects.project_id
    AND tasks_assigned.assigned_user_id = 1
LEFT JOIN tasks AS tasks_created 
    ON tasks_created.project_id = projects.project_id
    AND tasks_created.created_user_id = 1
GROUP BY tasks_assigned.project_id, tasks_created.project_id


SELECT 
    projects.*
    , (SELECT count(task_id) as task_assigned_count FROM tasks as task_assigned WHERE task_assigned.project_id = projects.project_id AND assigned_user_id = 1 GROUP BY task_id) as task_assigned_count
    , (SELECT count(task_id) as task_created_count FROM tasks as task_created WHERE task_created.project_id = projects.project_id AND assigned_user_id = 1 GROUP BY task_id) as task_assigned_count
FROM projects

5 回复 | 直到 16 年前

Gordon Bell 16 年前

SELECT 
projects.*
, (SELECT count(*) FROM tasks WHERE project_id = projects.project_id AND assigned_user_id = 1) as task_assigned_count,
, (SELECT count(*) FROM tasks WHERE project_id = projects.project_id AND created_user_id = 1) as task_created_count
FROM projects

Steve Kass 16 年前

删除子查询中的分组:

SELECT 
      projects.*,
     (SELECT count(task_id) FROM tasks
      WHERE task_assigned.project_id = projects.project_id
      AND assigned_user_id = 1) as task_assigned_count,
...
FROM projects

Todd Gardner 16 年前

SELECT projects.project_id, assigned_count.count, created_count.count
FROM projects
LEFT JOIN (SELECT project_id,
                  count(*) AS count,
           FROM projects
           INNER JOIN tasks A 
                  ON tasks.project_id = projects.project_id
           WHERE tasks.assigned_user_id = 1
           GROUP BY projects.project_id) assigned_count
 ON assigned_count.project_id = projects.project_id
LEFT JOIN (SELECT project_id,
                  count(*) AS count,
           FROM projects
           INNER JOIN tasks A 
                  ON tasks.project_id = projects.project_id
           WHERE tasks.created_user_id = 1
           GROUP BY projects.project_id) created_count
 ON created_count.project_id = projects.project_id

或:

SELECT  projects.*,
        SUM( IF(tasks.assigned_user_id = 1,       1,0) ) AS task_assigned_count,
        SUM( IF(tasks_created.created_user_id = 1,1,0) ) AS task_created_count
FROM projects
LEFT JOIN tasks A 
        ON tasks.project_id = projects.project_id
GROUP BY projects.project_id

Bill Karwin 16 年前

我将通过两个查询来实现这一点:

SELECT p.project_id, COUNT( a.task_id ) AS task_assigned_count
FROM projects p
LEFT JOIN tasks AS a ON a.project_id = p.project_id
  AND a.assigned_user_id = 1
GROUP BY p.project_id;

SELECT p.project_id, COUNT( c.task_id ) AS task_created_count
FROM projects p
LEFT JOIN tasks AS c ON c.project_id = p.project_id
  AND a.created_user_id = 1
GROUP BY p.project_id;

否则,您将在两个实例之间创建笛卡尔积 tasks DISTINCT 但这只是对笛卡尔积的补偿,而不是回避它:

SELECT p.project_id, COUNT( DISTINCT a.task_id ) AS task_assigned_count,
    COUNT( DISTINCT c.task_id ) AS task_created_count
FROM projects p
LEFT JOIN tasks AS a ON a.project_id = p.project_id
  AND a.assigned_user_id = 1
LEFT JOIN tasks AS c ON c.project_id = p.project_id
  AND a.created_user_id = 1
GROUP BY p.project_id;

-1

atfergs 16 年前

SELECT (SELECT COUNT(task_id) FROM tasks_assigned) AS task_assigned_count, (SELECT COUNT(task_id) FROM tasks_created) AS task_created_count