• 求按小时统计的语句,该怎么处理


    求按小时统计的语句
    表:issue 字段:
    id:1
    time:2012-04-01 08:15:00
    count:12
    
    。。。。。
    
    按小时统计count总数,结果如下
    1 2012-04-01 8:00~9:00 134
    2 2012-04-01 9:00~10:00 134
    
    
    ------解决方案--------------------
    如果结果前面的数字是你要的计数的话,那自己另加一个计数列就是了:
    SQL code
    mysql> select * from issue;
    +----+---------------------+-------+
    | id | time                | count |
    +----+---------------------+-------+
    |  1 | 2012-04-01 08:15:00 |    12 |
    |  2 | 2012-04-01 09:15:00 |    12 |
    |  3 | 2012-04-01 08:25:00 |    12 |
    |  4 | 2012-04-01 10:15:00 |    12 |
    |  5 | 2012-04-01 09:15:00 |    12 |
    +----+---------------------+-------+
    5 rows in set (0.00 sec)
    
    mysql> select concat(date_format(time, "%Y-%m-%d %k:00~"),  hour(time)+1, ":00")
     as 'time', sum(`count`) as 'count'
        -> from issue
        -> group by left(time, 13);
    +------------------------+-------+
    | time                   | count |
    +------------------------+-------+
    | 2012-04-01 8:00~9:00   |    24 |
    | 2012-04-01 9:00~10:00  |    24 |
    | 2012-04-01 10:00~11:00 |    12 |
    +------------------------+-------+
    3 rows in set (0.00 sec)
    
    ------解决方案--------------------group by left(time, 13);
    
    ------解决方案--------------------
    
    select DATE_FORMAT(time,'%Y-%m-%d %H:00'),sum(count)
    from issue
    group by DATE_FORMAT(time,'%Y-%m-%d %H:00')
    
    ------解决方案--------------------
    select Date_format(time,'%Y-%M-%d %H:00'),SUM(COUNT)
    FROM issue
    group byy DATA_FORMAR(time,'%Y-%m-%d %h:00)
  • 相关阅读:
    linux centos7 如何安装mysql
    Json转换 在java中的应用
    最最简单的spring mvc + Maven项目
    windows下 申请免费ssl证书的方法 (letsencrypt)
    PowerDesigner中Table视图同时显示Code和Name
    在linux中 部署 mongo 数据库服务端
    Java保存图片到数据库Blob格式
    MyBatis mapper记录
    vue防止多次点击,重复请求
    金额的单位转换,元转分
  • 原文地址:https://www.cnblogs.com/onetwo/p/5650278.html
Copyright © 2020-2023  润新知