[PHP]
如下:
SQL> select * from test;
USEID USENAME BELONG1ID BELONG2ID BELONG3ID BELONG4ID BELONG5ID BELONG6ID BELONG7ID BELONG8ID BELONG9ID BELONG10ID
----- ---------- --------- --------- --------- --------- --------- --------- --------- --------- --------- ----------
001 关羽 B1 B2 B3 B4 B5 B6 B7 B8 B9 B10
002 张飞 B3 B6 B10 B4 B5 B1
SQL>
SQL> select USEID,
2 USENAME,
3 decode(rn,
4 1,
5 BELONG1ID,
6 2,
7 BELONG2ID,
8 3,
9 BELONG3ID,
10 4,
11 BELONG4ID,
12 5,
13 BELONG5ID,
14 6,
15 BELONG6ID,
16 7,
17 BELONG7ID,
18 8,
19 BELONG8ID,
20 9,
21 BELONG9ID,
22 10,
23 BELONG10ID) BELONGID
24 from (select b.*,
25 row_number() over(partition by b.USEID order by rownum) rn
26 from (select level rn from dual connect by rownum <= 10) a, test b)
27 where decode(rn,
28 1,
29 BELONG1ID,
30 2,
31 BELONG2ID,
32 3,
33 BELONG3ID,
34 4,
35 BELONG4ID,
36 5,
37 BELONG5ID,
38 6,
39 BELONG6ID,
40 7,
41 BELONG7ID,
42 8,
43 BELONG8ID,
44 9,
45 BELONG9ID,
46 10,
47 BELONG10ID) is not null;
USEID USENAME BELONGID
----- ---------- --------
001 关羽 B1
001 关羽 B2
001 关羽 B3
001 关羽 B4
001 关羽 B5
001 关羽 B6
001 关羽 B7
001 关羽 B8
001 关羽 B9
001 关羽 B10
002 张飞 B3
002 张飞 B6
002 张飞 B10
002 张飞 B4
002 张飞 B5
002 张飞 B1
16 rows selected
SQL>
[/PHP]