mysql - 我怎样才能找到谁发送的消息多于收到的消息？

Question

我有一个用于聊天应用程序的 SQL 数据库，我想找出哪个用户发送的消息多于收到的消息。

CREATE TABLE Users(uid int PRIMARY KEY, name text, phone text);

CREATE TABLE Messages( recipient int REFERENCES Users(uid),
sender int REFERENCES Users(uid), time timestamp NOT NULL, 
message text NOT NULL, PRIMARY KEY (recipient, sender, time));

我已经尝试过示例中的那个，但它一直显示此消息

Query Error: Error: ER_NO_SUCH_TABLE: Table 'test.messages' doesn't exist"

我找不到我哪里错了

https://www.db-fiddle.com/f/fD1EvkosU3yvfmFV99MFqz/0

Answer 1

您可以使用这个查询，它计算每个发件人发送的消息数，以及每个收件人在两个派生表中收到的消息数，然后将这些表JOIN到 Users 表基于与发件人或收件人匹配的 uid。然后它只选择发送消息多于接收消息的用户:

SELECT u.name, m1.sent, m2.received
FROM Users u
LEFT JOIN (SELECT sender, COUNT(*) AS sent
           FROM Messages 
           GROUP BY sender) m1 ON m1.sender = u.uid
LEFT JOIN (SELECT recipient, COUNT(*) AS received
           FROM Messages 
           GROUP BY recipient) m2 ON m2.recipient = u.uid
WHERE sent > received

输出:

name    sent    received
John    4       3
Jim     5       2
Luis    3       1

Updated demo on dbfiddle