MySQL SUM 和 CASE 可以在 DISTINCTROW 上使用吗？

Question

我有这个查询:

SELECT
    p.name,
    COUNT(DISTINCT t.keyTask) AS totalTasksCount,
    SUM(DISTINCT CASE WHEN t.keyPriority = 24 AND (t.keyState = 16 OR t.keyState = 17) THEN 1 ELSE 0 END) AS highPriorityTasksCount,
    SUM(DISTINCT CASE WHEN t.keyState = 16 OR t.keyState = 17 THEN 1 ELSE 0 END) AS activesTasksCount,
    SUM(DISTINCT t.estimatedDuration) AS estimatedDuration,
    SUM(TIMESTAMPDIFF(SECOND,wp.start,wp.end)) * 1000 AS workedDuration
FROM projects_projects p
LEFT JOIN projects_tasks t
    ON p.keyProject = t.keyProject
LEFT JOIN projects_workPeriods wp
    ON wp.keyTask = t.keyTask
LEFT JOIN common_organizations o
    ON o.keyOrganization = p.keyOrganization
LEFT JOIN common_users uc
    ON uc.keyUser = p.keyUserCreator
LEFT JOIN common_users uu
    ON uu.keyUser = p.keyUserUpdater
GROUP BY
    p.keyProject
ORDER BY
    highPriorityTasksCount DESC,
    activesTasksCount DESC,
    p.updated DESC,
    p.name;

但是结果字段highPriorityTasksCount和activesTasksCount返回 0 或 1，这对于该查询来说是正常的。我想知道有没有办法制作DISTINCTROW对于这些字段没有子查询，只处理行而不是案例结果值？

当前结果:

p.name,
totalTasksCount,
highPriorityTasksCount,
activesTasksCount,
estimatedDuration,
workedDuration

'Project 1',  '4', '1', '1',  '14400000',  '15300000'
'Project 2', '48', '1', '1',  '84600000', '503100000'
'Project 3',  '6', '1', '1', '108000000',        NULL
'Project 4',  '4', '1', '1',  '25200000',  '30600000'
'Project 5',  '5', '1', '1', '226800000',  '39600000'
'Project 6',  '2', '0', '1',        NULL,  '10800000'
'Project 7',  '9', '0', '1',        NULL,  '36900000'

预期结果:

'Project 1',  '4', '1', '1',  '14400000',  '15300000'
'Project 2', '48','20', '2',  '84600000', '503100000'
'Project 3',  '6', '1', '1', '108000000',        NULL
'Project 4',  '4', '4', '2',  '25200000',  '30600000'
'Project 5',  '5', '5', '1', '226800000',  '39600000'
'Project 6',  '2', '0', '1',        NULL,  '10800000'
'Project 7',  '9', '0', '1',        NULL,  '36900000'

编辑:

使用子查询修改查询，因为没有办法，有关优化的帮助将不胜感激，这个正在工作:

SELECT
    p.name,
    COUNT(DISTINCT t.keyTask) AS totalTasksCount,
    /* OLD SUM(DISTINCT CASE WHEN t.keyPriority = 24 AND (t.keyState = 16 OR t.keyState = 17) THEN 1 ELSE 0 END) AS highPriorityTasksCount, */
    (
        SELECT
    SUM(CASE WHEN st.keyPriority = 24 AND (st.keyState = 16 OR st.keyState = 17) THEN 1 ELSE 0 END)
    FROM projects_tasks st
    WHERE
    st.keyProject = p.keyProject
) AS highPriorityTasksCount,
    /* OLD SUM(DISTINCT CASE WHEN t.keyState = 16 OR t.keyState = 17 THEN 1 ELSE 0 END) AS activesTasksCount, */
    (
        SELECT
    SUM(CASE WHEN st.keyState = 16 OR st.keyState = 17 THEN 1 ELSE 0 END)
    FROM projects_tasks st
    WHERE
    st.keyProject = p.keyProject
) AS activesTasksCount,
    SUM(t.estimatedDuration) AS estimatedDuration,
    SUM(TIMESTAMPDIFF(SECOND,wp.start,wp.end)) * 1000 AS workedDuration
FROM projects_projects p
LEFT JOIN projects_tasks t
    ON p.keyProject = t.keyProject
LEFT JOIN projects_workPeriods wp
    ON wp.keyTask = t.keyTask
LEFT JOIN common_organizations o
    ON o.keyOrganization = p.keyOrganization
LEFT JOIN common_users uc
    ON uc.keyUser = p.keyUserCreator
LEFT JOIN common_users uu
    ON uu.keyUser = p.keyUserUpdater
GROUP BY
    p.keyProject
ORDER BY
    highPriorityTasksCount DESC,
    activesTasksCount DESC,
    p.updated DESC,
    p.name;

Answer 1

without subquery.

不，您必须使用子查询，否则我对 MySQL 不了解。