我的 LEFT JOIN 和 SUM 之一的结果有问题

Question

所以我有一个关于LEFT JOIN的问题，这段代码根据用户是否获得了该组，为totalPoints返回不同的值。 （如果用户没有获得组或事件，它会返回正确的值）

我只想掌握如何获得LEFT JOIN flow_has_vote ON flow_has _vote.flow_id=flows.id 每次都有效。我之前用三个查询做了一个解决方案，一个获取组和事件规则，一个检查用户是否在考虑安全性的情况下获取了组或事件，一个获取流量...

我想我可以解决这是通过两个查询来实现的，一个查询获取组和事件规则，并检查用户是否获取了组和事件，然后一个查询根据用户应该有权访问它来获取流量。

现在我'我获取一个查询中所需的所有信息，然后使用 IF 语句检查是否应该打印它...

所以，我的问题是，是否有可能获得 SUM(flow_has _vote.points) AStotalPoints 可以这样工作吗？你知道怎么做吗？

而且我也有点好奇，一个查询是处理这个问题的最佳方法吗？当考虑到性能时，使用两个是否合理？

 SELECT 
       flows.id AS flowId,
       flows.security,
       SUM(flow__has__vote.points) AS totalPoints,
       users.id AS userId,
       users.alias,
       flows.event_id AS eventId,
       events.group_id AS groupId,
       events.membershipRules AS eMR,
       groups.membershipRules AS gMR,
       user__has__group.permission AS userHasGroup,
       user__has__event.permission AS userHasEvent
    FROM   
       users,
       events   LEFT JOIN user__has__event ON user__has__event.user_id = '.$userId.',
       groups   LEFT JOIN user__has__group ON user__has__group.user_id = '.$userId.',
       flows    LEFT JOIN flow__has__vote ON flow__has__vote.flow_id=flows.id
    WHERE   
       flows.user_id = users.id AND 
       events.id = flows.event_id AND 
       groups.id = events.group_id AND 
       flows.id='.$flowId

如果您想知道 SQL 语句正在做什么，获取流的信息（帖子）、流所在的事件和组的信息、检查用户对组和事件的访问权限以及获取所有投票对于流程...

这就是表格的样子...

FLOWS id,security,event_id,user_id
USERS id, alias
EVENTS id, name group_id, membershipRules
GROUPS id, name, membershipRules
USER__HAS__GROUP user_id,group_id,permission
USER__HAS__EVENT user_id,event_id,permission
FLOW__HAS__VOTE flow_id,user_id,points

这是我希望的结果...

+--------+----------+-------------+--------+--------+---------+---------+-----+-----+--------------+--------------+
| flowId | security | totalPoints | userId | alias  | eventId | groupId | eMR | gMR | userHasGroup | userHasEvent | 
+--------+----------+-------------+--------+--------+---------+---------+-----+-----+--------------+--------------+
|      1 | 2        | 1337        | 5      | Pontus | 15      | 2       | 2   | 2   | 4            | 4            | 
+--------+----------+-------------+--------+--------+---------+---------+-----+-----+--------------+--------------+

还有一个例子...

+--------+----------+-------------+--------+--------+---------+---------+-----+-----+--------------+--------------+
| flowId | security | totalPoints | userId | alias  | eventId | groupId | eMR | gMR | userHasGroup | userHasEvent | 
+--------+----------+-------------+--------+--------+---------+---------+-----+-----+--------------+--------------+
|      1 | 2        | 1337        | 6      | Kezia  | 15      | 2       | 2   | 2   | null         | null         | 
+--------+----------+-------------+--------+--------+---------+---------+-----+-----+--------------+--------------+

享受你的生活〜Pontus

Answer 1

因此，基本上要点（恕我直言）是不要在 WHERE 子句中包含 LEFT JOINed 的表条件，因为这使得 LEFT JOIN > 行为类似于INNER JOIN。

首先尝试这个查询（尽管我确信您必须进行调整，因为我不确定您到底想要什么结果，稍后会详细介绍）：

SELECT
       flows.id AS flowId,
       flows.security,
       SUM(flow__has__vote.points) AS totalPoints,
       users.id AS userId,
       users.alias,
       flows.event_id AS eventId,
       events.group_id AS groupId,
       events.membershipRules AS eMR,
       groups.membershipRules AS gMR,
       user__has__group.permission AS userHasGroup,
       user__has__event.permission AS userHasEvent
FROM users,
LEFT JOIN user__has__event 
     ON user__has__event.user_id = users.id,
LEFT JOIN events 
     ON user__has__event.event_id = events.id

LEFT JOIN user__has__group 
     ON user__has__group.user_id = users.id,
LEFT JOIN groups 
     ON user__has__group.group_id = groups.id 
     AND groups.id = events.group_id

LEFT JOIN flows 
     ON flows.user_id = users.id 
     AND events.id = flows.event_id 
     AND flows.id='.$flowId'
LEFT JOIN flow__has__vote 
     ON flow__has__vote.flow_id = flows.id

WHERE users.id = '.$userId.'
GROUP BY users.id

在这里，我将所有内容LEFT JOINed用户，并且还按用户分组。我有一种感觉，您可能希望通过 (flows.id?, events.id?) 将列添加到组中

另外，您可能希望将一些 < code>LEFT JOINs 到 JOIN，因此您将仅获得具有“流程”的用户。