多个左连接已影响我的SUM（TIMESTAMPDIFF计算

I am currently trying to create a report for the amount of time in total some individuals on my PHPBB3 forum have booked in, for the last week. Initially, I had the following query that worked as expected:

我目前正在尝试创建一份报告,说明我的PHPBB3论坛上的某些人在上周预订的总时间。最初,我有以下查询按预期工作:

SELECT forum_users.username, SUM(TIMESTAMPDIFF(SECOND, schedule_slots.time_starting, schedule_slots.time_finishing)) AS seconds 
FROM forum_users 
LEFT JOIN schedule_slots 
    ON forum_users.user_id = schedule_slots.user_id 
    AND schedule_slots.time_starting >= (CURDATE() - INTERVAL 1 WEEK) 
    AND schedule_slots.is_del = 0 
    AND schedule_slots.channel = 0
WHERE (forum_users.group_id = 8 OR forum_users.group_id = 5 OR forum_users.group_id = 14) 
GROUP BY forum_users.username 
ORDER BY upper(forum_users.username)

However, when I go to join another table, the timestamp difference ends up being incorrectly calculated (it's higher), here's my newer non working statement:

但是,当我去加入另一个表时,时间戳差异最终被错误计算(它更高),这是我新的非工作语句:

SELECT forum_users.username, SUM(TIMESTAMPDIFF(SECOND, schedule_slots.time_starting, schedule_slots.time_finishing)) AS seconds, group_concat(DISTINCT forum_user_group.group_id) AS user_groups 
FROM forum_users 
LEFT JOIN schedule_slots 
    ON forum_users.user_id = schedule_slots.user_id 
    AND schedule_slots.time_starting >= (CURDATE() - INTERVAL 1 WEEK) 
    AND schedule_slots.is_del = 0 
    AND schedule_slots.channel = 0 
LEFT JOIN forum_user_group 
    ON forum_user_group.user_id = forum_users.user_id 
WHERE (forum_users.group_id = 8 OR forum_users.group_id = 5 OR forum_users.group_id = 14 OR forum_users.group_id = 12) 
GROUP BY forum_users.username 
ORDER BY upper(forum_users.username)

I'm drawing a blank on this one, and your help is greatly appreciated.

我在这个问题上画了一个空白,非常感谢你的帮助。

1 个解决方案

#1

The timestamp difference is calculated right, but you multiply it by number of groups the user is in.

时间戳差异是正确计算的,但您可以将其乘以用户所在的组数。

I would try:

我会尝试:

SELECT forum_users.username, SUM(TIMESTAMPDIFF(SECOND, schedule_slots.time_starting, schedule_slots.time_finishing)) AS seconds, 
(select group_concat(DISTINCT group_id) from forum_user_group WHERE forum_user_group.user_id = forum_users.user_id) AS user_groups 
FROM forum_users 
LEFT JOIN schedule_slots 
ON forum_users.user_id = schedule_slots.user_id 
AND schedule_slots.time_starting >= (CURDATE() - INTERVAL 1 WEEK) 
AND schedule_slots.is_del = 0 
AND schedule_slots.channel = 0 
WHERE (forum_users.group_id = 8 OR forum_users.group_id = 5 OR forum_users.group_id = 14 OR forum_users.group_id = 12) 
GROUP BY forum_users.username, user_groups 
ORDER BY upper(forum_users.username)

#1