Home  >  Article  >  Database  >  Mysql 按月份为组进行统计排序_MySQL

Mysql 按月份为组进行统计排序_MySQL

WBOY
WBOYOriginal
2016-05-31 08:49:351182browse

在数据库中我们经常用sql语句对表进行简单的统计排序,对于日期字段,我们可以简单的对其进行order。对于复杂一点的可以按日期中的年,月,日分别进行group,order。

按年份进行group,统计排序:

SELECT YERA(established_time),COUNT(*) AS num FROM `table_record` GROUP BY YEAR (established_time)

按月份进行group,统计排序:

方法一:简单实用

SELECT YEAR(established_time) AS 'year' , MONTH(established_time) AS 'month' , COUNT(*) AS 'count' FROM <span style="font-family: Arial, Helvetica, sans-serif;">`table_record` </span><pre name="code" class="sql"><span style="font-family: Arial, Helvetica, sans-serif;">GROUP BY YEAR (established_time) DESC, MONTH(established_time)</span>

效果:

  year   month   count  

  ------   ------      --------

  2014       1      8320

  2014       2      5837

  2014       3     25069

  2014       4     29910

  2014       5     25018

  2014       6     17347

  2014       7         1    

  2013       1      9114

  2013       2      4258

方法二:利用MySql内置字符串连接函数 CONCAT(str1, str2, ...,str3) 。

SELECTCONCAT(YEAR(established_time),',',MONTH( established_time)) AS data_time ,COUNT(*) AS num FROM `table_record` #group by data_timeGROUP BY YEAR(established_time) DESC,MONTH( established_time)

效果:

data_time     num  

---------  --------

2014,1         8320

2014,2         5837

2014,3        25069

2014,4        29910

2014,5        25018

2014,6        17347

2014,7            1   

2013,1         9114

2013,2         4258

注意这里不要用:

<span style="font-family:Microsoft YaHei UI, Microsoft YaHei, SimSun, Segoe UI, Tahoma, Helvetica, sans-serif, Microsoft YaHei, Georgia, Helvetica, Arial, sans-serif, 宋体, PMingLiU, serif;">SELECTYEAR(established_time)+MONTH( established_time) as data_time </span>

这是错误的,它会造成2010+1=2011的这样的错误。

此外,若仅仅用下面语句,是统计的是多年来每月的数据。

<span style="font-family:Microsoft YaHei UI, Microsoft YaHei, SimSun, Segoe UI, Tahoma, Helvetica, sans-serif, Microsoft YaHei, Georgia, Helvetica, Arial, sans-serif, 宋体, PMingLiU, serif;">SELECTMONTH(established_time) AS 'month' , COUNT(*) AS 'count' FROM `tb_gongshangju_record_beijing` GROUP BYMONTH(established_time) DESC</span>


效果如下:

 

month   count  

------  --------

    12     44952

    11     49720

    10     38587

     9     48967

     8     52874

     7     54082

     6     69532

     5     76999

     4     87289

     3     85249

     2     39997

     1     49017

按日期day进行group,统计排序

<span style="font-family:Microsoft YaHei UI, Microsoft YaHei, SimSun, Segoe UI, Tahoma, Helvetica, sans-serif, Microsoft YaHei, Georgia, Helvetica, Arial, sans-serif, 宋体, PMingLiU, serif;">SELECT YEAR(established_time) AS 'year' , MONTH(established_time) AS 'month' ,DAY(established_time) AS 'day', COUNT(*) AS 'count' FROM `table_record` WHERE table_record.`established_time` >= '2014-01-01'GROUP BY YEAR (established_time) DESC, MONTH(established_time) DESC ,DAY(established_time) DESC</span>
 
Statement:
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn