mysql按顺序选择group by order by

ubby3x7f 于 2021-07-27 发布在 Java

关注(0)|答案(1)|浏览(251)

我有这张table：
信息

/*Table structure for table `messages` */

CREATE TABLE `messages` (
  `id` int(255) NOT NULL AUTO_INCREMENT,
  `fromperson` varchar(255) NOT NULL,
  `sent` datetime(6) NOT NULL,
  `msgread` int(2) DEFAULT 0,
  `content` text DEFAULT NULL,
  `toperson` varchar(255) NOT NULL,
  `route` int(2) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=75508 DEFAULT CHARSET=utf8mb4;

/*Data for the table `messages` */

insert  into `messages`(`id`,`fromperson`,`sent`,`msgread`,`content`,`toperson`,`route`) values 
(75477,'jaritje','2020-07-31 11:47:59.000000',1,'helemaal niks :)','anaisje',0),
(75478,'jaritje','2020-07-31 11:48:25.000000',1,'wdj','anaisje',1),
(75479,'jaritje','2020-05-25 12:57:27.000000',1,'cv','anaisje',0),
(75501,'jaritje','2020-05-25 13:38:31.000000',1,'gmj*','anaisje',1),
(75502,'jaritje','2020-05-25 13:38:48.000000',1,'gm','anaisje',1),
(75503,'jaritje','2020-05-26 16:53:27.000000',1,'hgh','anaisje',0),
(75504,'jaritje','2020-05-26 17:05:27.000000',1,'hey\r\n','anaisje',1),
(75505,'jaritje','2020-05-26 18:14:03.000000',1,'hallo','anaisje',0),
(75507,'jaritje','2020-07-22 12:57:27.000000',1,'TEST   ','saartje',1);

现在我要选择每个人最近的每条消息。所以最新的消息是“anaisje”和“saartje”。
所以我想要标识为75478和75507的行。
我在网上看到

SELECT * FROM (SELECT * FROM messages ORDER BY sent DESC) AS person WHERE fromperson = ?

应该有用，但对我来说不是。。。
有人能帮我吗？提前谢谢，
贾里

sql mysql select

来源：https://stackoverflow.com/questions/63189963/mysql-select-group-by-order-by

1条答案

按热度按时间

k3bvogb11#

要按发送方和接收方获取最新的数据行，可以使用如下自联接：

select * 
from messages msg
where sent = (select max(sent) 
              from messages msg_
              where msg_.fromperson = msg.fromperson
               and msg_.toperson = msg.toperson)

看看这把小提琴是怎么用的

赞(0）回复(0）举报 2021-07-27

我来回答

mysql按顺序选择group by order by

1条答案

相关问题

热门标签

最新问答