MySQL加入 "connected"间隔

Question

假设我有 table

teach_subject(teacher_id, subject_id, min_grade_of_school, max_grade_of_school, color_in_timetable)

示例数据:

teacher_id  subject_id  min_grade_of_school  max_grade_of_school  color_in_timetable
         1           2                    1                    4               #A00
         1           2                    5                    6               #0A0
         1           2                    9                   11               #00A
         1           3                    1                    7               #AA0

不允许min_grad_of_school > max_grad_of_school , 但也许它们是相等的。
对于给定的元组 (t_id_new, s_id_new, min_grade_new, max_grade_new, color_new) 也是不允许的存在另一个元组 (t_id, s_id, min_grade, max_grade, color)在 table 上拿着
t_id_new = t_id和 s_id_new = s_id和
( min_grade <= min_grade_new <= max_grade或 min_grade <= max_grade_new <= max_grade )

因此对于给定的示例，一个新的元组，如 (1,2,6,11,#FFF)或 (1,2,2,7,#FFF)不可能。

到目前为止没有问题。

现在我想忽略颜色并加入“连接”行，即如果老师 1 从一年级到四年级，从五年级到六年级教科目 2，你也可以说他教1至6年级科目2。
所以我想“加入”元组(1,2,1,4)和 (1,2,5,6)至 (1,2,1,6)但我不想加入 (1,2,4,5) 和 (1,2,9,11)，因为 5 和 9 之间有一个(整数)差距。

我只是不知道是否有办法用 MySQL 做到这一点。目前我只是选择所有数据并使用 PHP 编辑所选数据。有没有一种 MySQL 方法可以直接选择我想要的或者应该坚持使用 PHP？

编辑

示例结果(针对上述示例数据):

teacher_id  subject_id  min_grade_of_school  max_grade_of_school  color_in_timetable
         1           2                    1                    6               #A00
         1           2                    9                   11               #00A
         1           3                    1                    7               #AA0

编辑2

也许可以使用存储过程？

Answer 1

DROP TABLE IF EXISTS teach_subject;

CREATE TABLE teach_subject
(teacher_id INT NOT NULL
,subject_id INT NOT NULL
,min_g INT NOT NULL
,max_g INT NOT NULL
,color_in_timetable CHAR(4) NOT NULL
,PRIMARY KEY(teacher_id,subject_id,min_g)
);

INSERT INTO teach_subject VALUES
(1,2,1,4,'#A00'),
(1,2,5,6,'#0A0'),
(1,2,9,11,'#00A'),
(1,3,1, 7,'#AA0');

SELECT a.teacher_id
     , a.subject_id
     , a.min_g
     , MIN(c.max_g) max_g
     , a.color_in_timetable
  FROM teach_subject a
  LEFT 
  JOIN teach_subject b 
    ON b.teacher_id = a.teacher_id
   AND b.subject_id = a.subject_id
   AND b.min_g = a.max_g - 1
  LEFT 
  JOIN teach_subject c 
    ON c.teacher_id = a.teacher_id
   AND c.subject_id = a.subject_id
   AND c.min_g >= a.min_g 
  LEFT 
  JOIN teach_subject d 
    ON d.teacher_id = a.teacher_id
   AND d.subject_id = a.subject_id
   AND d.min_g = c.max_g + 1
 WHERE b.teacher_id IS NULL 
   AND c.teacher_id IS NOT NULL
   AND d.teacher_id IS NULL
 GROUP 
    BY a.teacher_id,a.subject_id,a.min_g; 

+------------+------------+-------+-------+--------------------+
| teacher_id | subject_id | min_g | max_g | color_in_timetable |
+------------+------------+-------+-------+--------------------+
|          1 |          2 |     1 |     6 | #A00               |
|          1 |          2 |     9 |    11 | #00A               |
|          1 |          3 |     1 |     7 | #AA0               |
+------------+------------+-------+-------+--------------------+