急...急....急....高分求一查询SQL....2

[jeje]

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

[开着拖拉机泡妞]

这哪才一个条件

[jeje]

第一条不用管,主要是二,三条...

[jeje]

大牛牛们还在休息么...

[gogodiy]

楼主重新调整下格式，按照你现在的格式，我们无法将值和字段名对应起来创建数据表

[jeje]

//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

[jeje]

郁闷大牛们都还在睡觉...

[tg_hgh163]

[Quote=引用楼主 jejexu 的回复:]
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
13……
[/Quote]

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

试试呐.

[tg_hgh163]

[Quote=引用 7 楼 tg_hgh163 的回复:]

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

[/Quote]


修改下

declare @i nvarchar(20) ---IVRData 的数据类型
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]

[tg_hgh163]

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