id shijian
1 20130712
2 20130712
3 20130712
4 20130712
1 20130710
1 20130711
2 20130708
3 20130713
4 20130715表t如上,
首先要先查出shijian=20130712的id
select id from t shijian=20130712
然后要从t表中再查询出id在上述查询结果中,且shijian<20130712的数据;
结果要将两次的查询结果都查询出来with t as(
select '1' id ,'20130712' shijian from dual
union all
select '2' id, '20130712' shijian from dual
union all
select '3' id, '20130712' shijian from dual
union all
select '4' id, '20130712' shijian from dual
union all
select '1' id, '20130710' shijian from dual
union all
select '1' id, '20130711' shijian from dual
union all
select '2' id, '20130708' shijian from dual
union all
select '3' id, '20130713' shijian from dual
union all
select '4' id, '20130715' shijian from dual),
m as (select id,shijian from t where shijian='20130712')
select id,shijian
from t
where t.id in(select id from m)
and t.shijian<'20130712'
union all
select id,shijian
from m我现在是想这样查,感觉性能不咋地,
想请教大家,还有其他办法不?
解决方案 »
- 请教一下:sql语句d.phonenumber = t.phonenumber(+)中的(+)是什么意思?
- Oracle11G数据库性能优化工具及手段,数据库安全?
- 两个结果集相互查询
- 急!急!急!监听器找不到了
- 求Oracle格式化SQL脚本命令?
- 一个关于存储过程的问题
- 急!ORACLE角色问题!超简单!来者有分!
- 两个oracle数据库连接传数据的问题
- 连接SQL——PLUS问题,用connect / as sysdba,抱错:服务句柄未初始化
- execute sys.auto_server_pkg.unlock_table('用户名','表名')
- 一条sql语句
- TNS-12560协议适配器错误
select '1' id ,'20130712' shijian from dual
union all
select '2' id, '20130712' shijian from dual
union all
select '3' id, '20130712' shijian from dual
union all
select '4' id, '20130712' shijian from dual
union all
select '1' id, '20130710' shijian from dual
union all
select '1' id, '20130711' shijian from dual
union all
select '2' id, '20130708' shijian from dual
union all
select '3' id, '20130713' shijian from dual
union all
select '4' id, '20130715' shijian from dual)
select id,shijian
from t
where t.shijian<='20130712';
额这个。
我数据弄的不够清楚,我再加几个
with t as(
select '1' id ,'20130712' shijian from dual
union all
select '2' id, '20130712' shijian from dual
union all
select '3' id, '20130712' shijian from dual
union all
select '4' id, '20130712' shijian from dual
union all
select '1' id, '20130710' shijian from dual
union all
select '1' id, '20130711' shijian from dual
union all
select '2' id, '20130708' shijian from dual
union all
select '3' id, '20130713' shijian from dual
union all
select '4' id, '20130715' shijian from dual
union all
select '5' id, '20130711' shijian from dual
union all
select '5' id, '20130710' shijian from dual)
select id,shijian
from t
where t.shijian<='20130712';