sql语句的问题

Question

I have 2 tables

chat_rooms 
chat_name | chat_url | chat_id


chat_msgs
msg_chat_id | timestamp

I'm trying to display it like this :

-------- chat rooms that were active last 10 minutes---------
chat_name | chat_url | count the messages 

Please help

Answer 1

Left join the messages to the chats on the chat ID and filtered for the last 10 minutes using timestampadd(). Then group by the chat room taking the count of timestamps.

SELECT cr.chat_name,
       cr.chat_url,
       count(cm.timestamp) "count the messages"
       FROM chat_rooms cr
            LEFT JOIN chat_msgs cm
                      ON cm.msg_chat_id = cr.chat_id
                         AND cm.timestamp >= timestampadd(minute, -10, now())
                         AND cm.timestamp < now()
       GROUP BY cr.chat_name,
                cr.chat_url;