mysql - 子查询中的操作数应包含 1 列

Question

当我尝试在子查询上插入第二个字段“e.occurred_at”时，我收到此错误，我想选择此表中的两个字段，有解决办法吗？

SELECT DISTINCT (
inbox_msg.id_conversation
), users.name, users.surname, users.username, n. * , image_upload.name_image, (

SELECT e.message, e.occured_at
FROM inbox_msg e
WHERE e.id_user = inbox_msg.id_user
ORDER BY occured_at DESC 
LIMIT 1
) AS last_msg
FROM (

SELECT * 
FROM inbox_join
WHERE (
id_user =1 || id_user_2 =1
)
)n
INNER JOIN inbox_msg ON n.id_conversation = inbox_msg.id_conversation
INNER JOIN users ON users.id = inbox_msg.id_user
INNER JOIN image_upload ON image_upload.id_image = users.profile_image
WHERE inbox_msg.id_user <>1
ORDER BY n.occured_at DESC 

Answer 1

您的子查询需要一列来创建别名，并且您已经提供了两列，任一解决方案都是为每列执行两次子查询，例如

(
SELECT e.message
FROM inbox_msg e
WHERE e.id_user = inbox_msg.id_user
ORDER BY occured_at DESC 
LIMIT 1
) AS last_msg,
(
SELECT e.occured_at
FROM inbox_msg e
WHERE e.id_user = inbox_msg.id_user
ORDER BY occured_at DESC 
LIMIT 1
) AS last_msg_time

连接列的其他解决方案

(
SELECT CONCAT(e.message, ' At ', e.occured_at)
FROM inbox_msg e
WHERE e.id_user = inbox_msg.id_user
ORDER BY occured_at DESC 
LIMIT 1
) AS last_msg