php - 通过此SQL查询使用INNER JOIN和ON防止重复

请参阅下面的SQL查询。我正在尝试创建一个简单的活动提要，该提要将与任务表中updated_by和/或created_by字段（以较新者为准）中记录的人员及其用户表中的配置文件图片相匹配。这个查询可以工作，除了当两个字段有不同的用户时得到重复，因为在ON子句中，OR语句的两个元素都将返回true，因为总是有一个created_by与users表中的某人匹配-当两个字段都填充了不同的用户时，我不知道如何将其限制为一个。我试过在ON子句中添加更多CASE when，但它没有if/else逻辑，因此它只是默认为再次为true并返回相同的结果。
（注意，我需要保持在WHERE子句中限制结果的能力，状态项作为变量传入，查询包含在PHP函数中。）
思想？

SELECT tasks.*, users.profile_pic,
CASE WHEN tasks.updated_at > tasks.created_at
THEN tasks.updated_at
ELSE tasks.created_at
END AS recent_activity
FROM tasks
INNER JOIN users
ON tasks.updated_by = users.name OR tasks.created_by = users.name
WHERE status <> :status1 AND status <> :status2
ORDER BY recent_activity DESC LIMIT 10'

最佳答案

你可以模仿很多“if”逻辑，比如：

ON (tasks.updated_at > tasks.created_at AND tasks.updated_by = users.name)
   OR (tasks.updated_at <= tasks.created_at AND tasks.created_by = users.name)

或者使用类似于您在select中的逻辑：

ON CASE
      WHEN tasks.updated_at > tasks.created_at
      THEN tasks.updated_by
      ELSE tasks.created_by
      END = users.name

另一方面（由于上述或更复杂的比较，几乎排除了索引的任何好处），您可以两次加入到用户表中，并在SELECT中选择优先级值：

SELECT tasks.*
  , CASE WHEN tasks.updated_at > tasks.created_at
    THEN uu.profile_pic
    ELSE cu.profile_pic
  END AS recent_profile_pic
  , CASE WHEN tasks.updated_at > tasks.created_at
    THEN uu.name
    ELSE cu.name
  END AS recent_activity
FROM tasks
LEFT JOIN users AS uu ON tasks.updated_by = uu.name
INNER JOIN users AS cu ON tasks.created_by = cu.name
WHERE status <> :status1 AND status <> :status2
ORDER BY recent_activity DESC LIMIT 10

这是假设您使用的所有字段都没有指定它们所属的表来自tasks，并且额外的连接不会导致歧义。

关于php - 通过此SQL查询使用INNER JOIN和ON防止重复，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/40621745/