select a.*,b.paynoney as newpaynoey
from tablea a
left join tablea b
on a.tickno = b.errortickno
where a.tickstate > 1

------解决方案--------------------
SQL code
select t1.TickNO,t1.TickState,isnull(t2.PayMoney,0) newPayMoney,t1.PayMoney oldPayMoney,t1.ErrorTickNO NewTickNO 
from (select * from A where TickState <> 1) t1 left join a t2 on t1.ErrorTickNO = t2.TickNO

------解决方案--------------------
SQL code

select t1.TickNO,t1.TickState,
       case when t2.TickNO is null then t1.PayMoney else t2.PayMoney end as NewPayMoney,
       case when t2.TickNO is null then '' else t1.PayMoney end as OldPayMoney,
       case when t2.TickNO is null then '' else t2.TickNO end as  NewTickNO
from a t1 left join a t2 on t1.ErrorTickNO = t2.TickNO
where t1.TickState <> 1

------解决方案--------------------
我一楼的关系搞错，4楼更正

------解决方案--------------------
SQL code
create table A(TickNO int,mTickState int,    PayMoney int,  ErrorTickNO int)
insert into A values(1,                   1,                       5, null)                  
insert into A values(2,                   2,                       10,             3 )
insert into A values(3,                   1,                       15, null    )
insert into A values(4,                   3,                       8, null)
go

select t1.TickNO,t1.mTickState,isnull(t2.PayMoney,0) newPayMoney,t1.PayMoney oldPayMoney,t1.ErrorTickNO NewTickNO 
from (select * from A where mTickState <> 1) t1 left join a t2 on t1.ErrorTickNO = t2.TickNO

drop table A

/*
TickNO      mTickState  newPayMoney oldPayMoney NewTickNO   
----------- ----------- ----------- ----------- ----------- 
2           2           15          10          3
4           3           0           8           NULL

（所影响的行数为 2 行）
*/

------解决方案--------------------
表：A  
-------------------------  
TickNO     TickState     PayMoney   ErrorTickNO  
1                   1                       5                  
2                   2                       10             3  
3                   1                       15    
4                   3                       8  
-----------------------------------------  
要取出的数据是  
---------------------------------------  
TickNO         TickState       NewPayMoney                 OldPayMoney     NewTickNO    
2                       2                           15                               10                       3                        
4                       3          

select t1.TickNO ,t1.TickState,  t2.PayMoney  AS NewPayMoney ,t1.PayMoney  AS OldPayMoney ,t2.TickNO As NewTickNO     from A t1,A t2 where t1.ErrorTickNO =t2.TickNO