1
1
如果您使用的是Oracle 12C,那么可以使用正则表达式来实现您的结果。 例如:- create table food_order (order_no number, food_order varchar2(500)); insert into food_order values (1001,'102*4;109*3;101*2'); insert into food_order values (1002,'103*3;108*5;101*3'); insert into food_order values (1003,'106*4;107*3;109*2'); insert into food_order values (1004,'106*4;105*3;101*7'); select * from food_order; Output:- ORDER_NO FOOD_ORDER 1001 102*4;109*3;101*2 1002 103*3;108*5;101*3 1003 106*4;107*3;109*2 1004 106*4;105*3;101*7 现在尝试以下查询:- SELECT order_no, regexp_substr(food_order,'[^*]+', 1,1) AS"ORDER_ID", regexp_substr(food_order,'[^*]+', 1,2) AS"QUANTITY" FROM (SELECT DISTINCT order_no, regexp_substr(food_order,'[^;]+', 1, level) AS"FOOD_ORDER" FROM food_order CONNECT BY regexp_substr(food_order, '[^;]+', 1, level) IS NOT NULL ) temp order by order_no; |
2
0
|
mikrimouse · 使用nvl功能选择 7 年前 |