一個有趣的sql

表A

partid matid partweight
ccc cccc 1
bbb bbbb 2
ddd dddd 3
eee eeee 4
fff ffff 5

請問用什麼語句可實現從表A到下面格式的顯示,謝謝......

partid matid partweight test
ccc cccc 1 1/1
bbb bbbb 2 1/2
bbb bbbb 2 2/2
ddd dddd 3 3/3
ddd dddd 3 1/3
ddd dddd 3 2/3
eee eeee 4 1/4
eee eeee 4 2/4
eee eeee 4 3/4
eee eeee 4 4/4
fff ffff 5 1/5
fff ffff 5 2/5
fff ffff 5 3/5
fff ffff 5 4/5
fff ffff 5 5/5

解答如下：

SQL> SELECT * FROM TEST;

PARTID MATID PARTWEIGHT
-------------------- ------------------------------------------------------------ -------------------
ccc cccc 1
bbb bbbb 2
ddd dddd 3
eee eeee 4
fff ffff 5

SQL>
SQL> SELECT PARTID,MATID,ROW_NUMBER() OVER(PARTITION BY PARTWEIGHT ORDER BY ROWNUM)||'/'||PARTWEIGHT FROM(
2 SELECT PARTID,MATID,PARTWEIGHT FROM TEST,
3 (
4 select substr(col,instr(col,',',1,rownum)+1,instr(col,',',1,rownum+1)-instr(col,',',1,rownum)-1) col from (
5 select ',1,2,2,3,3,3,4,4,4,4,5,5,5,5,5,' col from dual
6 ) connect by rownum7 ) TEMP
8 WHERE TEST.PARTWEIGHT = TEMP.COL
9 )
10 ;

PARTID MATID ROW_NUMBER()OVER(PARTITIONBYPA
-------------------- ------------------------------------------------------------ --------------------------------------------------------------------------------
ccc cccc 1/1
bbb bbbb 1/2
bbb bbbb 2/2
ddd dddd 1/3
ddd dddd 2/3
ddd dddd 3/3
eee eeee 1/4
eee eeee 2/4
eee eeee 3/4
eee eeee 4/4
fff ffff 1/5
fff ffff 2/5
fff ffff 3/5
fff ffff 4/5
fff ffff 5/5

15 rows selected

上邊的這個按照如下規律實現：
partweight如果是n就會出現n條資料。

n支援的最大值靠維護sql中如下部分實現。
select ',1,2,2,3,3,3,4,4,4,4,5,5,5,5,5,' col from dual

其中構造temp表部分

select substr(col,instr(col,',',1,rownum)+1,instr(col,',',1,rownum+1)-instr(col,',',1,rownum)-1) col from (
select ',1,2,2,3,3,3,4,4,4,4,5,5,5,5,5,' col from dual
) connect by rownum
可以參看以下地址：
http://www.itpub.net/showthread.php?threadid=626516


如果存在過大n值，可以考慮藉助一個臨時表來實現，先把n的值初始化。

方法二：

SQL> select a.partid, a.matid, t.n||'/'||a.partweight as partweight

2 from a, (select level as n from dual connect by level<=10) t

3 where a.partweight>=t.n

4 order by a.partweight, t.n

5 /

這個不等連線書寫簡單，但是效率不是很高

select partid, matid, t.n||'/'||test.partweight
from test, (select level as n from dual connect by level<=10) t
where test.partweight>=t.n order by test.partweight, t.n

已選擇15行。


執行計劃
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (ORDER BY)
2 1 NESTED LOOPS
3 2 VIEW
4 3 CONNECT BY (WITH FILTERING)
5 4 NESTED LOOPS
6 5 TABLE ACCESS (FULL) OF 'DUAL'
7 5 TABLE ACCESS (BY USER ROWID) OF 'DUAL'
8 4 NESTED LOOPS
9 8 BUFFER (SORT)
10 9 CONNECT BY PUMP
11 8 FILTER
12 11 TABLE ACCESS (FULL) OF 'DUAL'
13 2 TABLE ACCESS (FULL) OF 'TEST'




統計資訊
----------------------------------------------------------
0 recursive calls
0 db block gets
77 consistent gets
0 physical reads
0 redo size
699 bytes sent via SQL*Net to client
511 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
15 rows processed


SELECT PARTID,MATID,ROW_NUMBER() OVER(PARTITION BY PARTWEIGHT ORDER BY ROWNUM)||'/'||PARTWEIGHT FROM(
SELECT PARTID,MATID,PARTWEIGHT FROM TEST,
(
select substr(col,instr(col,',',1,rownum)+1,instr(col,',',1,rownum+1)-instr(col,',',1,rownum)-1) col from (
select ',1,2,2,3,3,3,4,4,4,4,5,5,5,5,5,' col from dual
) connect by rownum) TEMP
WHERE TEST.PARTWEIGHT = TEMP.COL
)
order by rownum;

已選擇15行。


執行計劃
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (ORDER BY)
2 1 WINDOW (SORT)
3 2 COUNT
4 3 MERGE JOIN
5 4 SORT (JOIN)
6 5 VIEW
7 6 COUNT (STOPKEY)
8 7 CONNECT BY (WITH FILTERING)
9 8 NESTED LOOPS
10 9 TABLE ACCESS (FULL) OF 'DUAL'
11 9 TABLE ACCESS (BY USER ROWID) OF 'DUAL'
12 8 NESTED LOOPS
13 12 BUFFER (SORT)
14 13 CONNECT BY PUMP
15 12 TABLE ACCESS (FULL) OF 'DUAL'
16 4 SORT (JOIN)
17 16 TABLE ACCESS (FULL) OF 'TEST'




統計資訊
----------------------------------------------------------
0 recursive calls
0 db block gets
14 consistent gets
0 physical reads
0 redo size
743 bytes sent via SQL*Net to client
511 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
9 sorts (memory)
0 sorts (disk)
15 rows processed

等連線>不等連線>笛卡爾積(最大程度的關聯)，不知道這個說法是否正確？

[@more@]