MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE `bom_tmp`  (
  `item` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '父階件號',
  `part` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '子階件號',
  PRIMARY KEY (`item`, `part`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of bom_tmp
-- ----------------------------
INSERT INTO `bom_tmp` VALUES ('11401-3412-1', '90514-20299O');
INSERT INTO `bom_tmp` VALUES ('11409-3320', '11409-3321-A');
INSERT INTO `bom_tmp` VALUES ('11409-3320', '11409-3322');
INSERT INTO `bom_tmp` VALUES ('11409-3416-1', 'L608M');
INSERT INTO `bom_tmp` VALUES ('11413-2400-1', '11413-3400-1');
INSERT INTO `bom_tmp` VALUES ('11413-2400-1', '11502-3502-A');
INSERT INTO `bom_tmp` VALUES ('11413-3400-1', '11409-3416-1');
INSERT INTO `bom_tmp` VALUES ('11413-3400-1', '11413-3410-1');
INSERT INTO `bom_tmp` VALUES ('11413-3410-1', '11401-3412-1');
INSERT INTO `bom_tmp` VALUES ('11413-3410-1', '11413-3411-1');
INSERT INTO `bom_tmp` VALUES ('11413-3411-1', '90505-45238O');
INSERT INTO `bom_tmp` VALUES ('11426-2300-A', '11409-3320');
INSERT INTO `bom_tmp` VALUES ('11426-2300-A', '11409-3332-B');
INSERT INTO `bom_tmp` VALUES ('11426-2300-A', '11409-3333-B');
INSERT INTO `bom_tmp` VALUES ('11426-2300-A', '11426-2311-2');
INSERT INTO `bom_tmp` VALUES ('11426-2300-A', '14402-3335-B');
INSERT INTO `bom_tmp` VALUES ('11426-2300-A', '41101-3334');
INSERT INTO `bom_tmp` VALUES ('11426-2311-2', '11426-3313');
INSERT INTO `bom_tmp` VALUES ('11426-3207', '90512-16348E');
INSERT INTO `bom_tmp` VALUES ('11433-2000-D', '11426-2950-A');
INSERT INTO `bom_tmp` VALUES ('11433-2000-D', '11433-2100-C');
INSERT INTO `bom_tmp` VALUES ('11433-2100-C', '11101-3503');
INSERT INTO `bom_tmp` VALUES ('11433-2100-C', '11101-3504');
INSERT INTO `bom_tmp` VALUES ('11433-2100-C', '11103-3702');
INSERT INTO `bom_tmp` VALUES ('11433-2100-C', '11413-2400-1');
INSERT INTO `bom_tmp` VALUES ('11433-2100-C', '11413-2501-A');
INSERT INTO `bom_tmp` VALUES ('11433-2100-C', '11413-3400-1');
INSERT INTO `bom_tmp` VALUES ('11433-2100-C', '11426-2300-A');
INSERT INTO `bom_tmp` VALUES ('11433-2100-C', '11433-2200-A');
INSERT INTO `bom_tmp` VALUES ('11433-2100-C', '11502-2520-A');
INSERT INTO `bom_tmp` VALUES ('11433-2100-C', '11502-2531-A');
INSERT INTO `bom_tmp` VALUES ('11433-2100-C', '11502-3611');
INSERT INTO `bom_tmp` VALUES ('11433-2100-C', '11502-3613');
INSERT INTO `bom_tmp` VALUES ('11433-2100-C', '11502-3615');
INSERT INTO `bom_tmp` VALUES ('11433-2100-C', '12501-3621');
INSERT INTO `bom_tmp` VALUES ('11433-2100-C', '12501-3901');
INSERT INTO `bom_tmp` VALUES ('11433-2100-C', '12501-3902-A');
INSERT INTO `bom_tmp` VALUES ('11433-2100-C', '14301-3701');
INSERT INTO `bom_tmp` VALUES ('11433-2100-C', 'CF241249-G01');
INSERT INTO `bom_tmp` VALUES ('11433-2200-A', '11401-3202-A');
INSERT INTO `bom_tmp` VALUES ('11433-2200-A', '11426-3207');
INSERT INTO `bom_tmp` VALUES ('11433-2200-A', '11433-2201');
INSERT INTO `bom_tmp` VALUES ('11433-2200-A', '11433-2P');
INSERT INTO `bom_tmp` VALUES ('11433-2201', '90504-29330E');
INSERT INTO `bom_tmp` VALUES ('11433-2P', '11401-3203');
INSERT INTO `bom_tmp` VALUES ('11433-2P', '11401-3204');
INSERT INTO `bom_tmp` VALUES ('11433-2P', '11433-3209');
INSERT INTO `bom_tmp` VALUES ('11433-2P', '11502-2205');
INSERT INTO `bom_tmp` VALUES ('11502-2520-A', '11502-2521-1');
INSERT INTO `bom_tmp` VALUES ('11502-2520-A', '11502-2522-1');
INSERT INTO `bom_tmp` VALUES ('11502-2520-A', '11502-3523-A');

SET FOREIGN_KEY_CHECKS = 1;

SELECT *
FROM bom_tmp

item	part
11401-3412-1	90514-20299O
11409-3320	11409-3321-A
11409-3320	11409-3322
11409-3416-1	L608M
11413-2400-1	11413-3400-1
11413-2400-1	11502-3502-A
11413-3400-1	11409-3416-1
11413-3400-1	11413-3410-1
11413-3410-1	11401-3412-1
11413-3410-1	11413-3411-1
11413-3411-1	90505-45238O
11426-2300-A	11409-3320
11426-2300-A	11409-3332-B
11426-2300-A	11409-3333-B
11426-2300-A	11426-2311-2
11426-2300-A	14402-3335-B
11426-2300-A	41101-3334
11426-2311-2	11426-3313
11426-3207	90512-16348E
11433-2000-D	11426-2950-A
11433-2000-D	11433-2100-C
11433-2100-C	11101-3503
11433-2100-C	11101-3504
11433-2100-C	11103-3702
11433-2100-C	11413-2400-1
11433-2100-C	11413-2501-A
11433-2100-C	11413-3400-1
11433-2100-C	11426-2300-A
11433-2100-C	11433-2200-A
11433-2100-C	11502-2520-A
11433-2100-C	11502-2531-A
11433-2100-C	11502-3611
11433-2100-C	11502-3613
11433-2100-C	11502-3615
11433-2100-C	12501-3621
11433-2100-C	12501-3901
11433-2100-C	12501-3902-A
11433-2100-C	14301-3701
11433-2100-C	CF241249-G01
11433-2200-A	11401-3202-A
11433-2200-A	11426-3207
11433-2200-A	11433-2201
11433-2200-A	11433-2P
11433-2201	90504-29330E
11433-2P	11401-3203
11433-2P	11401-3204
11433-2P	11433-3209
11433-2P	11502-2205
11502-2520-A	11502-2521-1
11502-2520-A	11502-2522-1
11502-2520-A	11502-3523-A

Id	item	part	No1	No1_Max	No2	No2_Max	Mark
1	11433-2000-D	11426-2950-A	1	6	1	2	├
2	11433-2000-D	11433-2100-C	1	6	2	2	└
3	11433-2100-C	11101-3503	2	6	1	18	├
4	11433-2100-C	11101-3504	2	6	2	18	├
5	11433-2100-C	11103-3702	2	6	3	18	├
6	11433-2100-C	11413-2400-1	2	6	4	18	├
7	11433-2100-C	11413-2501-A	2	6	5	18	├
8	11433-2100-C	11413-3400-1	2	6	6	18	├
9	11433-2100-C	11426-2300-A	2	6	7	18	├
10	11433-2100-C	11433-2200-A	2	6	8	18	├
11	11433-2100-C	11502-2520-A	2	6	9	18	├
12	11433-2100-C	11502-2531-A	2	6	10	18	├
13	11433-2100-C	11502-3611	2	6	11	18	├
14	11433-2100-C	11502-3613	2	6	12	18	├
15	11433-2100-C	11502-3615	2	6	13	18	├
16	11433-2100-C	12501-3621	2	6	14	18	├
17	11433-2100-C	12501-3901	2	6	15	18	├
18	11433-2100-C	12501-3902-A	2	6	16	18	├
19	11433-2100-C	14301-3701	2	6	17	18	├
20	11433-2100-C	CF241249-G01	2	6	18	18	└
21	11413-2400-1	11413-3400-1	3	6	1	2	├
22	11413-2400-1	11502-3502-A	3	6	2	2	└
23	11413-3400-1	11409-3416-1	3	6	1	2	├
24	11413-3400-1	11413-3410-1	3	6	2	2	└
25	11426-2300-A	11409-3320	3	6	1	6	├
26	11426-2300-A	11409-3332-B	3	6	2	6	├
27	11426-2300-A	11409-3333-B	3	6	3	6	├
28	11426-2300-A	11426-2311-2	3	6	4	6	├
29	11426-2300-A	14402-3335-B	3	6	5	6	├
30	11426-2300-A	41101-3334	3	6	6	6	└
31	11433-2200-A	11401-3202-A	3	6	1	4	├
32	11433-2200-A	11426-3207	3	6	2	4	├
33	11433-2200-A	11433-2201	3	6	3	4	├
34	11433-2200-A	11433-2P	3	6	4	4	└
35	11502-2520-A	11502-2521-1	3	6	1	3	├
36	11502-2520-A	11502-2522-1	3	6	2	3	├
37	11502-2520-A	11502-3523-A	3	6	3	3	└
38	11409-3320	11409-3321-A	4	6	1	2	├
39	11409-3320	11409-3322	4	6	2	2	└
40	11409-3416-1	L608M	4	6	1	1	└
41	11413-3400-1	11409-3416-1	4	6	1	2	├
42	11413-3400-1	11413-3410-1	4	6	2	2	└
43	11413-3410-1	11401-3412-1	4	6	1	2	├
44	11413-3410-1	11413-3411-1	4	6	2	2	└
45	11426-2311-2	11426-3313	4	6	1	1	└
46	11426-3207	90512-16348E	4	6	1	1	└
47	11433-2201	90504-29330E	4	6	1	1	└
48	11433-2P	11401-3203	4	6	1	4	├
49	11433-2P	11401-3204	4	6	2	4	├
50	11433-2P	11433-3209	4	6	3	4	├
51	11433-2P	11502-2205	4	6	4	4	└
52	11401-3412-1	90514-20299O	5	6	1	1	└
53	11409-3416-1	L608M	5	6	1	1	└
54	11413-3410-1	11401-3412-1	5	6	1	2	├
55	11413-3410-1	11413-3411-1	5	6	2	2	└
56	11413-3411-1	90505-45238O	5	6	1	1	└
57	11401-3412-1	90514-20299O	6	6	1	1	└
58	11413-3411-1	90505-45238O	6	6	1	1	└