最近在做一個(gè)項(xiàng)目，因?yàn)榭紤]的主要是實(shí)現(xiàn)查詢，所以沒(méi)有用到Hibernate。直接用的jdbc，里面涉及到分頁(yè)，所以用到rownum了。
比如，寫(xiě)個(gè)最簡(jiǎn)單的用法:select *from (select *from adjustrequsition a order by a.applydate desc) where rownum<6;這樣才是正確的想法，往往像我這樣的新手，喜歡這樣寫(xiě)：select *from adjustrequsition a where rownum<6 order by a.applydate desc; 這樣是最容易范的錯(cuò)誤。。因?yàn)閞ownum是先從數(shù)據(jù)庫(kù)中任意取的數(shù)據(jù)，然后在按條件排序。。HOHO。。
下面是我寫(xiě)的我工作4個(gè)月來(lái)最長(zhǎng)的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