select * from (
select rownum rm,t.assets_id,t.asset_name,t.spec,
(case when t.state !='4' then t.EHICLE_TYPE else t.state end) as state
from t_assets t left join t_assets_property_value p on t.assets_id=p.assets_id
where t.asset_tag like '001003104%'
group by t.assets_id,t.asset_name,t.spec,state,t.EHICLE_TYPE,rownum
)violation
where rownum<=20 and rm>=40
这样分页不起做用...数据不会变.
我试了下是group rownum 的原因不知有什么解决方法没有?
解决方案 »
- 关于一个oracle怪异问题(minus),谁来帮忙解释一下啊
- 求最高效的去重SQL语句
- sql语句得到近五年的年份
- 请教基础问题
- 请问 dbms_output.put_line(i); 是不是向屏幕输出函数? 为什么我看不到的?
- Oracle9i 怎么解决并发问题???????
- ★超急★修复了win2000后,oracle8起不来了!帮帮忙!
- ORA-12154:为何我通过SQL*plu可以连接DB,而通过Report Builder却出现ORA-12154:TNS:Could not resolve..
- Oracle連接字段!
- Oracle 静默安装执行sqlplus报错,求大神帮助~~~
- 100分求一个方法或函数拼接字符串(oracle 10g)
- 数据库视图
from ( select a.*, rownum rnum
from ( SELECT .... FROM .... ORDER BY) a
where rownum <= :MAX_ROWS )
where rnum >= :MIN_ROWS
select rownum rm,t.assets_id,t.asset_name,t.spec,
(case when t.state !='4' then t.EHICLE_TYPE else t.state end) as state
from t_assets t left join t_assets_property_value p on t.assets_id=p.assets_id
where t.asset_tag like '001003104%'
group by t.assets_id,t.asset_name,t.spec,state,t.EHICLE_TYPE,rownum
)violation
where rm between 20 and 40
select rownum rm,t.assets_id,t.asset_name,t.spec,
(case when t.state !='4' then t.EHICLE_TYPE else t.state end) as state
from t_assets t left join t_assets_property_value p on t.assets_id=p.assets_id
where t.asset_tag like '001003104%' rownum<=40
)violation
where rm>=20
(case when t.state !='4' then t.EHICLE_TYPE else t.state end) as state
from t_assets t left join t_assets_property_value p on t.assets_id=p.assets_id
where t.asset_tag like '001003104%'
group by t.assets_id,t.asset_name,t.spec,state,t.EHICLE_TYPE
),t2 as (select rownum rn ,assets_id,asset_name,spec,state from t1 )
select * from t2 where rn between 20 and 40;内层的group by 有问题. 大于小于取得值也不对.
SQL> select deptno,rownum from emp group by deptno,rownum;
DEPTNO ROWNUM
------ ----------
10 7
10 9
10 14
20 1
20 4
20 8
20 11
20 13
30 2
30 3
30 5
30 6
30 10
30 12
14 rows selected
SQL>
from
(select assets_id,asset_name,spec,state,rownum rn
from (
select t.assets_id,t.asset_name,t.spec,
(case when t.state !='4' then t.EHICLE_TYPE else t.state end) as state
from t_assets t left join t_assets_property_value p on t.assets_id=p.assets_id
where t.asset_tag like '001003104%'
group by t.assets_id,t.asset_name,t.spec,state,t.EHICLE_TYPE
) a
where rownum<=40) k
where k.rn>=20