现在有两张没有任何关系的表,appointment表 和 consult表;
两个表都有字段:id,title,time ,organiname (id,一个是varchar2类型,一个是number类型)
现在需要: (1)约会和征友在一个页面的表格内根据时间顺序显示。
(2)每条记录增加字段,用来区别是date记录还是consult记录。
(3) 因为页面显示的原因,要对查询分页。
例如: id, title, time, name type
1011 heheheh 2005/11/23 aa date
2011 fsdfs 2005/10/10 bb date
1023 fasfasf 2005/05/01 cc consultSELECT * FROM (SELECT TO_CHAR(ID) AS ID,ORGANIGER AS ORG,TITLE,TIME,ORGANINAME AS NAME ,'DATE' AS TYPE FROM APPOINTMENT
UNION
ALL SELECT ID AS ID,ORGANIGER AS ORG, TITLE,PUBLISHTIME AS TIME,ORGANINAME AS NAME,'CONSULT' AS TYPE FROM CONSULT ORDER BY TIME DESC) WHERE ROWNUM <30
MINUS
SELECT * FROM (SELECT TO_CHAR(ID) AS ID,ORGANIGER AS ORG,TITLE,TIME,ORGANINAME AS NAME ,'DATE' AS TYPE FROM APPOINTMENT
UNION ALL
SELECT ID AS ID,ORGANIGER AS ORG, TITLE,PUBLISHTIME AS TIME,ORGANINAME AS NAME,'CONSULT' AS TYPE FROM CONSULT ORDER BY TIME DESC) where rownum < 20
现在是我写的查询语句,但我一直担心效率问题,请各位帮忙看下,怎么提高,或者哪里写的不恰当,同时时间的排序是混乱的。
两个表都有字段:id,title,time ,organiname (id,一个是varchar2类型,一个是number类型)
现在需要: (1)约会和征友在一个页面的表格内根据时间顺序显示。
(2)每条记录增加字段,用来区别是date记录还是consult记录。
(3) 因为页面显示的原因,要对查询分页。
例如: id, title, time, name type
1011 heheheh 2005/11/23 aa date
2011 fsdfs 2005/10/10 bb date
1023 fasfasf 2005/05/01 cc consultSELECT * FROM (SELECT TO_CHAR(ID) AS ID,ORGANIGER AS ORG,TITLE,TIME,ORGANINAME AS NAME ,'DATE' AS TYPE FROM APPOINTMENT
UNION
ALL SELECT ID AS ID,ORGANIGER AS ORG, TITLE,PUBLISHTIME AS TIME,ORGANINAME AS NAME,'CONSULT' AS TYPE FROM CONSULT ORDER BY TIME DESC) WHERE ROWNUM <30
MINUS
SELECT * FROM (SELECT TO_CHAR(ID) AS ID,ORGANIGER AS ORG,TITLE,TIME,ORGANINAME AS NAME ,'DATE' AS TYPE FROM APPOINTMENT
UNION ALL
SELECT ID AS ID,ORGANIGER AS ORG, TITLE,PUBLISHTIME AS TIME,ORGANINAME AS NAME,'CONSULT' AS TYPE FROM CONSULT ORDER BY TIME DESC) where rownum < 20
现在是我写的查询语句,但我一直担心效率问题,请各位帮忙看下,怎么提高,或者哪里写的不恰当,同时时间的排序是混乱的。
换成
select * from (SELECT a.*,rownum t FROM (SELECT TO_CHAR(ID) AS ID,ORGANIGER AS ORG,TITLE,TIME,ORGANINAME AS NAME,'DATE' AS TYPE FROM APPOINTMENT UNION ALL SELECT ID AS ID,ORGANIGER AS ORG, TITLE,PUBLISHTIME AS TIME,ORGANINAME AS NAME,'CONSULT' AS TYPE FROM CONSULT ORDER BY TIME DESC) a WHERE ROWNUM <30) where t>=20
UNION ALL SELECT ID AS ID,ORGANIGER AS ORG, TITLE,PUBLISHTIME AS TIME,ORGANINAME AS NAME,'CONSULT' AS TYPE FROM CONSULT ORDER BY TIME DESC) WHERE ROWNUM <30 )WHERE T>20;
这个我测试了,用的时间是0.015,我上面的方法用的时间是0.062,却是提高了很多。大家还有没有更好的方法。
Select * from tab where rowid in (SELECT r FROM (SELECT m.rowid r, rownum t FROM (SELECT a.rowid FROM tab a order by col1 desc) m WHERE rownum < 30) WHERE t >= 10) order by col1 desc;