这样的sql要如何写好_Oracle管理

这样的sql要怎么写好？

SQL code

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')

------解决方案--------------------
有些看不明白，但说说我的想法。
1、max（）是根据ascii进行逐个比较，所以其得到的值应该是唯一的；
2、外面的条件和里面的条件一样，

所以可否用order by 呢？

SQL code

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;
------解决方案--------------------
同楼上，是返回2条日期最大的两条记录吧
------解决方案--------------------
不要让别人接受你的思路,建议把原数据和结果数据贴出来!
------解决方案--------------------
SQL codeselect *  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 );