MySQL 关闭数字序列间隙或建议新的序列元素

Question

我的 table 看起来像这样:

 CREATE TABLE `Room` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `branch_id` int(10) unsigned DEFAULT NULL,
  `course_id` int(10) unsigned DEFAULT NULL,
  `occupied_hours` tinyint(1) DEFAULT '0',
  `number` tinyint(2) DEFAULT NULL,
  PRIMARY KEY (`id`),
) ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=utf8

这里的数字就像计数器结合了另外 2 个值:branch_id、course_id。因此，例如，如果表格看起来像这样:

... | branch_id | course_id | number | ...
        1             1           1
        1             1           3

我想做的是，当我插入该表以填充数字字段之间的空隙(在本例中为 2)或给出 +1 最大值(如果空隙不存在)，如本例中的 4如果还有 2 个值。

如果有一种方法可以将其创建为过程，那就太好了。不长sql。我怎样才能做到这一点？有任何准备就绪的功能吗？

Answer 1

一个简单的 LEFT JOIN 就可以了

SELECT Coalesce(Min(number),1) AS number, branch_id, course_id
FROM
  (SELECT r1.number -1 AS number, r1.branch_id, r1.course_id
  FROM `Room` AS r1
  LEFT JOIN `Room` AS r2
    ON r1.branch_id = r2.branch_id 
      AND r1.course_id = r2.course_id
      AND (r1.number - 1) = r2.number
  WHERE r1.number > 1 AND r2.number IS NULL
  UNION ALL
  SELECT Max(number) + 1, branch_id, course_id
  FROM `Room`
  GROUP BY branch_id, course_id) AS q
GROUP BY branch_id, course_id