最近在做一个项目，因为考虑的主要是实现查询，所以没有用到Hibernate。直接用的jdbc，里面涉及到分页，所以用到rownum了。
比如，写个最简单的用法:select *from (select *from adjustrequsition a order by a.applydate desc) where rownum<6;这样才是正确的想法，往往像我这样的新手，喜欢这样写：select *from adjustrequsition a where rownum<6 order by a.applydate desc; 这样是最容易范的错误。。因为rownum是先从数据库中任意取的数据，然后在按条件排序。。HOHO。。
下面是我写的我工作4个月来最长的sql代码，毕竟我不是DBA哦。。呵呵
select *
from (select row_number() over(order by t.BEGIN_DATE) ranging,
decode(action_seq,
2,
t.person_name,
3,
(select name from account where id = s1.main_account_seq),
(select name from account where id = s1.main_account_seq)) as debit_name,
decode(action_seq,
7,
t.person_name,
4,
(select name from account where id = s1.main_account_seq),
(select name from account where id = s2.main_account_seq)) as credit_name,
(SELECT action_name FROM action_type WHERE t.action_seq = ID) AS action_name,
decode(action_seq,
2,
decode(bank_seq,
null,
'邮局',
(select bank_name
from bank_info
where id = t.bank_seq)),
(select bank_name from bank_info where id = t.bank_seq)) bankname,
decode(action_seq,
2,
t.card_no,
(select decode(a.email, null, a.mobile, a.email)
from account a, sub_account s
where a.id = s.main_account_seq
and s.id = t.debit_seq)) as debit_no,
decode(action_seq,
4,
t.card_no,
7,
t.card_no,
(select decode(a.email, null, a.mobile, a.email)
from account a, sub_account s
where a.id = s.main_account_seq
and s.id = t.credit_seq)) as credit_no,
t.amount,
to_char(t.BEGIN_DATE, 'YYYY-MM-DD hh24:mi') as begin_date,
t.remark,
t.id,
t.voucher_code,
t.DEBIT_FEE,
t.CREDIT_FEE
from transaction t, sub_account s1, sub_account s2
where t.voucher_code is not null
and exists
(select s.id
from account a, sub_account s
where s.main_account_seq = a.id
and a.account_type = 'B'
and (t.credit_seq = s.id or t.debit_seq = s.id))
and t.DEBIT_SEQ = s1.ID
and t.CREDIT_SEQ = s2.ID
and t.action_seq = 3)
where ranging between 1 and 100