MYSQL每小时​​数据内连接

Question

表用户

username |   name   | role
admin    | tony     | 1
sales    | roy      | 2
custo    | boy      | 3

日志统计表

logdate             |   ip              | username
2015/12/12 18:02:22 | 192.168.1.11      | admin
2015/11/12 21:02:44 | 192.168.1.101     | sales
2015/11/12 22:04:27 | 192.168.1.121     | custo

我想要的输出是:

datetime              | admin   | sales     | custo
(currentdate) 18:00:0 | 1       | 0         | 0
(currentdate) 21:00:0 | 0       | 1         | 0
(currentdate) 21:00:0 | 0       | 0         | 1

当显示系统中每个角色的每小时数据时，我需要(当前日期)根据ARMCHART要求进行调整的输出。

到目前为止我还停留在

SELECT CONCAT(Curdate(), ' ', HOUR(logdate)) as 'hourly', 
(select count('logstat.username') 
   from logstat inner join user on user.username=logstat.username 
   and user.idrole='1' 
   where logdate BETWEEN (CURDATE() + INTERVAL (SELECT hourly(NOW())) hourly - INTERVAL 23 hourly) 
   AND ((CURDATE() + INTERVAL (SELECT hourly(NOW())) hourly)))as 'Admin' 
   FROM `logstat` GROUP BY `hourly`

Answer 1

目前我找到了这个代码，

SELECT CONCAT(Curdate(),' ',DATE_FORMAT(logdate,'%H')) as 'hourly',
     (select count('logstat.username') from logstat inner join user on user.username=logstatus.username and user.idrole='1' where HOUR(logdate) = HOUR(hourly))as 'Admin',
    (select count('logstat.username') from logstat inner join user on user.username=logstatus.username and user.idrole='2' where HOUR(logdate) = HOUR(hourly))as 'Sales',
    (select count('logstat.username') from logstat inner join user on user.username=logstatus.username and user.idrole='3' where HOUR(logdate) = HOUR(hourly))as 'Custo'   
FROM `logstat` GROUP BY `hourly`

希望对使用 amchart 有所帮助。