有如下表:
id sender receive
1 1 7
2 7 1
3 6 1
4 1 6
5 6 1
6 1 7
7 1 7
我想得到如下结果
id sender receive
1 1 7
3 6 1就是只得到sender和receive没有交互的一条记录
求解..感激不尽呀.
附sql语句
CREATE TABLE IF NOT EXISTS `cym_message` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`sender` int(10) unsigned NOT NULL COMMENT '发件人ID',
`receive` int(10) unsigned NOT NULL COMMENT '收件人ID',
PRIMARY KEY (`id`),
KEY `sender` (`sender`),
KEY `receive` (`receive`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;INSERT INTO `cym_message` (`id`, `sender`, `receive`) VALUES(1, 1, 7),
INSERT INTO `cym_message` (`id`, `sender`, `receive`) VALUES(2, 7, 1),
INSERT INTO `cym_message` (`id`, `sender`, `receive`) VALUES(3, 6, 1),
INSERT INTO `cym_message` (`id`, `sender`, `receive`) VALUES(4, 1, 6),
INSERT INTO `cym_message` (`id`, `sender`, `receive`) VALUES(5, 6, 1),
INSERT INTO `cym_message` (`id`, `sender`, `receive`) VALUES(6, 1, 7),
INSERT INTO `cym_message` (`id`, `sender`, `receive`) VALUES(7, 1, 7)
id sender receive
1 1 7
2 7 1
3 6 1
4 1 6
5 6 1
6 1 7
7 1 7
我想得到如下结果
id sender receive
1 1 7
3 6 1就是只得到sender和receive没有交互的一条记录
求解..感激不尽呀.
附sql语句
CREATE TABLE IF NOT EXISTS `cym_message` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`sender` int(10) unsigned NOT NULL COMMENT '发件人ID',
`receive` int(10) unsigned NOT NULL COMMENT '收件人ID',
PRIMARY KEY (`id`),
KEY `sender` (`sender`),
KEY `receive` (`receive`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;INSERT INTO `cym_message` (`id`, `sender`, `receive`) VALUES(1, 1, 7),
INSERT INTO `cym_message` (`id`, `sender`, `receive`) VALUES(2, 7, 1),
INSERT INTO `cym_message` (`id`, `sender`, `receive`) VALUES(3, 6, 1),
INSERT INTO `cym_message` (`id`, `sender`, `receive`) VALUES(4, 1, 6),
INSERT INTO `cym_message` (`id`, `sender`, `receive`) VALUES(5, 6, 1),
INSERT INTO `cym_message` (`id`, `sender`, `receive`) VALUES(6, 1, 7),
INSERT INTO `cym_message` (`id`, `sender`, `receive`) VALUES(7, 1, 7)
解决方案 »
- mysql 数据导入出错 如何全部回滚
- 字符求助
- 才开始学mysql,为什么创建存储过程老提示stored routine?
- 如何对正在运行的master数据库创建slave节点
- 怎么用代码实现指定MYSQL数据库备份??
- 用pgadmin连不上PostgrSQL数据库!请高手帮忙!!急!!!
- 求一条mysql语句删除表里字段重复的数据,只保留1条
- mysql的event执行频率与单次执行时间问题
- 安装完Mysql后,用root登入,但是没有mysql库,
- 每次都在creating sort index 这个步骤卡死 怎么办
- 菜鸟求助,MYSQL不同进入方式显示的用户不一样
- MySQL cluster问题 急
from (
select id ,sender ,receive
from cym_message
union all
select id ,receive,sender
from cym_message
)
where sender<receive
group by sender,receive
| id | sender | receive |
+----+--------+---------+
| 3 | 6 | 1 |
| 1 | 1 | 7 |
+----+--------+---------+
2 rows in set (0.00 sec)
1 1 7
2 7 1不算交互?
就是交互只取一条,谢谢各位前辈由于效率和分组排序的问题.
最终我还是建了个有unique字段(包含排序后的sender,receive)的表,还排除重复!