CREATE TABLE aa (c1 VARCHAR2(5),c2 VARCHAR2(5));
INSERT INTO aa VALUES('v2','a');
INSERT INTO aa VALUES('v2','b');
INSERT INTO aa VALUES('v2','c');
INSERT INTO aa VALUES('v2','d');
INSERT INTO aa VALUES('v2','e');
COMMIT;
可否按每两笔分组, 效果如下:
"rid" c1 c2
1 v2 a
1 v2 b
2 v2 c
2 v2 d
3 v2 e
----------------------------------------libin_ftsafe(子陌红尘)哥
select
decode(MOD(a.rid+1,2),1,a.rid/2,(a.rid+1)/2) as rid, a.c1, a.c2
from (select ROWNUM as rid,c1,c2 from aa) a
还是有问题, 要是有多笔数据, 按每5笔分组, 用mod就不行吧
INSERT INTO aa VALUES('v2','a');
INSERT INTO aa VALUES('v2','b');
INSERT INTO aa VALUES('v2','c');
INSERT INTO aa VALUES('v2','d');
INSERT INTO aa VALUES('v2','e');
COMMIT;
可否按每两笔分组, 效果如下:
"rid" c1 c2
1 v2 a
1 v2 b
2 v2 c
2 v2 d
3 v2 e
----------------------------------------libin_ftsafe(子陌红尘)哥
select
decode(MOD(a.rid+1,2),1,a.rid/2,(a.rid+1)/2) as rid, a.c1, a.c2
from (select ROWNUM as rid,c1,c2 from aa) a
还是有问题, 要是有多笔数据, 按每5笔分组, 用mod就不行吧
可以不?
decode(MOD(a.rid+1,5),1,a.rid/5,2,(a.rid-1)/5,3,(a.rid-2)/5,4,(a.rid-3)/5,(a.rid+1)/5) as rid,
a.c1,
a.c2
from
(select ROWNUM as rid,c1,c2 from aa) a
FLOOR((a.rid+1)/5) as rid,
a.c1,
a.c2
from
(select ROWNUM as rid,c1,c2 from aa) a
INSERT INTO aa VALUES('v2','a');
INSERT INTO aa VALUES('v2','b');
INSERT INTO aa VALUES('v2','c');
INSERT INTO aa VALUES('v2','d');
INSERT INTO aa VALUES('v2','e');
INSERT INTO aa VALUES('v2','f');
INSERT INTO aa VALUES('v2','g');
INSERT INTO aa VALUES('v2','h');
INSERT INTO aa VALUES('v2','i');
COMMIT;select floor((a.rid-1)/5)+1,a.c1,a.c2
from (select ROWNUM as rid,c1,c2 from aa)a