Mysql 分别按月, 日为组group,进行统计排序order
时间:2022-03-10 17:51
在数据库中我们经常用sql语句对表进行简单的统计排序,对于日期字段,我们可以简单的对其进行order。对于复杂一点的可以按日期中的年,月,日分别进行group,order。
SELECT YEAR(established_time) AS 'year' , MONTH(established_time) AS 'month' , COUNT(*) AS 'count'
FROM `table_record`
GROUP BY YEAR (established_time) DESC, MONTH(established_time)
效果:
year month count
------ ------ -------
2014 1 8320
2014 2 5837
2014 3 25069
2014 4 29820
2014 5 25060
2014 6 17615
2014 7 1
2013 1 9114
2013 2 4258
方法二:利用 MySql内置字符串连接函数 CONCAT(str1, str2, ...,str3) 。
SELECT YEAR(established_time)+MONTH( established_time) as data_time这是错误的,它会造成2010+1=2011的这样的错误。
SELECT MONTH(established_time) AS 'month' , COUNT(*) AS 'count'
FROM `tb_gongshangju_record_beijing` GROUP BY MONTH(established_time) DESC
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
Mysql 分别按月, 日为组group,进行统计排序order,布布扣,bubuko.com