请教，这个如何写

显示全部楼层 · 2013-12-6 14:15:45

请各位指点，
从下面的结果
NMVAL
A1
B1
B2
C1
C2
C3
如果得到下面的结果
NMVAL
A1
B1
C1
A1
B1
C2
A1
B1
C3
A1
B2
C1
A1
B2
C2
A1
B2
C3

说明：从　Ａ，Ｂ，Ｃ　分组中每次取一个，请列出所有可能的结果。　有可能会有　Ｄ，Ｅ。。。
WITH T AS (
SELECT 'A' NM , 1 VAL FROM DUAL UNION ALL
SELECT 'B' NM , 1 VAL FROM DUAL UNION ALL
SELECT 'B' NM , 2 VAL FROM DUAL UNION ALL
SELECT 'C' NM , 1 VAL FROM DUAL UNION ALL
SELECT 'D' NM , 2 VAL FROM DUAL UNION ALL
SELECT 'D' NM , 3 VAL FROM DUAL
)
SELECT NM , VAL FROM T ;

千问 · 2013-12-6 14:15:45

这会儿只能想出这个，也不知道符合限制不
WITH T AS (
SELECT 'A' NM , 1 VAL FROM DUAL UNION ALL
SELECT 'B' NM , 1 VAL FROM DUAL UNION ALL
SELECT 'B' NM , 2 VAL FROM DUAL UNION ALL
SELECT 'C' NM , 1 VAL FROM DUAL UNION ALL
SELECT 'D' NM , 2 VAL FROM DUAL UNION ALL
SELECT 'D' NM , 3 VAL FROM DUAL
),
tmp as (
select
sys_connect_by_path(nm, ',') str, sys_connect_by_path(VAL, ',') n, rownum id
from
t
where
nm=(select max(nm) from t)
start with nm='A'
connect by prior ascii(nm)+1=ascii(nm)
)
select
regexp_substr(str, '[^,]+', 1, level) nm2, regexp_substr(n, '[^,]+', 1, level) val2
from
tmp
connect by prior id=id and level0
order by id, 1;

千问 · 2013-12-6 14:15:45

tmp也许这么写更灵活点
select
sys_connect_by_path(nm, ',') str, sys_connect_by_path(VAL, ',') n, rownum id
from
t
where
nm=(select max(nm) from t)
start with nm=(select min(nm) from t)
connect by prior nm0
order by id, 1;

千问 · 2013-12-6 14:15:45

我知道了，where的原因，改成这样
WITH T AS (
SELECT 'A' NM , 1 VAL FROM DUAL UNION ALL
SELECT 'B' NM , 1 VAL FROM DUAL UNION ALL
SELECT 'B' NM , 2 VAL FROM DUAL UNION ALL
SELECT 'C' NM , 1 VAL FROM DUAL UNION ALL
SELECT 'D' NM , 2 VAL FROM DUAL UNION ALL
SELECT 'D' NM , 3 VAL FROM DUAL
),
t1 as (
select min(nm) min_nm, max(nm) max_nm, count(distinct nm) cnt from t
),
tmp as (
select
sys_connect_by_path(nm, ',') str, sys_connect_by_path(VAL, ',') n, rownum id, cnt
from
t, t1
where
level=cnt
start with nm=min_nm
connect by prior nm0
order by id, 1;

千问 · 2013-12-6 14:15:45

牛蛙的connect by prior nm0
order by id, 1;
楼主可以逐层测试看看哪一层出了问题。

千问 · 2013-12-6 14:15:45

本帖最后由 lastwinner 于 2013-10-26 01:40 编辑
用动态sql的效率应该会高一些（拆成多个表连接），毕竟connect by太消耗资源了

千问 · 2013-12-6 14:15:45

lastwinner 发表于 2013-10-26 01:40
用动态sql的效率应该会高一些（拆成多个表连接），毕竟connect by太消耗资源了
看他的输出，你在多表连接后还不得不做列转行。

千问 · 2013-12-6 14:15:45

newkid 发表于 2013-10-25 23:34
牛蛙的connect by prior nm
没错，dense_rank一下好很多。
因为我第一版是先假设abcd这种了，connect by的prior ascii+1=ascii，就不会有acd的问题，后来扩展了，忘了这个地方了。