与SQL查询混淆 [英] Confused with an SQL query

问题描述

假设我们有一个用于聊天应用程序的数据库：

Let's assume we have a database for a chat application:

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));

我想查找至少两个不同用户对之间已发送的所有消息。例如，如果消息 Hello已从用户1
发送到用户2，也从用户75发送到用户83，则必须在结果中显示该消息。但是，如果仅在用户1和用户2之间发送了它，则不应在结果中显示它。

I want to find all the messages that have been sent between at least two different pairs of users. For instance, if message "Hello" has been sent from User 1 to User 2 and from User 75 to User 83 as well then it must be shown to the result. However, if it has been sent only between User 1 and User 2 then it shouldn't be shown to the result.

我考虑将至少出现两次的所有消息分组如下：

I consider grouping all the messages that appear at least two times as follows:

SELECT message 
FROM Messages 
GROUP BY message 
HAVING COUNT(*) > 1

但是，这将无济于事，因为同一对用户可能已经通过一遍又一遍。我可以同时按发件人，收件人和邮件分组吗？如果是的话，这将给我带来什么？

However, this won't help since the same pair of users might have sent the same message over and over again. Can I group by sender, recipient and message at the same time? If yes, what is this gonna give me as a result?

此外，有没有一种方法可以让我轻松地用随机值填充这两个表并自己进行查询以检查它们？

In addition, is there a way that I could easily fill these two tables with random values and practise queries by myself in order to check them?

谢谢！

推荐答案

是的，首先将user1，user2和&信息。这会给您每对消息一个唯一的消息：

Yea first start by grouping user1, user2, & message. This gives you a unique message for every pair:

SELECT case when recipient > sender then recipient else sender end user1,
       case when recipient > sender then sender else recipient end user2,
       message 
FROM Messages 
GROUP BY user1, user2, message

然后从该结果组中按Message并仅返回大于1的计数。您可以使用嵌套查询来做到这一点：

Then from that result group by Message and return only count greater than 1. You can use a nested query to do this:

SELECT message, COUNT(message) 
FROM (SELECT case when recipient > sender then recipient else sender end user1,
            case when recipient > sender then sender else recipient end user2,
            message 
     FROM Messages 
     GROUP BY user1, user2, message) PairMessages 
GROUP BY message 
HAVING COUNT(message) > 1

也许以此作为测试开始：

Maybe start with this as a test:

INSERT INTO Users VALUES (1,'john',1111111111)
INSERT INTO Users VALUES (2,'paul',2222222222)
INSERT INTO Users VALUES (75,'george',7575757575)
INSERT INTO Users VALUES (83,'ringo',8383838383)
INSERT INTO Messages VALUES (2,1,GETDATE(),'Yesterday')
INSERT INTO Messages VALUES (1,2,GETDATE(),'hello')
INSERT INTO Messages VALUES (75,83,GETDATE(),'yellow')
INSERT INTO Messages VALUES (75,83,GETDATE(),'hello')

您应该能够在发送消息时打招呼在一对以上的用户之间。

You should be able to get hello as your message sent between more than 1 pair of users.

编辑：我用正确的答案更新了上面的内容，以显示每对用户对于每条消息都是唯一的。同样，为每对用户创建一个groupID可能是一个好主意。然后，您可以将任意数量的用户添加到该groupID。参见此处以了解想法： http://sqlfiddle.com/#!9/fbc2e2/3

I updated the above with the correct answer to show that each pair of users is unique for each message. Also, it may be a good idea to create a groupID for every pair of users. Then you can add as many users as you want to that groupID. See here for an idea: http://sqlfiddle.com/#!9/fbc2e2/3

这篇关于与SQL查询混淆的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！