MySQL筛选结果按月和年 [英] MySQL filter results by month and year

问题描述

我的表格显示各个国家/地区所进行的搜寻量，以及这些国家/地区的拍摄日期，我想要一份查询资料，显示我每月在2012年和2012年进行的搜寻。 2013年，我需要为月，年和搜索次数添加单独的列。我以前使用下面的查询，返回每周的搜索量，但我想更改为只是每月的搜索量2012年& 2013。

My table shows the amount of searches taken by various countries and the date on which they were taken, I want a query that shows me the searches taken each month for both the years 2012 & 2013, I will need to add separate columns for month, year and number of searches. I have previously used the query below which returns the amount of searches taken each week, but I would like to change it to just the searches taken each month for years 2012 & 2013.

SELECT COUNT(*) as `count`,
      `region`, 
       DATE(NOW()) as `week_ending` 
   FROM my_stores.stats 
        WHERE `date` > DATE_ADD(DATE(NOW()), INTERVAL -1 WEEK) 
               AND 
              `date` < DATE(NOW()) 
 GROUP BY `region`, DATE(NOW());

推荐答案

只需使用 MONTH（）和 YEAR（）函数

SELECT COUNT(*) as `count`,
      `region`, 
       YEAR(`date`) as `year`,
       MONTH(`date`)  as `month`
   FROM my_stores.stats 
        WHERE  YEAR(`date`) in (2012,2013)
 GROUP BY `region`, YEAR(`date`),MONTH(`date`)

这篇关于MySQL筛选结果按月和年的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！