数据库数据如下:A B
1 001
2 002
3 003
4 004
5 005
6 006
7 007
8 008用sql语句要实现的效果是:
A B C D E F
1 001 2 002 3 003
4 004 5 005 6 006
7 0007 8 008也就是一列数据要求实现一行显示三条。
1 001
2 002
3 003
4 004
5 005
6 006
7 007
8 008用sql语句要实现的效果是:
A B C D E F
1 001 2 002 3 003
4 004 5 005 6 006
7 0007 8 008也就是一列数据要求实现一行显示三条。
--just do it
select a.id,a.name,b.id,b.name,c.id,c.name
from
(
(select rownum rn,id,name from (SELECT row_number() OVER(PARTITION BY trunc((id-1)/3) order by mod(id-1,3)) as rn,AA.* from AA) where rn=1) A
left JOIN
(select rownum rn,id,name from (SELECT ROW_NUMBER() OVER(PARTITION BY trunc((id-1)/3) order by mod(id-1,3)) as rn,AA.* from AA) where rn=2) B
ON A.RN=B.RN
left JOIN
(select rownum rn,id,name from (SELECT ROW_NUMBER() OVER(PARTITION BY trunc((id-1)/3) order by mod(id-1,3)) as rn,AA.* from AA) where rn=3) C
ON A.RN=C.RN)我以前回答的一个问题和你很相似~
你把语句里面的NAME换成B字段就可以
http://topic.csdn.net/u/20081211/15/3febc041-f1f2-4452-8ac3-77acccb94794.html
2 FROM TEST WHERE MOD(A,3)=1) a
3 ,(SELECT ROWNUM RN,
4 CASE WHEN MOD(A,3)=2 THEN A END C,CASE WHEN MOD(A,3)=2 THEN B END D
5 FROM TEST WHERE MOD(A,3)=2) b,
6 (SELECT ROWNUM RN,
7 CASE WHEN MOD(A,3)=0 THEN A END E,CASE WHEN MOD(A,3)=0 THEN B END F FROM TEST WHERE MOD(A,3)=0) c
8 where a.rn=b.rn(+) and a.rn =c.rn(+)
9 ;
A B C D E F
---------- ---------- ---------- ---------- ---------- ----------
1 001 2 002 3 003
4 004 5 005 6 006
7 007 8 008