id 玩家id 城池id 获取时间 失去时间
28 100000 10000 2007-3-19 18:13:04 2007-4-22 23:54:48
29 100001 10001 2007-4-10 15:57:34 2007-4-23 0:22:56
30 100052 10000 2007-4-22 23:54:48 2007-4-23 0:22:56
31 100053 10000 2007-4-23 23:54:48 2007-4-24 0:22:56
求：在一个月内某个城池谁占有的时间最长？也就是找出占有城池最长的玩家！这怎么sql语句怎么写啊？？
------解决方案--------------------
是这样?
create table game(id int,play_id int,town_id int,get_time datetime,lose_time datetime)
insert into game
select 28,100000,10000, '2007-3-19 18:13:04 ', '2007-4-22 23:54:48 '
union all select 29,100001,10001, '2007-4-10 15:57:34 ', '2007-4-23 0:22:56 '
union all select 30,100052,10000, '2007-4-22 23:54:48 ', '2007-4-23 0:22:56 '
union all select 31,100053,10000, '2007-4-23 23:54:48 ', '2007-4-24 0:22:56 '
select top 1 id,play_id,town_id,datediff(second,get_time,lose_time) as 'totaltime/秒 ' from game
order by datediff(second,get_time,lose_time) desc
/*
id play_id town_id totaltime/sec
----------- ----------- ----------- -------------
28 100000 10000 2958104
（所影响的行数为 1 行）
*/
------解决方案--------------------
是不是你想要的结果
create table game(id int,play_id int,town_id int,get_time datetime,lose_time datetime)
insert into game
select 28,100000,10000, '2007-3-19 18:13:04 ', '2007-4-22 23:54:48 '
union all select 29,100001,10001, '2007-4-10 15:57:34 ', '2007-4-23 0:22:56 '
union all select 30,100052,10000, '2007-4-22 23:54:48 ', '2007-4-23 0:22:56 '
union all select 31,100053,10000, '2007-4-23 23:54:48 ', '2007-4-24 0:22:56 '
---------------------
select [id],play_id,town_id,datediff(second,get_time,lose_time) as ttime
from game
where datediff(second,get_time,lose_time) in
(select max(datediff(second,get_time,lose_time)) as ttime from game group by town_id)
id play_id town_id ttime
----------- ----------- ----------- -----------
28 100000 10000 2958104
29 100001 10001 1067122