For oracle SQL statement-CodePudding

ID Name
1 and 4 apples, bananas, oranges,
2, 1 watermelon, apple
2 minus 2 watermelon, orange, pear
5,1,4,6,2 grapes, apples, oranges, pears, watermelon

To get the following list
Id name
1 apple
2 watermelon
Three bananas
4 oranges
5 grapes
Six pears

For each master, how do you write this SQL statement?

CodePudding user response:

This if can use stored procedures in a temporary table is relatively simple, using a SQL written as if do not have what idea, wait for SQL great god shows the code

CodePudding user response:

 with tab1 as (
Select rowidd 'r1', '1' and 4 'id' apples, bananas, oranges' name from dual union all 
Select 'r2' rowidd, '2 minus 2' id, 'watermelon, orange, pears' name from dual 
) 
Select distinct 
Regexp_substr (t1. Id, '[^,] +' 1, level) id, 
Regexp_substr (t1) name, '[^,] +', 1, level) name 
The from tab1 t1 
Connect by the prior t1. Rowidd=t1. Rowidd 
And the prior sys_guid () is not null 
And level & lt;=regexp_count (t1. Id ', ') + 1 
The order by regexp_substr (t1. Id, '[^,] +' 1, level) 
;

CodePudding user response:

Roughly is such, can be concluded that the original poster to effect, can enlarge the level to adapt to a long string of level,
SELECT DISTINCT Id, NAME
The FROM (SELECT Regexp_Substr (Anderson, d, '[^,] +' 1, LEVEL) Id,
Regexp_Substr (a.N ame, '[^,] +' 1, LEVEL) NAME
The FROM (SELECT '1 and 4' Id, apples, bananas, oranges' NAME
The FROM Dual
UNION ALL
SELECT '2, 1' Id, watermelon, apple 'NAME
The FROM Dual
UNION ALL
SELECT '2 minus 2' Id, watermelon, orange, pears' NAME
The FROM Dual
UNION ALL
SELECT '5,1,4,6,2' Id, grapes, apples, oranges, pears, watermelon 'NAME
The FROM Dual) a
CONNECT BY LEVEL & lt;
=3)WHERE the Id IS NOT NULL
The ORDER BY ID.

The following statements is to forge the data, can be replaced with the actual table
(SELECT '1 and 4' Id, apples, bananas, oranges' NAME
The FROM Dual
UNION ALL
SELECT '2, 1' Id, watermelon, apple 'NAME
The FROM Dual
UNION ALL
SELECT '2 minus 2' Id, watermelon, orange, pears' NAME
The FROM Dual
UNION ALL
SELECT '5,1,4,6,2' Id, grapes, apples, oranges, pears, watermelon 'NAME
The FROM Dual) a

CodePudding user response:

reference xxfamly reply: 3/f

roughly is such, can be concluded that the original poster to effect, can enlarge the level to adapt to a long string of level,
SELECT DISTINCT Id, NAME
The FROM (SELECT Regexp_Substr (Anderson, d, '[^,] +' 1, LEVEL) Id,
Regexp_Substr (a.N ame, '[^,] +' 1, LEVEL) NAME
The FROM (SELECT '1 and 4' Id, apples, bananas, oranges' NAME
The FROM Dual
UNION ALL
SELECT '2, 1' Id, watermelon, apple 'NAME
The FROM Dual
UNION ALL
SELECT '2 minus 2' Id, watermelon, orange, pears' NAME
The FROM Dual
UNION ALL
SELECT '5,1,4,6,2' Id, grapes, apples, oranges, pears, watermelon 'NAME
The FROM Dual) a
CONNECT BY LEVEL & lt;
=3)WHERE the Id IS NOT NULL
The ORDER BY ID.

The following statements is to forge the data, can be replaced with the actual table
(SELECT '1 and 4' Id, apples, bananas, oranges' NAME
The FROM Dual
UNION ALL
SELECT '2, 1' Id, watermelon, apple 'NAME
The FROM Dual
UNION ALL
SELECT '2 minus 2' Id, watermelon, orange, pears' NAME
The FROM Dual
UNION ALL
SELECT '5,1,4,6,2' Id, grapes, apples, oranges, pears, watermelon 'NAME
The FROM Dual) a

The query result errors, such as do not check the following statement watermelon

 SELECT DISTINCT Id, NAME 
The FROM (SELECT Regexp_Substr (Anderson, d, '[^,] +' 1, LEVEL) Id, 
Regexp_Substr (a.N ame, '[^,] +' 1, LEVEL) NAME 
The FROM (SELECT '1 and 4' Id, apples, bananas, oranges' NAME 
The FROM Dual 
UNION ALL 
SELECT '1' Id, 'apple' NAME 
The FROM Dual 
UNION ALL 
SELECT '4, 6' Id, oranges, pears' NAME 
The FROM Dual 
UNION ALL 
SELECT '5,1,4,6,2' Id, grapes, apples, oranges, pears, watermelon 'NAME 
The FROM Dual) a 
CONNECT BY LEVEL & lt; 
=3)WHERE the Id IS NOT NULL 
The ORDER BY ID.

And join condition is not complete, can make the intermediate data is too large (article 13 the data you have found 84), serious when may not find the data, according to your statement, intermediate data as the article level and basic data for exponential growth, compared to the following two query efficiency,

 with tab1 as (
Select 'r1' rowidd, '5,1,4,6,2' id 'grapes, apples, oranges, pears, watermelon' name from dual 
Connect by level & lt;=1000 
) 
Select distinct 
Regexp_substr (t1. Id, '[^,] +' 1, level) id, 
Regexp_substr (t1) name, '[^,] +', 1, level) name 
The from tab1 t1 
Connect by the prior t1. Rowidd=t1. Rowidd 
And the prior sys_guid () is not null 
And level & lt;=regexp_count (t1. Id ', ') + 1 
The order by regexp_substr (t1. Id, '[^,] +' 1, level) 
; 

SELECT DISTINCT Id, NAME 
The FROM (SELECT Regexp_Substr (Anderson, d, '[^,] +' 1, LEVEL) Id, 
Regexp_Substr (a.N ame, '[^,] +' 1, LEVEL) NAME 
The FROM (select 'r1' rowidd, '5,1,4,6,2' id, 'grapes, apples, oranges, pears, watermelon' name FROM dual 
Connect by level & lt; A 
=1000)CONNECT BY LEVEL & lt; 
=5)WHERE the Id IS NOT NULL 
The ORDER BY ID.

CodePudding user response:

Thanks to provide the source code of classmate, thanks for sharing,