我想通过分组SessionID查询最大的TalkDuration那条数据如
TalkDuration SequenceID SessionID
22 1 1
0 2 1
0 1 2
30 2 2
0 1 3
0 2 3
0 1 4
3 1 5要的数据应该是
TalkDuration SequenceID SessionID
22 1 1
30 2 2
0 1 3
0 1 4
3 1 5
要求是
1.相同的sessionid,TalkDuration不相同显示最大的TalkDuration那条数据
2.相同的sessionid,TalkDuration相同只显示一条
3.没有相同sessionid的全部都显示
TalkDuration SequenceID SessionID
22 1 1
0 2 1
0 1 2
30 2 2
0 1 3
0 2 3
0 1 4
3 1 5要的数据应该是
TalkDuration SequenceID SessionID
22 1 1
30 2 2
0 1 3
0 1 4
3 1 5
要求是
1.相同的sessionid,TalkDuration不相同显示最大的TalkDuration那条数据
2.相同的sessionid,TalkDuration相同只显示一条
3.没有相同sessionid的全部都显示
select t.* from tb t
where not exists (select 1 from tb where SessionID = t.SessionID and TalkDuration = t.TalkDuration and SequenceID > t.SequenceID)
select *
from(
select *,ps=row_number() over (partition by SessionID order by TalkDuration desc,SequenceId)
from tb
)t
where px = 1
select t.*
from tb t
where not exists
(select 1 from tb
where SessionID = t.SessionID and ((TalkDuration = t.TalkDuration and SequenceID > t.SequenceID)
or TalkDuration > t.TalkDuration))
这样查询出来的数据是正确的但是不知道如何以此为依据显示出行的所有字段信息
create table tb(TalkDuration int,SequenceID int,SessionID int)
insert into tb
select 22 ,1 ,1 union all
select 0 ,2 ,1 union all
select 0 ,1 ,2 union all
select 30 ,2 ,2 union all
select 0 ,1 ,3 union all
select 0 ,2 ,3 union all
select 0 ,1 ,4 union all
select 3 ,1 ,5
goselect t.*
from tb t
where not exists
(select 1 from tb
where SessionID = t.SessionID and ((TalkDuration = t.TalkDuration and SequenceID > t.SequenceID)
or TalkDuration > t.TalkDuration))/*
要的数据应该是
TalkDuration SequenceID SessionID
22 1 1
30 2 2
0 1 3
0 1 4
3 1 5
*/drop table tb/*************TalkDuration SequenceID SessionID
------------ ----------- -----------
22 1 1
30 2 2
0 2 3
0 1 4
3 1 5(5 行受影响)**********************/select *
from(
select *,px=row_number() over (partition by SessionID order by TalkDuration desc,SequenceId)
from tb
)t
where px = 1/**********************TalkDuration SequenceID SessionID px
------------ ----------- ----------- --------------------
22 1 1 1
30 2 2 1
0 1 3 1
0 1 4 1
3 1 5 1(5 行受影响)
create table je
(TalkDuration int, SequenceID int, SessionID int)insert into je
select 22, 1, 1 union all
select 0, 2, 1 union all
select 0, 1, 2 union all
select 30, 2, 2 union all
select 0, 1, 3 union all
select 0, 2, 3 union all
select 0, 1, 4 union all
select 3, 1, 5
;with t as
(
select row_number() over(partition by SessionID,TalkDuration order by getdate()) rn,
TalkDuration,SequenceID,SessionID from je
)
select a.TalkDuration,a.SequenceID,a.SessionID
from t a
inner join
(select max(TalkDuration) maxTalkDuration,SessionID
from t where rn=1 group by SessionID) b
on a.TalkDuration=b.maxTalkDuration and a.SessionID=b.SessionID
where a.rn=1 order by a.SessionIDTalkDuration SequenceID SessionID
------------ ----------- -----------
22 1 1
30 2 2
0 1 3
0 1 4
3 1 5(5 row(s) affected)