MySQL 查询多重连接为每一行复制数据？ JIRA 数据库

Question

我正在尝试提取 future 30 天内特定组中每个用户的总工作时间。我不擅长 MySQL，所以其中很多可能是错误的。这是来自 JIRA 数据库。

    SELECT DISTINCT cu.display_name AS 'Associate', sum(wl.timeworked/3600) AS '30 Days'
    FROM worklog AS wl, cwd_user AS cu
    INNER JOIN cwd_membership AS cm
        ON cu.directory_id=cm.directory_id
        AND cu.lower_user_name=cm.lower_child_name
        AND cm.membership_type='GROUP_USER'
    INNER JOIN worklog
        ON worklog.AUTHOR = cu.user_name
    WHERE cm.lower_parent_name='atl_servicedesk_it_agents' AND wl.STARTDATE BETWEEN NOW() AND DATE_ADD(NOW(), INTERVAL 30 DAY)
 ORDER BY cu.last_name;

现在它只显示第一个用户名以及每个人工作时间内所有内容的总和。这显然不是我想要的...如果我从 timeworked 中删除总和，它会显示该组中大约一半的用户，但每个用户的 timeworked 值为 16 小时，这是不正确的。在接下来的 30 天内只有一个用户有任何值(value)，而且是 16 小时，因此出于某种原因它正在复制此信息。有什么想法吗？

Answer 1

通过删除 INNER JOIN 让它工作。

SELECT cwd_user.display_name AS 'Associate', sum(worklog.timeworked/3600) AS '30 Days'
FROM worklog, cwd_user,cwd_membership
WHERE worklog.AUTHOR = cwd_user.user_name
and cwd_user.directory_id = cwd_membership.directory_id
and cwd_user.lower_user_name = cwd_membership.lower_child_name
and cwd_membership.membership_type = 'GROUP_USER'
and cwd_membership.lower_parent_name = 'atl_servicedesk_it_agents'
and worklog.STARTDATE BETWEEN NOW() AND DATE_ADD(NOW(), INTERVAL 60 DAY)
GROUP BY cwd_user. display_name
ORDER BY cwd_user.last_name;