select *
from t_standard_dtl d
where trim(d.acc) = '111111'
and trim(d.sub) = '6'
and d.txn_type = '2'
and d.confirm_flag = '0'
and d.txn_date || d.txn_time =
(select max(txn_date || txn_time)
from t_standard_dtl
where trim(acc) = '111111'
and trim(sub) = '6'
and d.txn_type = '2'
and d.confirm_flag = '0')
from t_standard_dtl d
where trim(d.acc) = '111111'
and trim(d.sub) = '6'
and d.txn_type = '2'
and d.confirm_flag = '0'
and d.txn_date || d.txn_time =
(select max(txn_date || txn_time)
from t_standard_dtl
where trim(acc) = '111111'
and trim(sub) = '6'
and d.txn_type = '2'
and d.confirm_flag = '0')
解决方案 »
- 请教个oracle sql 写法?
- 急.求一存储过程~!
- 各位,帮帮忙,为什么在Command Window 直接执行脚本不能创建触发器啊,在线等!!谢谢了,
- 求解:一些机器使用OraOLEDB的问题,请高手帮忙
- to_date('2002-01-14 16:34:34','YYYY-MM-DD hh:mi:ss'),显示 ORA-01849: 小时值必须介于 1 和 12 之间 ,如何解决呀?
- sqlserver的table导出到oracle的问题
- Unixware7.1下Oracle8.1.5的连接数问题!!急急急急急!!!在线!!1
- 这两个sql语句是否完全等价!!
- 關于crystal report 開發問題
- oracle数据库提示尝试加载Oracle客户端库时引发BadImageFormatException.如果在安装32位Oracle客户端组件的情况下64位模拟
- oracle 存储过程 如何按顺序执行
- ora012535 TNS:operation time out 怎么解决?
1、max()是根据ascii进行逐个比较,所以其得到的值应该是唯一的;
2、外面的条件和里面的条件一样,所以可否用order by 呢?
select *
from (select *
from t_standard_dtl d
where trim(d.acc) = '111111'
and trim(d.sub) = '6'
and d.txn_type = '2'
and d.confirm_flag = '0'
order by max(txn_date || txn_time) desc)
where rownum <2;
from t_standard_dtl d
where trim(d.acc) = '111111'
and trim(d.sub) = '6'
and d.txn_type = '2'
and d.confirm_flag = '0'
and d.txn_date || d.txn_time =
(select max(txn_date || txn_time)
from t_standard_dtl
where trim(acc) = '111111'
and trim(sub) = '6'
and d.txn_type = '2'
and d.confirm_flag = '0')-- 上面的SQL可以看出:既然父查询和子查询是查的同一个表,
-- 那么你这样的话,需要析取两次:父查询析取一次、子查询析取一次!(如果你的表有1000万行记录,析取后,只有1000条记录)-- 我们可以用表别名,这样只需要对原表析取一次
with a as ( select *
from t_standard_dtl d
where trim(d.acc) = '111111'
and trim(d.sub) = '6'
and d.txn_type = '2'
and d.confirm_flag = '0' )
select a1.*
from a a1 where exists (select 1 from a a2
having max(a2.txn_date)=a1.txn_date
and max(a2.txn_time)=a1.txn_date );
只是要取出trim(d.acc) = '111111'
and trim(d.sub) = '6'
and d.txn_type = '2'
and d.confirm_flag = '0'
这些条件下,时间最大的那条记录
所以采用这个max(txn_date || txn_time)