douchuo0730 2018-01-01 12:39
浏览 94
已采纳

SQL:按消息排序不起作用

why does this SQL Code not run?

user_chats

             id | user_id | to_user_id |  ad_id  | timestamp
----------------+---------+------------+---------+-----------
              1 |       1 |      6     |   13    |  1513516133

user_messages

             id | chat_id |    text    | user_id | timestamp
----------------+---------+------------+---------+-----------
              1 |       1 |    Hello   |    1    |  1513516133
              2 |       1 |    Hi!     |    6    |  1513516754

I want to get the Chats and order them by user_messages.timestamp. My SQL Code is:

SELECT user_chats.id,
       user_chats.timestamp,
       ad_id,
       title,
       user_chats.user_id
FROM   user_chats
       INNER JOIN ads
               ON ads.id = ad_id
WHERE  user_chats.user_id = "1"
        OR user_chats.to_user_id = "1"
ORDER  BY (SELECT id
           FROM   user_messages
           WHERE  chat_id = user_chats.id
           ORDER  BY user_messages.id DESC) 
  • 写回答

1条回答 默认 最新

  • dsds33222 2018-01-01 13:14
    关注

    The issue is that you've used a subquery in your Order By clause: as this returns multiple results for each record in the main query it cannot be used to order the results of the main query.

    I think you're trying to order the results by the latest message in each chat, but simply joining the user_messages table will mean you'll get duplicates (each chat being returned once per message). You can get around this by joining to an inline view:

    SELECT DISTINCT user_chats.id,
           user_chats.timestamp,
           ad_id,
           title,
           user_chats.user_id
    FROM   user_chats
           INNER JOIN ads
                   ON ads.id = ad_id
           LEFT JOIN   
             --in line view aliased 'UM' returns one row per chat_id in user_messages, with the last timestamp for that ID
                      (SELECT max(timestamp) LastMessage, 
                      chat_id
                      FROM user_messages
                      GROUP BY chat_id) um
                   ON um.chat_id = user_chats.id
    WHERE  user_chats.user_id = 1
            OR user_chats.to_user_id = 1
    ORDER  BY um.LastMessage desc
    
    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论

报告相同问题?

悬赏问题

  • ¥15 Arcgis相交分析无法绘制一个或多个图形
  • ¥15 seatunnel-web使用SQL组件时候后台报错,无法找到表格
  • ¥15 fpga自动售货机数码管(相关搜索:数字时钟)
  • ¥15 用前端向数据库插入数据,通过debug发现数据能走到后端,但是放行之后就会提示错误
  • ¥30 3天&7天&&15天&销量如何统计同一行
  • ¥30 帮我写一段可以读取LD2450数据并计算距离的Arduino代码
  • ¥15 飞机曲面部件如机翼,壁板等具体的孔位模型
  • ¥15 vs2019中数据导出问题
  • ¥20 云服务Linux系统TCP-MSS值修改?
  • ¥20 关于#单片机#的问题:项目:使用模拟iic与ov2640通讯环境:F407问题:读取的ID号总是0xff,自己调了调发现在读从机数据时,SDA线上并未有信号变化(语言-c语言)