现有一个商品销售表sale,表结构为:
month char(6) --月份
sell number(10,2) --月销售金额
create table sale (month char(6),sell number);
insert into sale values('200001',1000);
insert into sale values('200002',1100);
insert into sale values('200003',1200);
insert into sale values('200004',1300);
insert into sale values('200005',1400);
insert into sale values('200006',1500);
insert into sale values('200007',1600);
insert into sale values('200101',1100);
insert into sale values('200202',1200);
insert into sale values('200301',1300);
insert into sale values('200008',1000);
commit;
SQL>; select rownum,month,sell from sale where rownum=1;(可以用在限制返回记录条数的地方,保证不出错,如:隐式游标)
ROWNUM MONTH SELL
--------- ------ ---------
1 200001 1000
SQL>; select rownum,month,sell from sale where rownum=2;(1以上都查不到记录)
没有查到记录
SQL>; select rownum,month,sell from sale where rownum>;5;
(由于rownum是一个总是从1开始的伪列,Oracle 认为这种条件不成立,查不到记录)
没有查到记录
只返回前3条纪录
SQL>; select rownum,month,sell from sale where rownum<4;
ROWNUM MONTH SELL
--------- ------ ---------
1 200001 1000
2 200002 1100
3 200003 1200
如何用rownum实现大于、小于逻辑?(返回rownum在4—10之间的数据)(minus操作,速度会受影响)
SQL>; select rownum,month,sell from sale where rownum<10
2 minus
3 select rownum,month,sell from sale where rownum<5;
ROWNUM MONTH SELL
--------- ------ ---------
5 200005 1400
6 200006 1500
7 200007 1600
8 200101 1100
9 200202 1200
month char(6) --月份
sell number(10,2) --月销售金额
create table sale (month char(6),sell number);
insert into sale values('200001',1000);
insert into sale values('200002',1100);
insert into sale values('200003',1200);
insert into sale values('200004',1300);
insert into sale values('200005',1400);
insert into sale values('200006',1500);
insert into sale values('200007',1600);
insert into sale values('200101',1100);
insert into sale values('200202',1200);
insert into sale values('200301',1300);
insert into sale values('200008',1000);
commit;
SQL>; select rownum,month,sell from sale where rownum=1;(可以用在限制返回记录条数的地方,保证不出错,如:隐式游标)
ROWNUM MONTH SELL
--------- ------ ---------
1 200001 1000
SQL>; select rownum,month,sell from sale where rownum=2;(1以上都查不到记录)
没有查到记录
SQL>; select rownum,month,sell from sale where rownum>;5;
(由于rownum是一个总是从1开始的伪列,Oracle 认为这种条件不成立,查不到记录)
没有查到记录
只返回前3条纪录
SQL>; select rownum,month,sell from sale where rownum<4;
ROWNUM MONTH SELL
--------- ------ ---------
1 200001 1000
2 200002 1100
3 200003 1200
如何用rownum实现大于、小于逻辑?(返回rownum在4—10之间的数据)(minus操作,速度会受影响)
SQL>; select rownum,month,sell from sale where rownum<10
2 minus
3 select rownum,month,sell from sale where rownum<5;
ROWNUM MONTH SELL
--------- ------ ---------
5 200005 1400
6 200006 1500
7 200007 1600
8 200101 1100
9 200202 1200