mysql子查询返回多行

Question

我的问题出在第一个子查询选择中。我被告知我要返回多行。

$sql = "SELECT
      messages.message_id
    , messages.sent_timestamp
    , messages.content
    , messages.subject
    , users.user_name
    , (SELECT thread_participants.user_id 
FROM thread_participants 
WHERE thread_participants.user_id !=".$user_id.") as thread_participants
    , (SELECT message_read_state.readDate
       FROM message_read_state
       WHERE message_read_state.message_id = messages.message_id
        and message_read_state.user_id =". $user_id.") as ReadState
    FROM (messages INNER JOIN users ON messages.sender_user_id = users.user_id
    INNER JOIN thread_participants tp ON tp.thread_id = messages.thread_id)
    WHERE (((messages.thread_id)=".$thread_id."))
    ORDER BY messages.sent_timestamp DESC";

Answer 1

您可以使用GROUP_CONCAT() 函数获取单个字段中的所有userIds

试试这个:

$SQL = "SELECT
      messages.message_id
    , messages.sent_timestamp
    , messages.content
    , messages.subject
    , users.user_name
    , (SELECT GROUP_CONCAT(thread_participants.user_id) 
FROM thread_participants 
WHERE thread_participants.user_id !=".$user_id.") as thread_participants
    , (SELECT message_read_state.readDate
       FROM message_read_state
       WHERE message_read_state.message_id = messages.message_id
        and message_read_state.user_id =". $user_id.") as ReadState
    FROM (messages INNER JOIN users ON messages.sender_user_id = users.user_id
    INNER JOIN thread_participants tp ON tp.thread_id = messages.thread_id)
    WHERE (((messages.thread_id)=".$thread_id."))
    ORDER BY messages.sent_timestamp DESC";