日期:2014-05-18  浏览次数:20568 次

急...急....急....高分求一查询SQL
我想通过分组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的全部都显示

------解决方案--------------------
SQL code

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
go

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

/*
要的数据应该是
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 行受影响)

------解决方案--------------------
SQL code

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.SessionID

TalkDuration SequenceID  SessionID
------------ ----------- -----------
22           1           1
30           2           2
0            1           3
0            1           4
3            1           5

(5 row(s) affected)