mysql - 根据sql另一列中的值增加值

我有一个表，其中包含服务id、服务名称、权重起始范围、权重结束范围和增量。

service_id service_name weight_start_range  Weight_end_range    increment_by
sr1        Service1           0                    500             100
sr2        Service2           200                  300              0
sr3        Service3           0                    1000            500
sr4        Service4           0                    250              0
sr5        Service5           50                   100              0

我想从这个表中获取数据，并使用SQL查询创建一个表：

service_id  service_name    weight_start_range  Weight_end_range
sr1         Service1                0               100
sr1         Service1                100             200
sr1         Service1                200             300
sr1         Service1                300             400
sr1         Service1                400             500
sr2         Service2                200             300
sr3         Service3                0               500
sr3         Service3                500             1000
sr4         Service4                0               250
sr5         Service5                50              100

有人能帮我吗？
我无法设计表，所以附上图像看结构。

最佳答案

考虑到您的需求，我首先想到的是编写子查询，但提供循环功能是一项复杂的任务，因此我决定为它创建过程。
数据库设置：

CREATE TABLE `servicedata` (
  `service_id` varchar(255) NOT NULL,
  `serviceName` varchar(255) DEFAULT NULL,
  `weight_start_range` double DEFAULT NULL,
  `weight_end_range` double DEFAULT NULL,
  `increment_by` double DEFAULT NULL,
  PRIMARY KEY (`service_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

种子数据：

insert into `servicedata` (`service_id`, `serviceName`, `weight_start_range`, `weight_end_range`, `increment_by`) values('sr1','srvice1','0','500','100');
insert into `servicedata` (`service_id`, `serviceName`, `weight_start_range`, `weight_end_range`, `increment_by`) values('sr2','service2','200','300','0');
insert into `servicedata` (`service_id`, `serviceName`, `weight_start_range`, `weight_end_range`, `increment_by`) values('sr3','service3','0','1000','500');
insert into `servicedata` (`service_id`, `serviceName`, `weight_start_range`, `weight_end_range`, `increment_by`) values('sr4','service4','0','250','0');

servicedata表包含以下数据：

service_id  serviceName  weight_start_range  weight_end_range  increment_by
----------  -----------  ------------------  ----------------  ------------
sr1         srvice1                       0               500           100
sr2         service2                    200               300             0
sr3         service3                      0              1000           500
sr4         service4                      0               250             0

过程customOutPut：

DELIMITER //
DROP PROCEDURE IF EXISTS customOutPut//

CREATE PROCEDURE customOutPut()
BEGIN
DECLARE v_finished INTEGER DEFAULT 0;
DECLARE serviceid VARCHAR(50) ;
DECLARE servicename VARCHAR(50) ;
DECLARE START INTEGER ;
DECLARE END INTEGER ;
DECLARE incrementby INTEGER ;
DECLARE WEIGHT_END INTEGER DEFAULT 0;
DECLARE endRange INTEGER;
DECLARE startRange INTEGER;

DECLARE selectCursor CURSOR FOR
    SELECT service_id,servicename , weight_START_range , weight_end_range ,increment_by
    FROM servicedata;

DECLARE CONTINUE HANDLER
FOR NOT FOUND SET v_finished = 1;

DROP TABLE IF EXISTS DATA_SPREAD_WITH_INCREMENT_BY ;
CREATE TABLE DATA_SPREAD_WITH_INCREMENT_BY(service_id VARCHAR(50) ,servicename VARCHAR(50), weight_START_range INTEGER, weight_end_range INTEGER );

OPEN selectCursor;

getData: LOOP

FETCH selectCursor INTO serviceid,servicename,START,END,incrementby ;

IF v_finished =1 THEN
  LEAVE getData;
END IF ;

IF incrementby = 0 THEN
   INSERT INTO DATA_SPREAD_WITH_INCREMENT_BY VALUES (serviceid,servicename,START,END);

ELSE
 SET endRange =START;
 SET startRange = START;
    spreadData: LOOP

    SET endRange = endRange+incrementby ;
    IF endrange > END THEN
        LEAVE spreadData;
    END IF;

    INSERT INTO DATA_SPREAD_WITH_INCREMENT_BY VALUES (serviceid,servicename,startRange,endRange);

    SET startRange = startRange+incrementby ;

    END LOOP;

END IF ;

END LOOP getData;

END  //
DELIMITER ;

您应该将该过程称为CALL customOutPut()
在SQL下面用fire递增的数据扩展中查看可用的数据

SELECT * FROM `data_spread_with_increment_by`

表数据按增量排列

service_id  servicename  weight_START_range  weight_end_range
----------  -----------  ------------------  ----------------
sr1         (NULL)                        0               100
sr1         (NULL)                      100               200
sr1         (NULL)                      200               300
sr1         (NULL)                      300               400
sr1         (NULL)                      400               500
sr2         (NULL)                      200               300
sr3         (NULL)                        0               500
sr3         (NULL)                      500              1000
sr4         (NULL)                        0               250

我已经提供了这个程序作为指导，我可能错过了一些边缘案件，但我相信你一定可以找出他们。
请提出你的建议，这个程序是否适合你。