查找最近距离训练师的查询

Question

表名称：course_trainer_combination

创建表查询语句：

create table `course_trainer_combination` (
    `id` double ,
    `course` varchar (150),
    `trainer` varchar (150),
    `distance` float 
); 
insert into `course_trainer_combination` (`id`, `course`, `trainer`, `distance`) values('1','Course A','Trainer A','110.00');
insert into `course_trainer_combination` (`id`, `course`, `trainer`, `distance`) values('2','Course A','Trainer B','105.00');
insert into `course_trainer_combination` (`id`, `course`, `trainer`, `distance`) values('3','Course A','Trainer C','115.00');
insert into `course_trainer_combination` (`id`, `course`, `trainer`, `distance`) values('4','Course B','Trainer A','112.00');
insert into `course_trainer_combination` (`id`, `course`, `trainer`, `distance`) values('5','Course B ','Trainer B','108.00');
insert into `course_trainer_combination` (`id`, `course`, `trainer`, `distance`) values('6','Course B','Trainer C','109.00');
insert into `course_trainer_combination` (`id`, `course`, `trainer`, `distance`) values('7','Course C','Trainer A','124.00');
insert into `course_trainer_combination` (`id`, `course`, `trainer`, `distance`) values('8','Course C','Trainer B','128.00');
insert into `course_trainer_combination` (`id`, `course`, `trainer`, `distance`) values('9','Course C','Trainer C','121.00');

我的表格长这样

期望的结果：

2 | Course A | Trainer B | 105
6 | Course B | Trainer C | 109
7 | Course C | Trainer A | 124

- Techseria

---

似乎非常不太可能 id 会成为双精度！？！将距离设为浮点型也有点奇怪，但并不完全不合理。 - Strawberry

为什么期望的结果是“期望的”？你已经尝试过什么了吗？ - Strawberry

换句话说，为什么是“109”？ - Strawberry

如果您需要最小距离，那么为什么在课程-B中应该是B教练，而不是C教练？ - Vijiy

这是因为B教练正在教授A课程，实际上他不能在同一天甚至同一时间教授B课程。很抱歉我没有提供完整的情况。基本的事情是，我想在同一天和同一时间找到最近的教练来教授一门课程。所以如果B教练已经在某一天教授A课程，他实际上不能被分配到B课程。因此，对于B课程，我必须寻找另一个最近的教练，因此将C教练分配给B课程。希望这更有帮助。 - Techseria

Answer 1

你可以使用相关子查询。

select ct.*
from course_trainer_combination ct
where ct.distance = (select min(ct1.distance) 
                     from course_trainer_combination ct1 
                     where ct1.course = ct.course 
                    );

如果您使用的是更新版本，则可以使用分析函数：

select ct.*
from (select ct.*, 
             rank() over (partition by ct.course order by ct.distance) as seq
      from course_trainer_combination ct
     ) ct
where ct.seq = 1;

- Yogesh Sharma

---

谢谢回复，我正在使用MYSQL 5.7，所以分析函数不起作用。第一个查询将返回整个表的数据。 - Techseria

@Techseria. . . 在子查询中修复了使用外部表t别名的问题。 - Yogesh Sharma

Answer 2

另一种方法可以像下面这样。

select  id, 
        m.course, 
        trainer, 
        m.distance 
from   course_trainer_combination m 
       inner join (select course, 
                          Min(distance) distance
                   from   course_trainer_combination 
                   group by course)t 
               on t.course = m.course 
                  and t.distance = m.distance 

注意：如果两个教练在一门课程中的距离相同，将显示两条记录。

- PSK

---

感谢您快速回复。结果如下所示：2 | 课程A | 教练B | 105 5 | 课程B | 教练B | 109 7 | 课程C | 教练C | 121我的要求是：2 | 课程A | 教练B | 105 6 | 课程B | 教练C | 109 7 | 课程C | 教练A | 124代码 - Techseria

你需要解释为什么第二条记录是109而不是108？ - PSK

那是因为B教练正在教授A课程，实际上他不能在同一天更不用说同一时间教授B课程。很抱歉我没有呈现完整的情况。基本的事情是我想在同一天和同一时间找到最近的教练来教授一门课程。所以如果B教练已经在某一天教授A课程，他就不能被分配到B课程了。因此，对于B课程，我必须寻找另一个最近的教练，因此将C教练分配给B课程。希望这更有帮助。 - Techseria

Answer 3

一种解决方法是：

• 跟踪分配给每个课程的讲师，并且
• 选择符合以下条件的行：
  • 讲师尚未分配到课程
  • 没有其他相同课程的行，其中包含尚未选择的讲师

使用mysql用户变量来跟踪已选择的讲师，并使用FIND_IN_SET()检查讲师是否已经分配到课程：

SET @selectedTrainers :=''; 
SELECT *, @selectedTrainers := CONCAT(@selectedTrainers, ',', a.trainer) AS selectedTrainers
FROM `course_trainer_combination` a
where NOT EXISTS (
      SELECT 1
      FROM `course_trainer_combination` b
      WHERE b.course = a.course
      AND a.distance > b.distance
      AND FIND_IN_SET(b.trainer, @selectedTrainers) = 0
      )
  AND FIND_IN_SET(a.trainer, @selectedTrainers) = 0

- Jannes Botis