mysql 这个查询尝试连接两个 Select 语句有什么问题

Question

这是尝试连接到 select 语句的查询!我做错了什么？

​SELECT u.user_id,c.c_id,u.username,u.email, u.user_pic_path,c.time, G.file_name
     FROM conversation c, users u
 LEFT OUTER JOIN gallery G 
 ON U.user_pic_path = G.img_id
     WHERE CASE 
     WHEN c.user_one =1
     THEN c.user_two = u.user_id
     WHEN c.user_two = 1
     THEN c.user_one= u.user_id
     END 
     AND (c.user_one =1 OR c.user_two = 1)
   JOIN(SELECT R.time
        FROM conversation_reply R 
        ORDER BY R.time DESC LIMIT 1)
        ON R.c_id_fk = c.c_id

第一个查询，我在其中加入 2 个表。

SELECT u.user_id,c.c_id,u.username,u.email, u.user_pic_path,c.time, G.file_name
         FROM conversation c, users u
     LEFT OUTER JOIN gallery G 
     ON U.user_pic_path = G.img_id
         WHERE CASE 
         WHEN c.user_one = '$user_one'
         THEN c.user_two = u.user_id
         WHEN c.user_two = '$user_one'
         THEN c.user_one= u.user_id
         END 
         AND (c.user_one ='$user_one' OR c.user_two ='$user_one')
         Order by UNIX_TIMESTAMP(C.time) DESC Limit 20";

最后是第二个简单查询。

SELECT R.cr_id,R.time,R.reply 
                 FROM conversation_reply R 
                 WHERE R.c_id_fk = ?
                 ORDER BY R.cr_id DESC LIMIT 1"

这就是我最终得到的结果。

$sql = "SELECT u.user_id,c.c_id,u.username,u.email, u.user_pic_path,G.file_name, 
        MAX(r.time) last_reply_time,r.reply
FROM conversation c
JOIN users u ON
    CASE 
        WHEN c.user_one = 1
            THEN c.user_two = u.user_id
        WHEN c.user_two = 1
            THEN c.user_one= u.user_id
    END 
JOIN conversation_reply r ON c.c_id = r.c_id_fk
LEFT OUTER JOIN gallery G ON U.user_pic_path = G.img_id
WHERE (c.user_one = 1 OR c.user_two = 1)
GROUP BY c.c_id ORDER BY last_reply_time DESC";

唯一的是我从对话中得到的第一个对话回复不是最新的。

Answer 1

当您连接子查询时，别名必须位于子查询之后，而不是子查询内的表名之后。

JOIN(SELECT time, c_id_fk
    FROM conversation_reply 
    ORDER BY R.time DESC LIMIT 1) R
ON R.c_id_fk = c.c_id

但我不确定这会达到你想要的效果。此子查询查找所有对话中的最新回复，而不仅仅是两个用户之间的对话。如果这不是两个用户之一的对话，您将不会得到任何结果。

如果您想获取该对话中最近回复的时间，只需使用 conversation_reply 执行普通的 JOIN 并选择 MAX(time) .

SELECT u.user_id,c.c_id,u.username,u.email, u.user_pic_path,c.time, G.file_name, 
        MAX(r.time) last_reply_time
FROM conversation c
JOIN users u ON
    CASE 
        WHEN c.user_one =1
            THEN c.user_two = u.user_id
        WHEN c.user_two = 1
            THEN c.user_one= u.user_id
    END 
JOIN conversation_reply r ON c.id = r.c_id_fk
LEFT OUTER JOIN gallery G ON U.user_pic_path = G.img_id
WHERE (c.user_one =1 OR c.user_two = 1)