ID Name
1,3,4 蘋果,香蕉,桔子
2,1 西瓜,蘋果
2,4,6 西瓜,桔子,梨子
5,1,4,6,2 葡萄,蘋果,桔子,梨子,西瓜
要得到下面的串列
id name
1 蘋果
2 西瓜
3 香蕉
4 桔子
5 葡萄
6 梨子
求助各位大俠，這個sql陳述句怎么寫？
uj5u.com熱心網友回復：
這個如果可以用存盤程序寫入臨時表的話相對而言簡單點，用一條sql寫出來 好像沒啥思路，靜候SQL大神給出代碼uj5u.com熱心網友回復：
with tab1 as (
select 'r1' rowidd, '1,3,4' id, '蘋果,香蕉,桔子' name from dual union all
select 'r2' rowidd, '2,4,6' id, '西瓜,桔子,梨子' name from dual
)
select distinct
regexp_substr(t1.id, '[^,]+', 1, level) id,
regexp_substr(t1.name, '[^,]+', 1, level) name
from tab1 t1
connect by prior t1.rowidd = t1.rowidd
and prior sys_guid() is not null
and level <= regexp_count(t1.id, ',') + 1
order by regexp_substr(t1.id, '[^,]+', 1, level)
;
uj5u.com熱心網友回復：
大致就是這樣，可以得出樓主要的效果，可以擴大level的級別以適應更長的字串。SELECT DISTINCT Id, NAME
FROM (SELECT Regexp_Substr(a.Id, '[^,]+', 1, LEVEL) Id,
Regexp_Substr(a.Name, '[^,]+', 1, LEVEL) NAME
FROM (SELECT '1,3,4' Id, '蘋果,香蕉,桔子' NAME
FROM Dual
UNION ALL
SELECT '2,1' Id, '西瓜,蘋果' NAME
FROM Dual
UNION ALL
SELECT '2,4,6' Id, '西瓜,桔子,梨子' NAME
FROM Dual
UNION ALL
SELECT '5,1,4,6,2' Id, '葡萄,蘋果,桔子,梨子,西瓜' NAME
FROM Dual) a
CONNECT BY LEVEL <= 3)
WHERE Id IS NOT NULL
ORDER BY ID;
以下陳述句是為了偽造資料，可以替換為實際的表
(SELECT '1,3,4' Id, '蘋果,香蕉,桔子' NAME
FROM Dual
UNION ALL
SELECT '2,1' Id, '西瓜,蘋果' NAME
FROM Dual
UNION ALL
SELECT '2,4,6' Id, '西瓜,桔子,梨子' NAME
FROM Dual
UNION ALL
SELECT '5,1,4,6,2' Id, '葡萄,蘋果,桔子,梨子,西瓜' NAME
FROM Dual) a
uj5u.com熱心網友回復：
查詢結果錯誤，比如下面的陳述句查不到西瓜
SELECT DISTINCT Id, NAME
FROM (SELECT Regexp_Substr(a.Id, '[^,]+', 1, LEVEL) Id,
Regexp_Substr(a.Name, '[^,]+', 1, LEVEL) NAME
FROM (SELECT '1,3,4' Id, '蘋果,香蕉,桔子' NAME
FROM Dual
UNION ALL
SELECT '1' Id, '蘋果' NAME
FROM Dual
UNION ALL
SELECT '4,6' Id, '桔子,梨子' NAME
FROM Dual
UNION ALL
SELECT '5,1,4,6,2' Id, '葡萄,蘋果,桔子,梨子,西瓜' NAME
FROM Dual) a
CONNECT BY LEVEL <= 3)
WHERE Id IS NOT NULL
ORDER BY ID;
而且連接條件不全，會使中間資料過大（13條資料你查出了84條），嚴重時可能會查不出資料。按照你的陳述句，中間資料會隨著level與基礎資料條數成指數增長。對比下面兩個查詢的效率就知道了。
with tab1 as (
select 'r1' rowidd, '5,1,4,6,2' id, '葡萄,蘋果,桔子,梨子,西瓜' name from dual
connect by level <= 1000
)
select distinct
regexp_substr(t1.id, '[^,]+', 1, level) id,
regexp_substr(t1.name, '[^,]+', 1, level) name
from tab1 t1
connect by prior t1.rowidd = t1.rowidd
and prior sys_guid() is not null
and level <= regexp_count(t1.id, ',') + 1
order by regexp_substr(t1.id, '[^,]+', 1, level)
;
SELECT DISTINCT Id, NAME
FROM (SELECT Regexp_Substr(a.Id, '[^,]+', 1, LEVEL) Id,
Regexp_Substr(a.Name, '[^,]+', 1, LEVEL) NAME
FROM (select 'r1' rowidd, '5,1,4,6,2' id, '葡萄,蘋果,桔子,梨子,西瓜' name from dual
connect by level <= 1000) a
CONNECT BY LEVEL <= 5)
WHERE Id IS NOT NULL
ORDER BY ID;
uj5u.com熱心網友回復：
感謝提供源代碼的同學，謝謝分享。轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/54015.html
標籤：基礎和管理