問題描述：

如下圖所示：

根據(jù)sendid和receiveid進行數(shù)據(jù)去重，就是說其實id=22的數(shù)據(jù)和id=25的數(shù)據(jù)是一條數(shù)據(jù)。去重后取時間最新的那條數(shù)據(jù)

sql語句應該怎么寫呢？

解決方法：

CREATE TABLE `message` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `sendid` int(11) NOT NULL DEFAULT '0',
  `receiveid` int(11) NOT NULL DEFAULT '0',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=latin1;

INSERT INTO `message` (`id`, `sendid`, `receiveid`, `create_time`)
VALUES
    (1, 321, 3, '2017-01-13 10:23:03'),
    (2, 322, 4, '2017-01-13 10:23:11'),
    (3, 123123, 9, '2017-01-13 10:23:25'),
    (4, 0, 0, '2017-01-13 10:22:54'),
    (5, 4, 321, '2017-01-13 10:22:54'),
    (6, 4, 322, '2017-01-13 10:23:17'),
    (7, 9, 12232, '2017-01-13 10:23:30'),
    (8, 0, 0, '2017-01-13 11:29:42');

SELECT *
FROM   message m3
WHERE  id NOT IN (#查詢需要去重的id
           select DISTINCT m1.id
           FROM            message AS m1
           INNER JOIN      message AS m2
           WHERE           m1.id != m2.id #過濾掉自身關聯(lián)
           AND             ((
                                                           m1.receiveid = m2.sendid
                                           AND             m1.sendid = m2.receiveid)
                           OR              (
                                                           m1.sendid = m2.sendid
                                           AND             m1.receiveid = m2.receiveid ) )
           AND             m1.create_time < m2.create_time #
           GROUP BY        m1.id,
                           m2.id);