mysql - 从单个 table 匹配战斗机

我有一张有拳击记录的桌子，我需要根据级别、体重、性别和拳击运动的类型进行相应的匹配。这是桌子的结构

CREATE TABLE `tfc_sparring_requests_athletes` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `fullname` varchar(300) DEFAULT NULL,
  `birthdate` date DEFAULT NULL,
  `weight` float DEFAULT NULL,
  `fightsport` int(11) DEFAULT NULL,
  `sex` int(11) DEFAULT NULL,
  `level` int(11) DEFAULT NULL,
  `teamid` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=31 DEFAULT CHARSET=utf8

我试着做这样一个查询以便与战斗机匹配

select a.fullname,b.fullname,a.weight
    from tfc_sparring_requests_athletes a ,
             tfc_sparring_requests_athletes b
where a.weight = b.weight
    and a.fightsport = b.fightsport
    and a.level =b.level
    and a.sex = b.sex
    and a.fullname != b.fullname

结果是

fighter23   fighter1    78
fighter6    fighter5    70
fighter5    fighter6    70
fighter1    fighter23   78
fighter26   fighter25   57
fighter25   fighter26   57
fighter28   fighter27   80
fighter27   fighter28   80

正如你所看到的，战斗机1与战斗机23在第1排，但在第4排战斗机再次出现。其他拳手也会重复他们的比赛，比如第2排和第3排的5号和6号拳手。我怎样才能避免这种重复，使我可以显示唯一的匹配？

最佳答案

无论如何，我找到了基于这个问题的解决方案How to select distinct pairs in MySQL join (same table) with transitivity?

select MIN(a.id),b.id,a.fullname name1,b.fullname name2,a.weight
    from tfc_sparring_requests_athletes a ,tfc_sparring_requests_athletes b
where a.weight = b.weight
    and a.fightsport = b.fightsport
    and a.level =b.level
    and a.sex = b.sex
    and a.fullname != b.fullname
    AND b.id > a.id
GROUP BY b.id;

关于mysql - 从单个 table 匹配战斗机，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/23097356/