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


    求按小时统计的语句
    表: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)
  • 相关阅读:
    编程实现SQL Server数据库导入导出操作
    C#正则表达式入门
    Winform实现窗体抖动的效果代码
    js获取当前日期,格式为YYYYMMDD
    XML基本知识及其技术指南
    WPF学习心得
    脚本调试
    标题:VS2008简体中文专业版
    使用 XML Schema 定义元素的基本知识2
    C#操作xml文件入门
  • 原文地址:https://www.cnblogs.com/onetwo/p/5650278.html
Copyright © 2020-2023  润新知