使用SQL语句暗藏查询后的某一列,求大神教教
使用SQL语句隐藏查询后的某一列,求大神教教
我在写一个方法,用于将数据库里面查询到的数据经行分页,以下是我的SQL语句:
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
使用的是Oracle数据库操作的,查询后的结果如下:
怎么去掉PX 和PN列 ,因为这写的是一个公共的分页方法,所以可能有好多表都要用到这一个方法,请问怎么去掉那两列,让那两列不显示
------解决思路----------------------
------解决思路----------------------
可以试试下面的方式,按这种方式的话不会多出一些类似rn的列
我在写一个方法,用于将数据库里面查询到的数据经行分页,以下是我的SQL语句:
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
使用的是Oracle数据库操作的,查询后的结果如下:
怎么去掉PX 和PN列 ,因为这写的是一个公共的分页方法,所以可能有好多表都要用到这一个方法,请问怎么去掉那两列,让那两列不显示
------解决思路----------------------
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;