需求参看http://www.itpub.net/showthread.php?s=&postid=5476878#post5476878

求个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

等连接>不等连接>笛卡尔积(最大程度的关联)，不知道这个说法是否正确？