时间戳

首页 > 解决方案 > SQL 从其他表中给定时间戳后的记录中选择

php - SQL 从其他表中给定时间戳后的记录中选择

问题描述

我做了2张桌子。一个用于消息，另一个用于已删除聊天。如果有人删除了聊天，那么它应该只显示删除时间戳后的新消息

请在您的答案中使用这些表/列名称：

user_messages: user_id, chat_id, timestamp
user_chats_deleted: user_id, chat_id, timestamp

我的 SQL 代码：

SELECT * FROM user_messages um

    LEFT JOIN (

SELECT *
FROM user_chats_deleted
GROUP BY timestamp Order by timestamp ASC Limit 1
)ud ON (um.chat_id = ud.chat_id and um.user_id = ud.user_id and ud.timestamp     < um.timestamp)

    where um.chat_id = '4' ORDER BY um.timestamp ASC

它显示消息但不是我想要的：/

标签： phphtmlmysqlsqldatabase

解决方案

您可以尝试几个不同的查询，尽管它们都应该做同样的事情：

SELECT *
  FROM user_messages um
 WHERE um.timestamp >
     ( SELECT MAX(um.timestamp)
         FROM user_chats_deleted ucd
        WHERE ucd.user_id = um.user_id
          AND ucd.chat_id = um.chat_id
     );

一个更干净、更易读/易懂的 SQL 脚本：

 WITH Last_Deleted_Message AS
    ( 
      SELECT user_id
           , chat_id
           , MAX(timestamp) AS max_deleted_timestamp
        FROM user_chats_deleted ucd
       GROUP
          BY user_id
           , chat_id
     )
SELECT user_id
  FROM user_messages um
  JOIN Last_Deleted_Message ldm
    ON um.user_id = ldm.user_id
   AND um.chat_id = ldm.chat_id
   AND um.timestamp > ldm.max_deleted_timestamp
 WHERE um.chat_id = '4' 
 ORDER 
    BY um.timestamp ASC;

推荐阅读