时间戳

首页 > 解决方案 > MySQL：如何从里程周期中获取维护计划

range - MySQL：如何从里程周期中获取维护计划

问题描述

我有一个 MySQL 表，描述了铁路车辆的维护周期（大修类型和周期，以公里为单位）：

CREATE TABLE `cycle_test` (
 `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
 `type` varchar(2) COLLATE utf8_spanish_ci NOT NULL,
 `km` int(11) unsigned NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_spanish_ci

我已按如下方式填充此表：

INSERT INTO `cycle_test` (`id`, `type`, `km`) VALUES (NULL, 'R1', '12000'), (NULL, 'R2', '24000'), (NULL, 'R3', '72000'), (NULL, 'R4', '144000');
SELECT type, km FROM cycle_test;
+------+--------+
| type | km     |
+------+--------+
| R1   |  12000 |
| R2   |  24000 |
| R3   |  72000 |
| R4   | 144000 |
+------+--------+

目标是获得维护计划，在整个周期中进行所有大修，以及每次大修的里程，同时考虑到当不同类型的大修重合时，排名最高的那个（R4>R3 > R2 > R1) 取代排名最低的那些，如下所示：

+--------+------+
| km     | type |
+--------+------+
| 12000  | R1   |
| 24000  | R2   |
| 36000  | R1   |
| 48000  | R2   |
| 60000  | R1   |
| 72000  | R3   |
| 84000  | R1   |
| 96000  | R2   |
| 108000 | R1   |
| 120000 | R2   |
| 132000 | R1   |
| 144000 | R4   |
+--------+------+

这个问题我已经在 PHP 中解决了，但我正在寻找一个纯粹的 MySQL 解决方案，可以使用会话变量或存储过程。（可能的）解决方案必须针对不同的维护周期进行扩展（非硬编码）。提前感谢您的帮助和/或提示。

标签： rangemysql-5.5

解决方案

好吧，在 MySQL 文档的帮助下，结果比我想象的要容易。首先，我创建了日程表（在生产中，它将是一个临时表）：

CREATE TABLE `schedule_test` (
 `type` varchar(2) COLLATE utf8_spanish_ci NOT NULL,
 `km` int(11) unsigned NOT NULL,
 UNIQUE KEY `km` (`km`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_spanish_ci

允许检测相同里程的不同等级的大修的UNIQUE KEY km并发性。

然后我在数据库中创建了以下存储过程：

BEGIN
/* Declaration of variables, cursors and handlers */
DECLARE done INT DEFAULT 0;
DECLARE vTip CHAR(16);
DECLARE vKm, acKm INT;
DECLARE vFinal INT DEFAULT 0;
DECLARE cur1 CURSOR FOR SELECT type, km FROM cycle_test;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
/* end declaration */
TRUNCATE schedule_test; -- Get rid of any data
SELECT MAX(km) FROM cycle_test INTO vFinal; -- Find the final mileage of the cycle
OPEN cur1;
REPEAT
    FETCH cur1 INTO vTip, vKm; -- Read record from overhaul cycle
    IF NOT done THEN
        SET acKm = 0; -- Initialize mileage
        REPEAT
            SET acKm = acKm + vKm; -- Accumulate mileage
            INSERT INTO schedule_test (type, km) VALUES (vTip, acKm)
            ON DUPLICATE KEY UPDATE type=VALUES(type); -- Insert schedule; update type if an overhaul already exists
        UNTIL acKm = vFinal END REPEAT; -- until reach the end of cycle
    END IF;
UNTIL done END REPEAT;
CLOSE cur1;
END

这是我想要的结果，运行程序后：

mysql> select * from schedule_test;
+------+--------+
| type | km     |
+------+--------+
| R1   |  12000 |
| R2   |  24000 |
| R1   |  36000 |
| R2   |  48000 |
| R1   |  60000 |
| R3   |  72000 |
| R1   |  84000 |
| R2   |  96000 |
| R1   | 108000 |
| R2   | 120000 |
| R1   | 132000 |
| R4   | 144000 |
+------+--------+
12 rows in set (0.00 sec)

推荐阅读