表test 字段
a1 a2
1 a
1 b
2 x
2 y
2 z
查询的结果要求
1 ab
2 xyz
SQL> with b as (select 1 a1,'a' a2 from dual
2 union
3 select 1 a1,'b' a2 from dual
4 union
5 select 2 a1,'x' a2 from dual
6 union
7 select 2 a1,'y' a2 from dual
8 union
9 select 2 a1,'z' a2 from dual
10 )
11 select a1,max(sys_connect_by_path(a2,' ')) aa from
12 (select a1,a2,row_number()over(partition by a1 order by a1,a2) rn from b)
13 start with rn=1
14 connect by rn-1=prior rn
15 group by a1
16 ;
A1 AA
---------- --------------------------------------------------------------------------------
1 x b
2 x y z
當加了ROWNUM后
SQL> with b as (select 1 a1,'a' a2 from dual
2 union
3 select 1 a1,'b' a2 from dual
4 union
5 select 2 a1,'x' a2 from dual
6 union
7 select 2 a1,'y' a2 from dual
8 union
9 select 2 a1,'z' a2 from dual
10 )
11 select a1,max(sys_connect_by_path(a2,' ')) aa from
12 (select a1,a2,rownum rnum,row_number()over(partition by a1 order by a1,a2) rn from b)
13 start with rn=1
14 connect by rnum-1=prior rnum
15 group by a1
16 ;
A1 AA
---------- --------------------------------------------------------------------------------
1 a b
2 x y z
或者這樣也可以
select A1,MAX(SYS_CONNECT_BY_PATH(A2,' ')) A2 from
(SELECT A2,A1,ROW_NUMBER()OVER(PARTITION BY A1 ORDER BY A1,A2) RN FROM B) C
START WITH RN=1
CONNECT BY rn-1=prior rn and a1=prior a1
GROUP BY A1
主要是在CONNECT BY rn-1=prior rn and a1=prior a1,對分組的字段進行CONNECT BY
例三:
有一个通用表如下:
TYPE_CODE TYPE_NAME
01 XX学校
0101 XX班级
010101 XX学生
请问怎么用SQL方便的查出以下记录:
XX学校 XX班级 XX学生
WITH A AS (SELECT '01' ID,'XX學校' ADDRESS FROM DUAL
UNION
SELECT '0101' ID,'XX班級' ADDRESS FROM DUAL
UNION
SELECT '010101' ID,'XX學生' ADDRESS FROM DUAL
)
SELECT MAX(SYS_CONNECT_BY_PATH(B.ADDRESS,' ')) DD FROM (select ID,ADDRESS,ROW_NUMBER()OVER(PARTITION BY SUBSTR(ID,1,2) ORDER BY ID) RN
from A ) B
START WITH B.RN=1
CONNECT BY B.RN-1=PRIOR B.RN
/*Start with 开始位置,connect by prior 子节点=父节点;
必须使用prior引用父行
sys_connect_by_path(userid, ',')之间用','分割*/
With x As (
SELECT 'test_1' NAME, 'test1' userid FROM dual UNION ALL
SELECT 'test_1' NAME, 'test2' userid FROM dual UNION ALL
SELECT 'test_2' NAME, 'test1' userid FROM dual UNION ALL
SELECT 'test_2' NAME, 'test2' userid FROM dual UNION ALL
SELECT 'test_3' NAME, 'test1' userid FROM dual UNION ALL
SELECT 'test_3' NAME, 'test2' userid FROM dual UNION ALL
SELECT 'test_4' NAME, 'test1' userid FROM dual )
select Name, substr(max(sys_connect_by_path(userid, ',')),2) userid
from (select Name,
userid,
row_number() over(partition by Name order by userid) rn
from x)
start with rn = 1
connect by rn - 1 = prior rn
And Name = Prior Name
group by Name
order by Name;
WITH A AS (SELECT 1 ID,'硬件' TITLE,0 PARENTID FROM DUAL
UNION ALL
SELECT 2 ID,'外設' TITLE,1 PARENTID FROM DUAL
UNION ALL
SELECT 3 ID,'打印機' TITLE,2 PARENTID FROM DUAL
UNION ALL
SELECT 4 ID,'軟件' TITLE,0 PARENTID FROM DUAL
UNION ALL
SELECT 5 ID,'惠普打印機' TITLE,3 PARENTID FROM DUAL
UNION ALL
SELECT 6 ID,'存儲器' TITLE,1 PARENTID FROM DUAL
UNION ALL
SELECT 7 ID,'系統軟件' TITLE,4 PARENTID FROM DUAL
UNION ALL
SELECT 8 ID,'外存儲器' TITLE,6 PARENTID FROM DUAL
UNION ALL
SELECT 9 ID,'內存儲器' TITLE,6 PARENTID FROM DUAL
UNION ALL
SELECT 10 ID,'硬盤' TITLE,8 PARENTID FROM DUAL
)
select LPAD(' ',PARENTID)||TITLE from A
START WITH PARENTID=0
CONNECT BY PARENTID=PRIOR ID
LPAD('',PARENTID)||TITLE
--------------------------------------------------------------------------------
硬件
外設
打印機
惠普打印機
存儲器
外存儲器
硬盤
內存儲器
軟件
系統軟件
10 rows selected
列出從ID1出發到ID2的所有路線
SQL> WITH A AS (SELECT 'A' ID1,'B' ID2,1000 JL FROM DUAL
2 UNION
3 SELECT 'A' ID1,'C' ID2,1100 JL FROM DUAL
4 UNION
5 SELECT 'A' ID1,'D' ID2,900 JL FROM DUAL
6 UNION
7 SELECT 'A' ID1,'E' ID2,400 JL FROM DUAL
8 UNION
9 SELECT 'B' ID1,'D' ID2,300 JL FROM DUAL
10 UNION
11 SELECT 'D' ID1,'F' ID2,600 JL FROM DUAL
12 UNION
13 SELECT 'E' ID1,'A' ID2,400 JL FROM DUAL
14 UNION
15 SELECT 'F' ID1,'G' ID2,1000 JL FROM DUAL
16 UNION
17 SELECT 'C' ID1,'B' ID2,600 JL FROM DUAL
18 )
19 select ID1,SYS_CONNECT_BY_PATH(ID1,'->')||'->'||ID2 RN
20 from A
21 START WITH ID1='A'
22 CONNECT BY NOCYCLE ID1=PRIOR ID2 AND ID2<>'A'
23 /
ID1 RN
--- --------------------------------------------------------------------------------
A ->A->B
B ->A->B->D
D ->A->B->D->F
F ->A->B->D->F->G
A ->A->C
C ->A->C->B
B ->A->C->B->D
D ->A->C->B->D->F
F ->A->C->B->D->F->G
A ->A->D
D ->A->D->F
F ->A->D->F->G
A ->A->E
13 rows selected
有张表a中有两个字段,id,num_begin ,num_end表示起始号码和终止号码,数据如下:
id num_begin num_end
1 12 15
2 40 45
求一个sql,得到如下结果
id num
1 12
1 13
1 14
1 15
2 40
2 41
2 42
2 43
2 44
2 45
with c as (select 1 id,12 num_begin,15 num_end from dual
union
select 2 id,40 num_begin,45 num_end from dual
)
select c.id,b.lvl from jim.c, (select level lvl from dual connect by level<=45) b
where b.lvl between c.num_begin and c.num_end
1 12
1 13
1 14
1 15
2 40
2 41
2 42
2 43
2 44
2 45
connect by 說明每行數據將是按層次順序進行檢索.當要把一行轉換成多行的時候就要用到這個!
SQL> WITH A AS (SELECT '1 2 3' ID FROM DUAL
2 UNION
3 SELECT '4 5 6' ID FROM DUAL
4 UNION
5 SELECT '4 6 9' ID FROM DUAL
6 )
7 SELECT * FROM
8 (select SUBSTR(ID,LEV,1) RN from
9 (select DISTINCT LEVEL LEV,ID FROM A
10 CONNECT BY LEVEL<=LENGTH(ID)
11 ORDER BY ID,LEV))
12 WHERE NVL(RN,' ')<>' '
13 /
RN
--
1
2
3
4
5
6
4
6
9
9 rows selected
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/10768286/viewspace-83477/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/10768286/viewspace-83477/