select user_id, sum(datediff(hh,user_login_time,user_long_exit)) as 在线时间 from [user] group by user_id having sum(datediff(hh,user_login_time,user_long_exit))>10
select user_id from [user] u group by user_id having sum(datediff(hh,user_login_time,user_long_exit) > 5
select user_id from [user] u group by user_id where XXXXX having sum(datediff(hh,user_login_time,user_long_exit) > 10
select * from tb where datediff(hh,user_login_time,user_long_exit)>10
select user_id from user where 登录时间 between '2011-10-15' and '2011-10-19 23:59:59' group by user_id having sum(datediff(h,user_login_time,user_long_exit))>10
select id from tb group by id having sum(datediff(hh,user_login_time,user_long_exit))>10
declare @startdate datetime,@enddate datetime set @startdate='2011-10-15' set @enddate='2011-10-19 23:59:59' select [user_id] from [user] where 登录时间 between @startdate and @enddate group by [user_id] having sum(datediff(hh,user_login_time,user_long_exit))>10
select id from tb group by id,convert(varchar(10),user_login_time,120) having sum(datediff(hh,user_login_time,user_long_exit))>10
select id from tb group by id,convert(varchar(10),user_login_time,120) having sum(datediff(hh,user_login_time,user_long_exit))>10不行呀。这只能统计一条记录呀。如果一个用户有多个几个呢?就比如我刚才写的 id login_time login_exit 1 2011-10-20 09:30 2011-10-20 10:10 1 2011-10-20 13:21 2011-10-20 13:33 1 2011-10-20 14:30 2011-10-20 16:12 我要把这个时间都统计出来呀。 比如,用户 1 第一次的时间是40分钟、第二次 8分钟。第三次是96分钟那么统计出来的时间应该是 40 + 8 + 96 = 144分钟呀。好像要用到循环语句才能统计出来。
try:select id,CONVERT(varchar(10),login_time,120),MIN(login_time),MAX(login_exit), datediff(HOUR ,MIN(login_time),MAX(login_exit)) from tb where login_time between 开始时间 and 结束时间 group by id,CONVERT(varchar(10),login_time,120) having datediff(HOUR ,MIN(login_time),MAX(login_exit))>10
以下代码精确到秒,10小时是36000秒,declare @startdate datetime,@enddate datetime set @startdate='2011-10-01 00:00:00' set @enddate='2011-10-04 23:59:59'select user_id, sum(datediff(s,user_login_time,user_long_exit)) as 在线时间(秒) from [user] where user_login_time between @startdate and @enddate group by user_id having sum(datediff(s,user_login_time,user_long_exit))>36000
declare @startdate datetime,@enddate datetime set @startdate='2011-10-15' set @enddate='2011-10-19 23:59:59' select [user_id] from [user] where 登录时间 between @startdate and @enddate group by [user_id] having sum(datediff(mi,user_login_time,user_long_exit))>600
解释一下, group by user_id --> 以用户id进行分组. sum(datediff(s,user_login_time,user_long_exit)) --> 把该用户的所有上线时间相加,精确到秒.
--这个按照分钟算。 select id , sum(datediff(mi,login_time, login_exit)) from tb group by id having sum(datediff(mi,login_time, login_exit)) >= 10 * 60--这个按照分钟算。只考虑同用户同天 select id , sum(datediff(mi,login_time, login_exit)) from tb where datediff(day ,login_time, login_exit) = 0 group by id having sum(datediff(mi,login_time, login_exit)) >= 10 * 60
try:select id,CONVERT(varchar(10),login_time,120),SUM(MINUTE)/60.0 from ( select *,datediff(MINUTE,login_time,login_exit) as MINUTE from tb )tb where login_time between GETDATE()-20 and GETDATE() group by id,CONVERT(varchar(10),login_time,120) having SUM(MINUTE)/60.0>10
怎么再把查询出来的结果(user_id),再统计出一个总数呀?我用count(userid)统计的是上一次查询的结构呀!是不是要用子查询呀?declare @startdate datetime,@enddate datetime set @startdate='2011-10-01 00:00:00' set @enddate='2011-10-04 23:59:59'select user_id, sum(datediff(s,user_login_time,user_long_exit)) as 在线时间(秒) from [user] where user_login_time between @startdate and @enddate group by user_id having sum(datediff(s,user_login_time,user_long_exit))>36000
declare @startdate datetime,@enddate datetime set @startdate='2011-10-01 00:00:00' set @enddate='2011-10-04 23:59:59'-- 按用户统计 select user_id, sum(datediff(s,user_login_time,user_long_exit)) as 在线时间(秒) from [user] where user_login_time between @startdate and @enddate group by user_id having sum(datediff(s,user_login_time,user_long_exit))>36000-- 统计在线时间大于10小时的用户数量. select count(*) from (select user_id, sum(datediff(s,user_login_time,user_long_exit)) as 在线时间(秒) from [user] where user_login_time between @startdate and @enddate group by user_id having sum(datediff(s,user_login_time,user_long_exit))>36000) t
1.输出 5天内的每个人的在线时间和 select user_id,sum(user_login_time-user_long_exit) from tbl where user_login_time>now()-interval 5 days gourp by user_id; 到 list.txt 2.输出超过10小时的记录 awk '{if($2>10*60*60)print $1" "$2}' list.txt >> ret.txt
select * from tb where datediff(hh,user_login_time,user_long_exit)>10
select user_id, sum(datediff(hh,user_login_time,user_long_exit)) as 在线时间 from [user] group by user_id having sum(datediff(hh,user_login_time,user_long_exit))>10
select user_id, getOnlineTime(5, user_login_time, user_long_time) "onlineTime" from user group by user_id having getOnlineTime(5, user_login_time, user_long_time) > 10; 注:函数getOnlineTime(daysNumber, user_login_time, user_long_time) 待实现。
select user_id, getOnlineTime(5, user_login_time, user_long_time) "onlineTime" from user group by user_id having getOnlineTime(5, user_login_time, user_long_time) > 10; 注:函数getOnlineTime(daysNumber, user_login_time, user_long_time) 待实现。
-- 统计在线时间大于10小时的用户数量. select count(*) from (select user_id, sum(datediff(s,user_login_time,user_long_exit)) as 在线时间(秒) from [user] where user_login_time between @startdate and @enddate group by user_id having sum(datediff(s,user_login_time,user_long_exit))>36000) t是否可以这样 select max(rownum), user_id, sum(datediff(s,user_login_time,user_long_exit)) as 在线时间(秒) from [user] where user_login_time between @startdate and @enddate group by user_id having sum(datediff(s,user_login_time,user_long_exit))>36000
select user_id from user group by user_id having datediff(mi,user_login_time,user_long_exit)>=600
user_id,
sum(datediff(hh,user_login_time,user_long_exit)) as 在线时间
from
[user]
group by
user_id
having
sum(datediff(hh,user_login_time,user_long_exit))>10
[user] u
group by user_id
having sum(datediff(hh,user_login_time,user_long_exit) > 5
[user] u
group by user_id
where XXXXX
having sum(datediff(hh,user_login_time,user_long_exit) > 10
set @startdate='2011-10-15'
set @enddate='2011-10-19 23:59:59'
select [user_id]
from [user]
where 登录时间 between @startdate and @enddate
group by [user_id]
having sum(datediff(hh,user_login_time,user_long_exit))>10
id login_time login_exit
1 2011-10-20 09:30 2011-10-20 10:10
1 2011-10-20 13:21 2011-10-20 13:33
1 2011-10-20 14:30 2011-10-20 16:12 我想把这些时间统计出来时间累计大于10个小时的。
having sum(datediff(hh,user_login_time,user_long_exit))>10
select id from tb group by id,convert(varchar(10),user_login_time,120)
having sum(datediff(hh,user_login_time,user_long_exit))>10不行呀。这只能统计一条记录呀。如果一个用户有多个几个呢?就比如我刚才写的
id login_time login_exit
1 2011-10-20 09:30 2011-10-20 10:10
1 2011-10-20 13:21 2011-10-20 13:33
1 2011-10-20 14:30 2011-10-20 16:12 我要把这个时间都统计出来呀。
比如,用户 1 第一次的时间是40分钟、第二次 8分钟。第三次是96分钟那么统计出来的时间应该是 40 + 8 + 96 = 144分钟呀。好像要用到循环语句才能统计出来。
datediff(HOUR ,MIN(login_time),MAX(login_exit))
from tb
where login_time between 开始时间 and 结束时间
group by id,CONVERT(varchar(10),login_time,120)
having datediff(HOUR ,MIN(login_time),MAX(login_exit))>10
set @startdate='2011-10-01 00:00:00'
set @enddate='2011-10-04 23:59:59'select
user_id,
sum(datediff(s,user_login_time,user_long_exit)) as 在线时间(秒)
from [user]
where user_login_time between @startdate and @enddate
group by user_id
having sum(datediff(s,user_login_time,user_long_exit))>36000
declare @startdate datetime,@enddate datetime
set @startdate='2011-10-15'
set @enddate='2011-10-19 23:59:59'
select [user_id]
from [user]
where 登录时间 between @startdate and @enddate
group by [user_id]
having sum(datediff(mi,user_login_time,user_long_exit))>600
group by user_id --> 以用户id进行分组.
sum(datediff(s,user_login_time,user_long_exit)) --> 把该用户的所有上线时间相加,精确到秒.
select id , sum(datediff(mi,login_time, login_exit)) from tb group by id having sum(datediff(mi,login_time, login_exit)) >= 10 * 60--这个按照分钟算。只考虑同用户同天
select id , sum(datediff(mi,login_time, login_exit)) from tb where datediff(day ,login_time, login_exit) = 0 group by id having sum(datediff(mi,login_time, login_exit)) >= 10 * 60
from
(
select *,datediff(MINUTE,login_time,login_exit) as MINUTE from tb
)tb
where login_time between GETDATE()-20 and GETDATE()
group by id,CONVERT(varchar(10),login_time,120)
having SUM(MINUTE)/60.0>10
set @startdate='2011-10-01 00:00:00'
set @enddate='2011-10-04 23:59:59'select
user_id,
sum(datediff(s,user_login_time,user_long_exit)) as 在线时间(秒)
from [user]
where user_login_time between @startdate and @enddate
group by user_id
having sum(datediff(s,user_login_time,user_long_exit))>36000
declare @startdate datetime,@enddate datetime
set @startdate='2011-10-01 00:00:00'
set @enddate='2011-10-04 23:59:59'-- 按用户统计
select
user_id,
sum(datediff(s,user_login_time,user_long_exit)) as 在线时间(秒)
from [user]
where user_login_time between @startdate and @enddate
group by user_id
having sum(datediff(s,user_login_time,user_long_exit))>36000-- 统计在线时间大于10小时的用户数量.
select count(*) from
(select
user_id,
sum(datediff(s,user_login_time,user_long_exit)) as 在线时间(秒)
from [user]
where user_login_time between @startdate and @enddate
group by user_id
having sum(datediff(s,user_login_time,user_long_exit))>36000) t
到 list.txt
2.输出超过10小时的记录
awk '{if($2>10*60*60)print $1" "$2}' list.txt >> ret.txt
user_id,
sum(datediff(hh,user_login_time,user_long_exit)) as 在线时间
from
[user]
group by
user_id
having
sum(datediff(hh,user_login_time,user_long_exit))>10
select user_id, getOnlineTime(5, user_login_time, user_long_time) "onlineTime"
from user
group by user_id
having getOnlineTime(5, user_login_time, user_long_time) > 10;
注:函数getOnlineTime(daysNumber, user_login_time, user_long_time) 待实现。
from user
group by user_id
having getOnlineTime(5, user_login_time, user_long_time) > 10;
注:函数getOnlineTime(daysNumber, user_login_time, user_long_time) 待实现。
select count(*) from
(select
user_id,
sum(datediff(s,user_login_time,user_long_exit)) as 在线时间(秒)
from [user]
where user_login_time between @startdate and @enddate
group by user_id
having sum(datediff(s,user_login_time,user_long_exit))>36000) t是否可以这样
select max(rownum),
user_id,
sum(datediff(s,user_login_time,user_long_exit)) as 在线时间(秒)
from [user]
where user_login_time between @startdate and @enddate
group by user_id
having sum(datediff(s,user_login_time,user_long_exit))>36000