时间戳

首页 > 解决方案 > 尝试选择 distrinct 时出现语法错误或访问冲突

mysql - 尝试选择 distrinct 时出现语法错误或访问冲突

问题描述

老实说，我不确定为什么会出现错误，因为我只是将两个选择移到了 distrinct 并在左外连接中，但它们之前在主打开选择语句中工作

SELECT body, timestamp FROM chats 
LEFT OUTER JOIN messages ON chats.id = messages.chat_id 
LEFT OUTER JOIN (SELECT DISTINCT sender.first_name AS sender_first_name, sender.last_name AS sender_last_name,
sender.username AS sender_username) users AS sender ON chats.from_user_id = sender.id 
LEFT OUTER JOIN (SELECT DISTINCT reciever.first_name AS reciever_first_name, reciever.last_name AS reciever_last_name,
reciever.username AS reciever_username) users AS reciever ON chats.to_user_id = reciever.id 
WHERE from_user_id = :fromUserId AND to_user_id = :toUserId

还尝试使用 FROM 子句

LEFT OUTER JOIN (SELECT DISTINCT sender.first_name AS sender_first_name, sender.last_name AS sender_last_name,
                                sender.username AS sender_username FROM users) users AS sender ON chats.from_user_id = sender.id

标签： mysqlsqljoinmariadbrelational-database

解决方案

这不是一个有效的子查询：

(SELECT DISTINCT sender.first_name AS sender_first_name, sender.last_name AS sender_last_name,
 sender.username AS sender_username) users AS sender

您还没有解释查询应该做什么，但我推测您想要：

SELECT body, timestamp,
       us.first_name AS sender_first_name, us.last_name AS sender_last_name, us.username AS sender_username
       ur.first_name AS receiver_first_name, ur.last_name AS receiver_last_name, ur.username AS receiver_username
FROM chats c LEFT OUTER JOIN
     messages m
     ON c.id = m.chat_id LEFT OUTER JOIN
     users us
     ON c.from_user_id = us.id LEFT OUTER JOIN
     users ur
     ON c.to_user_id = ur.id 
WHERE c.from_user_id = :fromUserId AND c.to_user_id = :toUserId;

DISTINCT加入id. _

推荐阅读