让我们假设我们有一个用于聊天应用程序的数据库:
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之间发送,则不应该显示给结果。
我考虑将至少出现两次的所有消息分组如下:
SELECT message
FROM Messages
GROUP BY message
HAVING COUNT(*) > 1但是,这不会有帮助,因为同一对用户可能会一次又一次地发送相同的消息。我可以同时按发件人、收件人和邮件分组吗?如果是的话,这会给我什么结果?
此外,是否有一种方法可以轻松地用随机值填充这两个表,并自行进行查询以检查它们?
提前感谢!
发布于 2019-05-02 12:45:11
是的,首先从分组user1、user2和message开始。这为您提供了一条独特的信息:
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然后,根据消息从该结果组返回大于1的计数。可以使用嵌套查询来执行以下操作:
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也许从这个开始作为一个测试:
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')当您的消息在1对以上的用户之间发送时,您应该能够得到hello。
编辑:我用正确的答案更新了上面的内容,以显示每对用户对于每条消息都是独一无二的。另外,为每一对用户创建一个groupID可能是个好主意。然后,您可以向该groupID添加任意数量的用户。请看这里的一个想法:http://sqlfiddle.com/#!9/fbc2e2/3
发布于 2019-05-02 13:47:19
这将适用于MySql:
SELECT message
FROM Messages
GROUP BY message
HAVING
COUNT(distinct least(recipient, sender), greatest(recipient, sender)) > 1见演示。
发布于 2019-05-06 06:12:47
实现这一目标的最简单方法是使用以下查询:
select message, count(*)
from Messages
group by message
HAVING COUNT(distinct least(recipient, sender), greatest(recipient, sender)) > 1这改进了forpa的查询,增加了使用该消息的次数。
https://stackoverflow.com/questions/55950905
复制相似问题