把下表的資料根據num的數字拆分成多行，如num為2就要變成01,02,為3就要變成01,02,03
原表
code num
01 2
02 3
結果如下
序號 code num2
1 01 01
2 01 02
3 02 01
3 02 02
4 02 03
測驗資料
DROP TABLE IF EXISTS `test`;
CREATE TABLE `test` (
`Id` int(11) NOT NULL AUTO_INCREMENT,
`code` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`num` int(11) NULL DEFAULT NULL,
PRIMARY KEY (`Id`) USING BTREE
) ENGINE = MyISAM AUTO_INCREMENT = 10 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
INSERT INTO `test` VALUES (8, '01', 2);
INSERT INTO `test` VALUES (9, '02', 3);
uj5u.com熱心網友回復：
e.g.with recursive CTE
as
(
select 1 as ID
union all
select ID+1 as ID from CTE where ID<100
)
select row_number()over(order by a.id) as rn
,a.code
,right(row_number()over(partition by a.code order by b.id)+100,2) as num2
from test as a inner join CTE as b on b.ID<=a.num;
/*
# rn, code, num2
'1', '01', '01'
'2', '01', '02'
'3', '02', '01'
'4', '02', '02'
'5', '02', '03'
*/
uj5u.com熱心網友回復：
這序號重復是不是貼差了序號 code num2
1 01 01
2 01 02
3 02 01
3 02 02
4 02 03
uj5u.com熱心網友回復：
是的，謝謝。轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/165383.html
標籤：MySQL