求同一表关联SQL

Question

表：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的取出关联的信息，
没有则直接取出本条信息即可。
谢谢!

Answer 1

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

Answer 2

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

Answer 3

declare @t table(TickNO int,TickState int,PayMoney int,ErrorTickNO  int)
insert @t select 1          ,         1     ,                  5  ,0                 
insert @t select 2       ,            2     ,                  10  ,           3 
insert @t select 3         ,          1     ,                  15   ,0  
insert @t select 4        ,           3  ,                     8 ,0
select a.*,b.PayMoney as newpaynoey
from @t a
left join @t b
on b.tickno = a.errortickno
where a.tickstate > 1
/*
TickNO      TickState   PayMoney    ErrorTickNO newpaynoey  
----------- ----------- ----------- ----------- ----------- 
2           2           10          3           15
4           3           8           0           NULL
*/

Answer 4

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

Answer 5

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          3
4           3           0           8           NULL（所影响的行数为 2 行）
*/

Answer 6

--这个行了.
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 , t2.PayMoney newPayMoney , case when t2.PayMoney is not null then t1.PayMoney else null end 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          3
4           3           NULL        NULL        NULL（所影响的行数为 2 行）
*/

Answer 7

表：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     

Answer 8

select TickNO,
                 TickState,
                 NewPayMoney=(select PayMoney from A where TickNO=a.ErrorTickNO),
                 PayMoney,
                 NewTickNO=(case when ErrorTickNO is not null then ErrorTickNO end) 
          from A a where a.TickState<>1 

Answer 9

发现问题了,原来我的TickNO中有空值.
谢谢楼上各位兄弟,若能加分,加分想送!谢谢

Answer 10

此帖发出时间不到5天不能加分.Sorry