SELECT 总数,未注册,注册率,企业 FROM (SELECT A.*,ROWNUM RN FROM (SELECT * FROM ( SELECT COUNT(1) AS 总数, SUM(CASE WHEN S.REGISTRATION_STATE = 0 THEN 1 ELSE 0 END) AS 未注册, ROUND(((COUNT(1) - SUM(CASE WHEN S.REGISTRATION_STATE = 0 THEN 1 ELSE 0 END))*100 / COUNT(1)), 2) AS 注册率, V2.SITE_PIC_NAME AS 企业, CASE V2.SITE_PIC_NAME WHEN '炼化' THEN 1 WHEN '海外' THEN 2 WHEN '装备' THEN 3 WHEN '勘探' THEN 4 WHEN '销售' THEN 5 WHEN '管道' THEN 6 WHEN '工建' THEN 7 WHEN '工技' THEN 8 WHEN '科研' THEN 9 WHEN '其他' THEN 10 ELSE 11 END PX FROM S_EQUIPMENT_MAIN_TB S INNER JOIN V_VLD_SITE_ENTERPRISE V1 ON V1.VLD_SITE_ID = S.VLD_SITE_ID INNER JOIN VLD_SITE V2 ON V2.VLD_SITE_ID = V1.VLD_ENTERPRISE_ID WHERE S.EQUIP_STATUS IN (1, 2, 3) AND V2.ORG_STATUS='A' AND V2.RENDER_ORDER < 9999 GROUP BY V2.SITE_PIC_NAME ORDER BY PX)) A ) WHERE RN BETWEEN 1 AND 20
可以试试下面的方式,按这种方式的话不会多出一些类似rn的列select * from t_xiaoxi where rowid in(select rid from (select rownum rn,rid from(select rowid rid,cid from t_xiaoxi order by cid desc) where rownum<10000) where rn>9980) order by cid desc;
SELECT COUNT(1) AS 总数,
SUM(CASE
WHEN S.REGISTRATION_STATE = 0 THEN
1
ELSE
0
END) AS 未注册,
ROUND(((COUNT(1) - SUM(CASE
WHEN S.REGISTRATION_STATE = 0 THEN
1
ELSE
0
END))*100 / COUNT(1)),
2) AS 注册率,
V2.SITE_PIC_NAME AS 企业,
CASE V2.SITE_PIC_NAME
WHEN '炼化' THEN 1
WHEN '海外' THEN 2
WHEN '装备' THEN 3
WHEN '勘探' THEN 4
WHEN '销售' THEN 5
WHEN '管道' THEN 6
WHEN '工建' THEN 7
WHEN '工技' THEN 8
WHEN '科研' THEN 9
WHEN '其他' THEN 10
ELSE 11 END PX
FROM S_EQUIPMENT_MAIN_TB S INNER JOIN V_VLD_SITE_ENTERPRISE V1 ON V1.VLD_SITE_ID = S.VLD_SITE_ID INNER JOIN VLD_SITE V2 ON V2.VLD_SITE_ID = V1.VLD_ENTERPRISE_ID WHERE S.EQUIP_STATUS IN (1, 2, 3) AND V2.ORG_STATUS='A' AND V2.RENDER_ORDER < 9999
GROUP BY V2.SITE_PIC_NAME ORDER BY PX)) A ) WHERE RN BETWEEN 1 AND 20
也可以 对 column 设置 visible=false
或者 bind的字段 field 不加上最后两列 都可以
where rowid in(select rid
from (select rownum rn,rid
from(select rowid rid,cid
from t_xiaoxi order by cid desc)
where rownum<10000)
where rn>9980)
order by cid desc;
看我5楼的语句,这种方法应该比rownum的方法要快
参考 http://www.cnblogs.com/hxw/archive/2005/09/11/234619.html