mysql - 在单独的行中检索重复的月份数据

Question

MySql查询是这样的

SELECT MONTHNAME(access_date) as date,
  DATE_FORMAT( access_date,  '%d/%m/%Y' ) as month_date, 
  COUNT( log_id ) as total_count
    FROM user_activity_log
    WHERE dam_id = (
        SELECT dam_id 
            FROM dam_content_details
            WHERE content_type= 'userLogin'
        ) 
    AND CAST(access_date as DATE) BETWEEN '2012-09-01'
        AND '2014-01-01'
    GROUP BY MONTH( access_date )
    ORDER BY access_date ASC

1. 我遇到的问题是 2012 年的 11 月和 12 月的数据与 2013 年的 11 月和 12 月相加并显示在一行中。但我想为此单独排行。

2. 第二个是它只显示前 12 个月而不是 2014 年 1 月。

我的示例输出是这样的

date        month_date  total_count
---------  ------------ -----------
September   15/09/2012  7
October     05/10/2012  34
November    05/11/2012  21
December    07/12/2012  49
January     01/01/2013  45
February    02/02/2013  107
March       01/03/2013  158
April       01/04/2013  100
May         01/05/2013  393
June        01/06/2013  272

Answer 1

试试这个:

SELECT Monthname(access_date)               AS DATE, 
       Date_format(access_date, '%d/%m/%Y') AS month_date, 
       Count(log_id)                        AS total_count 
FROM   user_activity_log 
WHERE  dam_id = (SELECT dam_id 
                 FROM   dam_content_details 
                 WHERE  content_type = 'userLogin') 
       AND Cast(access_date AS DATE) BETWEEN '2012-09-01' AND '2014-01-01' 
GROUP  BY Year(access_date), 
          Month(access_date) 
ORDER  BY access_date ASC 

或

使用JOIN查询

SELECT Monthname(ual.access_date)               AS DATE, 
       Date_format(ual.access_date, '%d/%m/%Y') AS month_date, 
       Count(DISTINCT ual.log_id)               AS total_count 
FROM   user_activity_log ual 
       INNER JOIN dam_content_details dcd 
               ON ual.dam_id = dcd.dam_id 
                  AND dcd.content_type = 'userLogin' 
WHERE  ual.access_date BETWEEN '2012-09-01' AND '2014-01-01' 
GROUP  BY Year(ual.access_date), 
          Month(ual.access_date) 
ORDER  BY ual.access_date ASC