mysql - MySQL 中的多个多对多关系 JOINed

Question

对于我正在从事的项目，我正在尝试查询时钟，但是当我LEFT JOIN多个多对多(或在单个用户的记录意义上，一对多)它会创建重复的条目，因此当它分组时，总计不正确。

鉴于以下模拟架构:

还有一个查询:

SELECT
UserTbl.UserID,
CONCAT_WS(", ", UserTbl.LastName, UserTbl.FirstName) AS UserName,
SUM(TIMESTAMPDIFF(MINUTE,  TimeClockTbl.StartDateTime, TimeClockTbl.EndDateTime)) AS ClockedInMinutes,
FROM
Users AS UserTbl
LEFT JOIN
TimeClock AS TimeClockTbl
ON UserTbl.UserID = TimeClockTbl.UserID
LEFT JOIN
UserRoles AS UserRoleTbl
ON UserTbl.UserID = UserRoleTbl.UserID
WHERE
UserRoleTbl.RoleID IN (1,2,3)
GROUP BY
UserTbl.UserID
ORDER BY
UserTbl.LastName ASC,
UserTbl.FirstName ASC;

如果用户只分配了 1 个角色，则效果很好，但如果分配了第二个或第三个角色，则最终结果似乎会成倍增加。我考虑过使用 GROUP_CONCAT 作为角色并进行过滤，但这似乎效率不高。我还考虑使用子查询来计算给定用户的计时小时数，但我认为这会产生相同的结果。还需要注意的是，它被缩放为具有多个条目的 TimeClock 表和一个包含多个条目的 Scheduled 表。

我怎样才能以相当高的效率做到这一点？

Answer 1

简单的决定:

SELECT UserTbl.UserID,
       CONCAT_WS(", ", UserTbl.LastName, UserTbl.FirstName) AS UserName,
       SUM(TIMESTAMPDIFF(MINUTE, TimeClockTbl.StartDateTime, TimeClockTbl.EndDateTime)) AS ClockedInMinutes,
  FROM Users AS UserTbl
  LEFT JOIN TimeClock AS TimeClockTbl ON UserTbl.UserID = TimeClockTbl.UserID
 WHERE UserTbl.UserID IN( SELECT UserID FROM UserRoles WHERE RoleID IN (1,2,3) )
 GROUP BY UserTbl.UserID
 ORDER BY UserTbl.LastName ASC, UserTbl.FirstName ASC;

类似情况的概念 - 一致连接:

SELECT A.*,
       SUM(TIMESTAMPDIFF(MINUTE, TimeClockTbl.StartDateTime, TimeClockTbl.EndDateTime)) AS ClockedInMinutes,
       MAX(A.RolesTitle) AS RolesTitle
  FROM (
        SELECT UserTbl.UserID,
               CONCAT_WS(", ", UserTbl.LastName, UserTbl.FirstName) AS UserName,
               FirstName, LastName,
               GROUP_CONCAT(Roles.Title) as RolesTitle
          FROM Users AS UserTbl
          JOIN UserRoles AS UserRoleTbl  ON UserTbl.UserID = UserRoleTbl.UserID
          JOIN Roles ON Roles.RoleID=UserRoleTbl.RoleID
         WHERE UserRoleTbl.RoleID IN (1,2,3)
         GROUP BY UserTbl.UserID
       ) A
  LEFT JOIN TimeClock AS TimeClockTbl ON A.UserID = TimeClockTbl.UserID
 GROUP BY A.UserID
 ORDER BY A.LastName ASC, A.FirstName ASC;