====================================
1.开户日期排序,去掉开户日期为0的垃圾数据,设置 sql中展示条数,和页数,展示要求的数据信息
select * from (
select ceil(rownum/15) page,t2.* from (
select * from o_dd_mst t1 where t1.opn_date<>0 order by t1.opn_date )t2 )
where page=2
第二种方式:
Select rownum as num ,t1.* from
(select * from o_dd_mst t where t.opn_date<>0 order by t.opn_date )t1 )t2 where t2.num >15*(6-1)and t2.num <=15*6
2.行转列
select max(t1.cif_no) 客户号,max(t2.name) 客户名称,
sum(decode(rownum,1,t1.ac_id,0)) 账户号1,
sum(decode(rownum,1,t1.bal,0)) 账户余额,
sum(decode(rownum,2,t1.ac_id,0)) 账户号2,
sum(decode(rownum,2,t1.bal,0)) 账户余额,
sum(decode(rownum,3,t1.ac_id,0)) 账户号3,
sum(decode(rownum,3,t1.bal,0)) 账户余额,
sum(decode(rownum,4,t1.ac_id,0)) 账户号4,
sum(decode(rownum,4,t1.bal,0)) 账户余额
from o_dd_mst t1
left join o_mdm_ac_rel t2
on t1.ac_id=t2.ac_id
and t1.ac_seqn=t2.ac_seqn
where cif_no = 10066744
group by t1.cif_no
方式二:子查询 多表关联 应该是第一种考虑方式
Select distinct t1.cif_no ,t2.name ,t3.ac_id,t3.bal
from o_dd_mst t1,o_mdm_ac_rel t2
(select ac_id,bal,cif_no from o_dd_mst t1 where cif_no = 10066744) t3,
(select ac_id,bal,cif_no from o_dd_mst t1 where cif_no = 10066744) t4,
where t1.ac_id=t2.ac_id
and cif_no = 10066744
and t3.cifno=t1.cifno
And t3.cifno<>t4.cifno
group by t1.cif_no
==========================