请参考下面的SQL查询。我试图创建一个简单的活动feed,将匹配在任务表中的updated_by
和/或created_by
字段中记录的人(以较新的为准),在用户表中显示他们的个人资料图片。这个查询工作,除了当两个字段有不同的用户时我得到重复,因为在ON子句中OR语句的两个元素将返回true,因为总是有一个created_by
将匹配用户表中的某人-我无法弄清楚如何将这限制为只有一个,当两个字段都填充了不同的用户。我试过在ON子句中添加更多的CASE when,但没有if/else逻辑,所以它只是默认为再次为真并返回相同的结果。
(注意,我需要保持在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'
可以用OR和and来模拟很多"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
或者(因为OR和像上面这样更复杂的比较几乎排除了索引的任何好处),您可以两次连接到users表,并在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
的一部分的表,并且额外的连接不会导致歧义。