首页
学习
活动
专区
圈层
工具
发布
社区首页 >问答首页 >与SQL查询混淆

问与SQL查询混淆
EN

Stack Overflow用户
提问于 2019-05-02 10:56:43
回答 4查看 433关注 0票数 0

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

代码语言:javascript
复制
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之间发送,则不应该显示给结果。

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

代码语言:javascript
复制
SELECT message 
FROM Messages 
GROUP BY message 
HAVING COUNT(*) > 1

但是,这不会有帮助,因为同一对用户可能会一次又一次地发送相同的消息。我可以同时按发件人、收件人和邮件分组吗?如果是的话,这会给我什么结果?

此外,是否有一种方法可以轻松地用随机值填充这两个表,并自行进行查询以检查它们?

提前感谢!

EN

回答 4

Stack Overflow用户

回答已采纳

发布于 2019-05-02 12:45:11

是的,首先从分组user1、user2和message开始。这为您提供了一条独特的信息:

代码语言:javascript
复制
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的计数。可以使用嵌套查询来执行以下操作:

代码语言:javascript
复制
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

也许从这个开始作为一个测试:

代码语言:javascript
复制
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

票数 1
EN

Stack Overflow用户

发布于 2019-05-02 13:47:19

这将适用于MySql:

代码语言:javascript
复制
SELECT message 
FROM Messages 
GROUP BY message 
HAVING 
  COUNT(distinct least(recipient, sender), greatest(recipient, sender)) > 1

见演示。

票数 2
EN

Stack Overflow用户

发布于 2019-05-06 06:12:47

实现这一目标的最简单方法是使用以下查询:

代码语言:javascript
复制
select message, count(*)
from Messages
group by message
HAVING COUNT(distinct least(recipient, sender), greatest(recipient, sender)) > 1

这改进了forpa的查询,增加了使用该消息的次数。

票数 1
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/55950905

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档