求同一表关联SQL
表：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
-------------------
表A中的ErrorTickNO与表A中的TickNO关联，即表示表A中的第二条记录被第三号记录重打了,
要求取出所有票据状态(TickState)不为1 的记录，有ErrorTickNO的取出关联的信息，
没有则直接取出本条信息即可。
谢谢!

[解决办法]

SQL code

select a.*,b.paynoney as newpaynoeyfrom tablea aleft join tablea bon a.tickno = b.errorticknowhere 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  NewTickNOfrom a t1 left join a t2 on t1.ErrorTickNO = t2.TickNOwhere 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)goselect 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.TickNOdrop table A/*TickNO      mTickState  newPayMoney oldPayMoney NewTickNO   ----------- ----------- ----------- ----------- ----------- 2           2           15          10          34           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