请问:sql 语句分栏写法
现有一表 test (A)
有如下数据
A
------
a
b
c
d
e
f
g
h
i
j
要就得出下面的结果(分3列)
a e h
b f i
c g j
d
上面结果是这样得来的,要求按第一栏排列完成后,排列第二栏,再排列第三栏(也就是先栏后列的意思)
如:上面有10条记录,那么可以分成10/3行, 如果不能整除则加1表示是有多少行
上例子则是10/3+1 = 4 行
如果Sql不行的话,程储过程也可以
谢谢!!
...全文
21213打赏收藏
请问:sql 语句分栏写法
请问:sql 语句分栏写法 现有一表 test (A) 有如下数据 A ------ a b c d e f g h i j 要就得出下面的结果(分3列) a e h b f i c g j d 上面结果是这样得来的,要求按第一栏排列完成后,排列第二栏,再排列第三栏(也就是先栏后列的意思) 如:上面有10条记录,那么可以分成10/3行, 如果不能整除则加1表示是有多少行 上例子则是10/3+1 = 4 行 如果Sql不行的话,程储过程也可以 谢谢!!
已用时间: 00: 00: 00.00
SQL> select a,a1,a2 from
2 (select a,cnt,lead(a,ceil(cnt/3)) over(order by a) a1,
3 lead(a,ceil(cnt/3)*2) over(order by a) a2
4 from (select a,count(*) over() cnt from test)
5 ) where rownum <=ceil(cnt/3)
6 /
A A1 A2
---------- ---------- ----------
a e i
b f j
c g
d h
已用时间: 00: 00: 00.10
SQL> select tb1.name name1,tb2.name name2,tb3.name name3 from (
2 select id,name from (select rownum id,name from t) t1,
3 (select ceil(count(*)/3) num from t) tt where t1.id<=tt.num
4 ) tb1,
5 (
6 select id-tt.num id,name from (select rownum id,name from t) t1,
7 (select ceil(count(*)/3) num from t) tt where t1.id between tt.num+1 and tt.num*2
8 ) tb2,
9 (
10 select id-tt.num*2 id,name from (select rownum id,name from t) t1,
11 (select ceil(count(*)/3) num from t) tt where t1.id >tt.num*2
12 ) tb3
13 where tb1.id=tb2.id and tb1.id=tb3.id(+);
NAME1 NAME2 NAME3
---------- ---------- ----------
a e i
b f j
c g
d h
已用时间: 00: 00: 00.71
SQL> select tb1.name name1,tb2.name name2,tb3.name name3 from (
2 select id,name from (select rownum id,name from t) t1,
3 (select ceil(count(*)/3) num from t) tt where t1.id<=tt.num
4 ) tb1,
5 (
6 select id-tt.num id,name from (select rownum id,name from t) t1,
7 (select ceil(count(*)/3) num from t) tt where t1.id between tt.num+1 and tt.num*2
8 ) tb2,
9 (
10 select id-tt.num*2 id,name from (select rownum id,name from t) t1,
11 (select ceil(count(*)/3) num from t) tt where t1.id >tt.num*2
12 ) tb3
13 where tb1.id=tb2.id and tb1.id=tb3.id(+);
NAME1 NAME2 NAME3
---------- ---------- ----------
a e i
b f j
c g k
d h
select a.rowid as col1,b.rowid as col2,c.rowid as col3 from test a left join test b on b.rowid=a.rowid+1*rowcount left join test c on c.rowid=a.rowid+2*rowcount where a.rowid<=rowcount