mysql - 在正确的记录行显示 COUNT() 总数

Question

1。问题陈述

我很难在正确的记录行中显示 COUNT() 的总数。

如何将 COUNT() 显示到正确的相应服务

2。背景

我想根据stage_id 和分解到project_name 显示员工负责的项目总数的输出。

我尝试过/尝试过的

SELECT
    B.employee_name,
    B.project_name,
    A.total 
FROM
    (
    SELECT
        COUNT( * ) AS total,
        services.NAME AS project_name,
        services.id AS service_id,
        users.id AS member_id,
        users.NAME AS member_name 
    FROM
        users
        LEFT JOIN projects ON users.id = projects.leader_id
        LEFT JOIN services ON projects.service_id = services.id 
    WHERE
        ( projects.service_id IN ( 1, 5 ) AND projects.stage_id < 6 ) 
        OR ( projects.service_id IN ( 2, 3, 4, 7 ) AND projects.stage_id < 7 ) 
    GROUP BY
        member_name,
        service_id 
    ) AS A
    RIGHT OUTER JOIN (
    SELECT
        users.id AS user_id,
        users.NAME AS employee_name,
        services.NAME AS project_name,
        services.id AS service_id 
    FROM
        users,
    services 
    ) AS B ON A.service_id = B.user_id RIGHT OUTER JOIN ( SELECT users.id AS user_id, users.NAME AS user_name, services.NAME AS project_name, services.id AS service_id FROM users, services ) AS B ON A.service_id = B.user_id

我使用这个查询生成下面的输出

+-------+-------------------+------------------+-------------------+
|   id  |   project_name    |   employee_name  |       total       |
+-------+-------------------+------------------+-------------------+
|   1   |   Projects A      |   Employee A     |         2         |
|   2   |   Projects B      |   Employee A     |         2         |
|   3   |   Projects A      |   Employee B     |         3         |
|   4   |   Projects B      |   Employee B     |         3         |
|   5   |   Projects A      |   Employee C     |         1         |
|   6   |   Projects B      |   Employee C     |         1         |
|   7   |   Projects A      |   Employee D     |         2         |
|   8   |   Projects B      |   Employee D     |         2         |
+-------+-------------------+------------------+-------------------+

计数总数分配到错误的记录行中。

3。 SQL fiddle

这是我在 SQL Fiddle 上创建的输出示例。 http://www.sqlfiddle.com/#!9/08eff4/1/0

4。预期产出

我期望可以将总计数分配给正确的记录行。

输出期望

+-------+-------------------+------------------+-------------------+
|   id  |   project_name    |   employee_name  |       total       | (COUNT (*) AS total) 
+-------+-------------------+------------------+-------------------+
|   1   |   Projects A      |   Employee A     |         2         |
|   2   |   Projects B      |   Employee A     |        NULL       |
|   3   |   Projects A      |   Employee B     |         3         |
|   4   |   Projects B      |   Employee B     |        NULL       |
|   5   |   Projects A      |   Employee C     |         1         |
|   6   |   Projects B      |   Employee C     |        NULL       |
|   7   |   Projects A      |   Employee D     |         2         |
|   8   |   Projects B      |   Employee D     |        NULL       |
+-------+-------------------+------------------+-------------------+

我已经没有办法实现预期的结果了。因此，我想向本论坛的专家寻求帮助。

Answer 1

P/S: 字段名称可能与上面不同，因为我在不同的架构中编写查询

经过不断尝试，我终于找到了答案。

SELECT
    A.NAME,
    B.NAME,
    (
    SELECT
        COUNT( * ) 
    FROM
        projects
        LEFT JOIN users ON users.id = projects.leader_id 
    WHERE
        (
            projects.service_id IN ( 1, 5 ) 
            AND projects.stage_id < 6 
            AND users.id = A.id 
            AND projects.service_id = B.id 
        ) 
        OR (
            projects.service_id IN ( 2, 3, 4, 7 ) 
            AND projects.stage_id < 7 
            AND users.id = A.id 
            AND projects.service_id = B.id 
        ) 
    ) 
FROM
    users AS A
    CROSS JOIN services AS B

这是输出我预期结果的查询，如下所示。

+-------+-------------------+------------------+-------------------+
|   id  |   project_name    |   employee_name  |       total       | (COUNT (*) AS total) 
+-------+-------------------+------------------+-------------------+
|   1   |   Projects A      |   Employee A     |         2         |
|   2   |   Projects B      |   Employee A     |        NULL       |
|   3   |   Projects A      |   Employee B     |         3         |
|   4   |   Projects B      |   Employee B     |        NULL       |
|   5   |   Projects A      |   Employee C     |         1         |
|   6   |   Projects B      |   Employee C     |        NULL       |
|   7   |   Projects A      |   Employee D     |         2         |
|   8   |   Projects B      |   Employee D     |        NULL       |
+-------+-------------------+------------------+-------------------+