mysql - 从受限于内部连接的 sqlite 获取数据

Question

我想将房间限制在 20 间，最后一条消息仅由 lastMessage.created_at DESC 订购

消息表

     create table tb_message (
            message_id varchar(50) primary key  ,
            room_id varchar(50) not null,
            message_type text not null,
            message text not null,
            sender_id varchar(50) not null,
            recipient_id varchar(50) not null,
            reply_id varchar(50) default null ,
            created_at BIGINT ,
            status integer ,
            constraint tb_message_chat_id_fk foreign key (room_id) references tb_room (room_id),
            constraint tb_message_sender_user_fk foreign key (sender_id) references tb_user (user_id),
            constraint tb_message_recipient_user_fk foreign key (recipient_id) references tb_user (user_id))

房间表

          create table tb_room (
            room_id  varchar(50) primary key ,
            group_id varchar(50),
            room_type text ,
            owner_id varchar(50) not null ,
            is_muted integer default 0,
             constraint tb_chat_user_id_fk foreign key (owner_id) references tb_user (user_id))   

用户表

      create table tb_user (
            user_id varchar(50) primary key ,
            user_name text not null,
            online_status integer )

我的查询是

      SELECT *
      FROM  (select * from tb_room limit 20) as room
      
      INNER JOIN tb_message 
        ON room.room_id = (select room_id from tb_message   limit 1)  
        
      INNER JOIN tb_user
        ON tb_user.user_id = room.owner_id
      ORDER BY tb_message.created_at DESC 

它返回所有房间及其所有消息

标签： mysqlinner-joinsqflite