请教一个SQL语句

Question

请教，我想知道一天内每个小时内上线的总人数，请问该如何去写，能否给个例句或者逻辑，谢谢。

Answer 1

MSSQL语句如下：
select convert(varchar(13),时间字段,121),count(字段) from 表
group by convert(varchar(13),时间字段,121)MySql可能不是这样写的。

Answer 2

select date_format(时间字段,'%Y-%m-%d &H'),count(字段) from 表 
group by date_format(时间字段,'%Y-%m-%d &H')
用临时表，保存1-24时，再与工作表连接

Answer 3

谢谢楼上两位，但是又个这样的问题，我现在有两个字段，logintime和loginout字段，我需要计算出用户是在什么时候登陆和什么时候登出，在登入和登出的时间段才是真正在线的时间段，是否需要进行时间差计算？谢谢。

Answer 4

下面是我写的SQL，请看看，总觉得数据不对
select         case 
         when right(login_time,2) >= '00' and right(logout_time,2) < '01' then '00:00'
         when right(login_time,2) >= '01' and right(logout_time,2) < '02' then '01:00'
         when right(login_time,2) >= '02' and right(logout_time,2) < '03' then '02:00'
         when right(login_time,2) >= '03' and right(logout_time,2) < '04' then '03:00'
         when right(login_time,2) >= '04' and right(logout_time,2) < '05' then '04:00'
         when right(login_time,2) >= '05' and right(logout_time,2) < '06' then '05:00'
         when right(login_time,2) >= '06' and right(logout_time,2) < '07' then '06:00'
         when right(login_time,2) >= '07' and right(logout_time,2) < '08' then '07:00'
         when right(login_time,2) >= '08' and right(logout_time,2) < '09' then '08:00'
         when right(login_time,2) >= '09' and right(logout_time,2) < '10' then '09:00'
         when right(login_time,2) >= '10' and right(logout_time,2) < '11' then '10:00'
         when right(login_time,2) >= '11' and right(logout_time,2) < '12' then '11:00'
         when right(login_time,2) >= '12' and right(logout_time,2) < '13' then '12:00'
         when right(login_time,2) >= '13' and right(logout_time,2) < '14' then '13:00'
         when right(login_time,2) >= '14' and right(logout_time,2) < '15' then '14:00'
         when right(login_time,2) >= '15' and right(logout_time,2) < '16' then '15:00'
         when right(login_time,2) >= '16' and right(logout_time,2) < '17' then '16:00'
         when right(login_time,2) >= '17' and right(logout_time,2) < '18' then '17:00'
         when right(login_time,2) >= '18' and right(logout_time,2) < '19' then '18:00'
         when right(login_time,2) >= '19' and right(logout_time,2) < '20' then '19:00'
         when right(login_time,2) >= '20' and right(logout_time,2) < '21' then '20:00'
         when right(login_time,2) >= '21' and right(logout_time,2) < '22' then '21:00'
         when right(login_time,2) >= '22' and right(logout_time,2) < '23' then '22:00'
         when right(login_time,2) >= '23' and right(logout_time,2) < '00' then '23:00'
        end as during_time ,
        sum(total_number) 
from 
(
select date_format(login_time,'%Y-%m-%d %H') as login_time,
        date_format(logout_time,'%Y-%m-%d %H') as logout_time,
        count(*) as total_number
 from
  (select EA.pid,
         EA.time as login_time,
         EB.time as logout_time
    from eventstats EA,
         eventstats EB 
   where EA.cid = EB.cid 
     and EA.event = 'login'
     and EB.event = 'logout'
     and EA.pid in (select distinct(PR.id) 
                      from principals PR, 
                           passport_character PC 
                     where substring(PR.name,10)=PC.ppid)) as EC
where date(login_time) = date(now())
group by date_format(login_time,'%Y-%m-%d %H'),
        date_format(logout_time,'%Y-%m-%d %H')) as D
group by right(login_time,2),right(logout_time,2);

Answer 5

 (不要高估你的汉语表达能力或者我的汉语理解能力)
   建议你列出你的表结构，并提供测试数据以及基于这些测试数据的所对应正确结果。
   参考一下这个贴子的提问方式http://topic.csdn.net/u/20091130/20/8343ee6a-417c-4c2d-9415-fa46604a00cf.html
   
   1. 你的 create table xxx .. 语句
   2. 你的 insert into xxx ... 语句
   3. 结果是什么样，（并给以简单的算法描述）
   4. 你用的数据库名称和版本(经常有人在MS SQL server版问 MySQL)
   
   这样想帮你的人可以直接搭建和你相同的环境，并在给出方案前进行测试，避免文字描述理解上的误差。   

Answer 6

谢谢提醒，我现在把问题整理一下，平台是MYSQL
CREATE TABLE `eventstats` (                                             
              `event` enum('login','logout','node_restart','node_fault') NOT NULL,  
              `did` int(5) unsigned NOT NULL default '0',                           
              `cid` int(10) unsigned NOT NULL default '0',                          
              `pid` int(10) unsigned NOT NULL default '0',                          
              `time` datetime NOT NULL,                                             
              `ip` int(10) unsigned default '0',                                    
              KEY `i_cid` (`cid`),                                                  
              KEY `i_time` (`time`)                                                 
            ) ENGINE=MyISAM DEFAULT CHARSET=utf8    insert  into `eventstats`(`event`,`did`,`cid`,`pid`,`time`,`ip`) values ('login',8,1,1000,'2009-08-21 18:00:01',168387199),('logout',8,1,1000,'2009-08-21 18:01:11',NULL),('login',8,2,1000,'2009-08-21 18:05:28',168362201),('login',8,3,1001,'2009-08-21 18:05:35',168362201),('logout',8,2,1000,'2009-08-21 18:06:24',NULL),('login',8,4,1000,'2009-08-21 18:06:24',168362201),('logout',8,3,1001,'2009-08-21 18:06:27',NULL),('login',8,5,1001,'2009-08-21 18:06:28',168362201),('login',8,6,1000,'2009-08-21 18:08:36',168387214),('login',8,7,1002,'2009-08-21 18:08:56',168387214),('logout',8,7,1002,'2009-08-21 18:14:17',NULL),('logout',8,6,1000,'2009-08-21 18:14:17',NULL),('login',8,8,1000,'2009-08-21 18:17:03',168387183),('logout',8,8,1000,'2009-08-21 18:17:55',NULL),('login',8,9,1000,'2009-08-21 18:17:56',168387183),('logout',8,9,1000,'2009-08-21 18:19:06',NULL),('login',8,10,1000,'2009-08-21 18:22:53',168362179),('login',8,11,1003,'2009-08-21 18:23:01',168362179),('logout',8,10,1000,'2009-08-21 18:24:53',NULL),('logout',8,11,1003,'2009-08-21 18:25:01',NULL),('login',8,12,1004,'2009-08-24 09:07:41',168362044),('login',8,13,1006,'2009-08-24 09:16:13',168362044),('logout',8,13,1006,'2009-08-24 09:18:14',NULL),('logout',8,12,1004,'2009-08-24 09:18:21',NULL),('login',8,14,1004,'2009-08-24 09:18:22',168362044),('login',8,15,1006,'2009-08-24 09:18:25',168362044),('logout',8,4,1000,'2009-08-24 09:20:04',NULL),('logout',8,5,1001,'2009-08-24 09:20:07',NULL),('logout',8,14,1004,'2009-08-24 09:21:30',NULL),('login',8,16,1005,'2009-08-24 09:21:31',168362044),('logout',8,15,1006,'2009-08-24 09:22:05',NULL),('login',8,17,1008,'2009-08-24 09:22:37',168362044),('login',8,18,1007,'2009-08-24 09:23:11',168362201),('login',8,19,1010,'2009-08-24 09:23:20',168362201),('login',8,20,1004,'2009-08-24 09:26:02',168362044),('login',8,21,1006,'2009-08-24 09:26:06',168362044),('login',8,22,1011,'2009-08-24 09:28:00',168362492),('login',8,23,1012,'2009-08-24 09:29:15',168362492),('logout',8,23,1012,'2009-08-24 09:32:15',NULL),('logout',8,22,1011,'2009-08-24 09:32:20',NULL),('login',8,24,1013,'2009-08-24 09:34:01',168386517),('logout',8,24,1013,'2009-08-24 09:35:11',NULL),('login',8,25,1013,'2009-08-24 09:35:31',168386517),('logout',8,18,1007,'2009-08-24 09:36:23',NULL),('login',8,26,1007,'2009-08-24 09:36:24',168362201),('logout',8,19,1010,'2009-08-24 09:36:26',NULL),('login',8,27,1010,'2009-08-24 09:36:26',168362201),('login',8,28,1014,'2009-08-24 09:36:27',168362179),('login',8,29,1017,'2009-08-24 09:36:45',168362179),('login',8,30,1011,'2009-08-24 09:37:42',168362492),('login',8,31,1012,'2009-08-24 09:37:45',168362492)
我需要的结果：一天内每小时的在线人数
time count(*) 
00:00 10       
01:00 20      
02:00 4        
03:00 8        
04:00 10       
05:00 12       
06:00 20       
. .        
.               .        
.               .        
.               .        
.               .        
.               .        
23:00           18       
上面的00：00表示从0点到1点的时间内，01：00表示1点到2点的时间内，依次类推，23：00表示23点到0点的时间内。

Answer 7

login是登陆时间
logout是登出时间