select tab.* from (select * from barmanagersheng.shengshi_id_table where shengshiqu_code=5 group by 排序字段) tab where rownum>=n and rownum<=m
oracel中能用大于号吗?这句行不通
tab 是(select * from barmanagersheng.shengshi_id_table where shengshiqu_code=5 group by 排序字段)执行的记录集的“别名”。 最好定义个“排序字段”。 放在sqlplus中执行试试看吧。
把m和n赋上确切的值,如: select tab.* from (select * from barmanagersheng.shengshi_id_table where shengshiqu_code=5) tab where rownum>=3 and rownum<=5
1.仔细检查有无“barmanagersheng”用户; 2.连接barmanagersheng用户,执行:select count(*) from shengshi_id_table; 看此表中有多少条记录; 3.select count(*) from barmanagersheng.shengshi_id_table where shengshiqu_code>=5; 看满足此条件的记录有多少? 你说的条件“shengshiqu_code=5”,可能没有满足此条件的记录。
再试试: select tab.* from (select * from barmanagersheng.shengshi_id_table where shengshiqu_code=5) tab where rownum between 3 and 55
select tab.* from (select * from barmanagersheng.shengshi_id_table where shengshiqu_code=5) tab where rownum>=3 and rownum<=5这句话为什么把3改成1就好用,换成其他数就不行呢?我确定数据库中等于5的记录有12条
用“between ...and ...”吧。
先对数据库的shengshi_id_table表建立主键和索引。
^_^>>select rownum,sal from emp where sal>1000; ROWNUM SAL ---------- ---------- 1 1600 2 1600 3 1250 4 2975 5 1250 6 2850 7 2450 8 3000 9 5000 10 1500 11 1100 12 3000 13 1300已选择13行。^_^>>ed 已写入文件 afiedt.buf 1 select rownum,sal from emp where sal>1000 and rownum<20 2 minus 3* select rownum,sal from emp where sal>1000 and rownum<10 ^_^>>/ ROWNUM SAL ---------- ---------- 10 1500 11 1100 12 3000 13 1300^_^>>ed 已写入文件 afiedt.buf 1 select rownum,sal from emp where sal>1000 and rownum<10 2 minus 3* select rownum,sal from emp where sal>1000 and rownum<5 ^_^>>/ ROWNUM SAL ---------- ---------- 5 1250 6 2850 7 2450 8 3000 9 5000
简单查询语句中rownum必须包含1,否则就要用子查询的方法来实现 select * from barmanagersheng.shengshi_id_table where shengshiqu_code=5 and rownum<m minus select * from barmanagersheng.shengshi_id_table where shengshiqu_code=5 and rownum<n n到m应该没问题的啊!
最好定义个“排序字段”。
放在sqlplus中执行试试看吧。
select tab.* from (select * from barmanagersheng.shengshi_id_table where shengshiqu_code=5) tab where rownum>=3 and rownum<=5
2.连接barmanagersheng用户,执行:select count(*) from shengshi_id_table; 看此表中有多少条记录;
3.select count(*) from barmanagersheng.shengshi_id_table where shengshiqu_code>=5;
看满足此条件的记录有多少?
你说的条件“shengshiqu_code=5”,可能没有满足此条件的记录。
select tab.* from (select * from barmanagersheng.shengshi_id_table where shengshiqu_code=5) tab where rownum between 3 and 55
---------- ----------
1 1600
2 1600
3 1250
4 2975
5 1250
6 2850
7 2450
8 3000
9 5000
10 1500
11 1100
12 3000
13 1300已选择13行。^_^>>ed
已写入文件 afiedt.buf 1 select rownum,sal from emp where sal>1000 and rownum<20
2 minus
3* select rownum,sal from emp where sal>1000 and rownum<10
^_^>>/ ROWNUM SAL
---------- ----------
10 1500
11 1100
12 3000
13 1300^_^>>ed
已写入文件 afiedt.buf 1 select rownum,sal from emp where sal>1000 and rownum<10
2 minus
3* select rownum,sal from emp where sal>1000 and rownum<5
^_^>>/ ROWNUM SAL
---------- ----------
5 1250
6 2850
7 2450
8 3000
9 5000
select * from barmanagersheng.shengshi_id_table where
shengshiqu_code=5 and rownum<m
minus select * from barmanagersheng.shengshi_id_table where
shengshiqu_code=5 and rownum<n
n到m应该没问题的啊!