首页 诗词 字典 板报 句子 名言 友答 励志 学校 网站地图
当前位置: 首页 > 教程频道 > 数据库 > SQL Server >

特难的统计语句,50分,马上就给!

2012-01-31 
特难的统计语句,50分,在线等,马上就给!!!!!!id玩家id城池id获取时间失去时间28100000100002007-3-1918:13:

特难的统计语句,50分,在线等,马上就给!!!!!!
id               玩家id         城池id       获取时间                         失去时间  
28100000100002007-3-19   18:13:04     2007-4-22   23:54:48
29100001100012007-4-10   15:57:34     2007-4-23   0:22:56
30100052100002007-4-22   23:54:48     2007-4-23   0:22:56
31100053100002007-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

热点排行