SessionID,SequenceID,TalkDuration,ExitState,IVRData,Direction
1320683479 1 206 11 1
1320683479 2 0 1 PJ=2 1
1320683495 1 30 12 1
1320683495 2 20 4 1
1320683503 1 19 12 1
1320683503 2 25 3 1
1320683561 1 0 4 1
1320683561 2 0 7 1
1320683561 3 0 4 1
1320683561 4 49 1 1
1320683568 1 0 7 1
1320683568 2 12 1 2要求:
1.有一个条件SessionID根据此ID找出数据.数据条数只有一条的不管(返回一数据,后续操作就不用了)
2.如果数据大于1条,找出最后一条数据也就是相同SessionID下SequenceID最大的那条记录
3.判断最后那条记录IVRData是否为'',不为空返回此数据(如PJ=2),如果IVRData=''andExitState=1找出此信息的上一条记录,如果上一条记录TalkDuration>0 and Direction=1 返回null
1320683479 1 206 11 1
1320683479 2 0 1 PJ=2 1
1320683495 1 30 12 1
1320683495 2 20 4 1
1320683503 1 19 12 1
1320683503 2 25 3 1
1320683561 1 0 4 1
1320683561 2 0 7 1
1320683561 3 0 4 1
1320683561 4 49 1 1
1320683568 1 0 7 1
1320683568 2 12 1 2要求:
1.有一个条件SessionID根据此ID找出数据.数据条数只有一条的不管(返回一数据,后续操作就不用了)
2.如果数据大于1条,找出最后一条数据也就是相同SessionID下SequenceID最大的那条记录
3.判断最后那条记录IVRData是否为'',不为空返回此数据(如PJ=2),如果IVRData=''andExitState=1找出此信息的上一条记录,如果上一条记录TalkDuration>0 and Direction=1 返回null
//1320683479 1 206 11 '' 1
//1320683479 2 0 1 PJ=2 1
//1320683495 1 30 12 '' 1
//1320683495 2 20 4 '' 1
//1320683503 1 19 12 '' 1
//1320683503 2 25 3 '' 1
//1320683561 1 0 4 '' 1
//1320683561 2 0 7 '' 1
//1320683561 3 0 4 '' 1
//1320683561 4 49 1 '' 1
//1320683568 1 0 7 '' 1
//1320683568 2 12 1 '' 2
declare @i nvarchar(20) ---IVRData 的数据类型
declare @j nvarchar(20) ---SessionID的数据类型set @i=SessionID --给SessionID 赋值 select SessionID,SequenceID,TalkDuration,ExitState,@j=(case when IVRData<>'' then 'PJ=2' else IVRData end),Direction
from table a,
where a.SessionID=@i
and a.SequenceID=(select max(SequenceID) from table b where a.SessionID=b.SessionID )
if @j=''
begin
select top 1 a.*
from table a
where a.SessionID<=@i --等于号自己决定
and a.SequenceID=(select max(SequenceID) from table b where a.SessionID=b.SessionID )
and a.IVRData=''
and a.ExitState=1
and (case when TalkDuration>0 then 1 else 0 end)+(case when Direction=1 then 1 else 0 end)<>2
order by a.SessionID desc
end
试试呐.
declare @j nvarchar(20) ---SessionID的数据类型
declare @k nvarchar(20) ---ExitState的数据类型
set @i=SessionID --给SessionID 赋值 select SessionID,SequenceID,TalkDuration,@k=ExitState,@j=(case when IVRData<>'' then 'PJ=2' else IVRData end),Direction
from table a,
where a.SessionID=@i
and a.SequenceID=(select max(SequenceID) from table b where a.SessionID=b.SessionID )
if (@j='' and @k=1)
begin
select top 1 a.*
from table a
where a.SessionID<=@i --等于号自己决定
and a.SequenceID=(select max(SequenceID) from table b where a.SessionID=b.SessionID )
and a.IVRData=''
and a.ExitState=1
and (case when TalkDuration>0 then 1 else 0 end)+(case when Direction=1 then 1 else 0 end)<>2
order by a.SessionID desc
end[/Quote]
declare @i nvarchar(20) ---IVRData 的数据类型
declare @j nvarchar(20) ---SessionID的数据类型
declare @k nvarchar(20) ---ExitState的数据类型
set @i=SessionID --给SessionID 赋值 select @k=ExitState,@j=(case when IVRData<>'' then 'PJ=2' else IVRData end),Direction
from table a,
where a.SessionID=@i
and a.SequenceID=(select max(SequenceID) from table b where a.SessionID=b.SessionID )
if(@j<>'')
begin
select SessionID,SequenceID,TalkDuration,@ExitState,(case when IVRData<>'' then 'PJ=2' else IVRData end) IVRData ,Direction
from table a,
where a.SessionID=@i
and a.SequenceID=(select max(SequenceID) from table b where a.SessionID=b.SessionID )end
if (@j='' and @k=1)
begin
select top 1 a.*
from table a
where a.SessionID<=@i --等于号自己决定
and a.SequenceID=(select max(SequenceID) from table b where a.SessionID=b.SessionID )
and a.IVRData=''
and a.ExitState=1
and (case when TalkDuration>0 then 1 else 0 end)+(case when Direction=1 then 1 else 0 end)<>2
order by a.SessionID desc
end