sql拆分表再合并
Tb1:
ID PID Type Amount paymentDate
1 1 0 4000 2013-5-1
2 1 0 6000 2013-5-2
3 1 1 4000 2013-5-2
Tb2:
ID PID P2Amount P2paymentDate P3Amount P3paymentDate
1 1 1500 2013-4-30 3000 2013-4-29
结果:
统一用tb1的字段,根据payementDate进行从小到大排序,tb1只取Type=0的值
PID Type Amount payementDate
1 null 3000 2013-4-29
1 null 1500 2013-4-30
1 0 4000 2013-5-1
1 0 6000 2013-5-2 sql
[解决办法]
select pid,null as[type],p2amount as amount,p2paymentdate as payementdate
from tb2
union all
select pid,null as[type],p3amount as amount,p3paymentdate as payementdate
from tb2
union all
(
select PID, Type, Amount ,payementDate
from tb1
where type=0
)
[解决办法]
select PID ,Type ,Amount ,payementDate from tb1 where Type=0
union all
select PID,null as type,P2Amount as Amount,P2paymentDate as payementDate from tb2
union all
select PID,null as type,P3Amount as Amount,P3paymentDate as payementDate from tb2
order by payementDate