MySQL获得一年中所有月份的统计数据

Question

我必须做一个SQL查询来获取公司一年中所有月份的收入，但有些月份没有记录。

我使用了以下查询：

SELECT COUNT(wp_dgl_stats.datetime_stamp)*wp_dgl_ads.price as incomes, MONTHNAME(wp_dgl_stats.datetime_stamp) as month
FROM wp_dgl_ads
INNER JOIN wp_dgl_stats ON wp_dgl_stats.id_ad = wp_dgl_ads.id
WHERE YEAR(wp_dgl_stats.datetime_stamp) = 2015
GROUP BY MONTHNAME(wp_dgl_stats.datetime_stamp)

我不得不说，wp_dgl_stats包含了一个记录，记录了用户在web特定空间(广告显示)的每一次点击，并引用了广告和日期时间戳。

此查询返回包含收入和确切金额的准确月份。但我也需要在剩下的几个月里得到一个0。

这是怎么做到的呢？

Answer 1

经过多次测试，我找到了一个合适的解决方案。如果有人需要解释的话，我会把它发到这里。

SELECT meses.month, CAST(COUNT(stats.id)*ads.precio AS UNSIGNED) as precio
            FROM
            (
                      SELECT 1 AS MONTH
                       UNION SELECT 2 AS MONTH
                       UNION SELECT 3 AS MONTH
                       UNION SELECT 4 AS MONTH
                       UNION SELECT 5 AS MONTH
                       UNION SELECT 6 AS MONTH
                       UNION SELECT 7 AS MONTH
                       UNION SELECT 8 AS MONTH
                       UNION SELECT 9 AS MONTH
                       UNION SELECT 10 AS MONTH
                       UNION SELECT 11 AS MONTH
                       UNION SELECT 12 AS MONTH
            ) as meses
            LEFT JOIN wp_dgl_stats stats ON meses.month = MONTH(stats.datetime_stamp)
            LEFT JOIN wp_dgl_ads ads ON stats.id_ad = ads.id AND YEAR(stats.datetime_stamp) = '2015'
            GROUP BY meses.month

因为我是一名西班牙开发人员，我需要有西班牙语的月份名称，所以我选择了月份号，并且使用PHP数组，我们可以将月份号转换为他的西班牙语名称。

如果有人有什么问题，请回答，我很乐意回答。

Answer 2

您可以在几个月内加入LEFT/RIGHT：

WITH monthnames AS (SELECT <something that returns a list of the month names> as monthname)
SELECT COALESCE(COUNT(wp_dgl_stats.datetime_stamp)*wp_dgl_ads.price, 0) as incomes, MONTHNAME(wp_dgl_stats.datetime_stamp) as month
FROM wp_dgl_ads
INNER JOIN wp_dgl_stats ON wp_dgl_stats.id_ad = wp_dgl_ads.id
RIGHT JOIN monthnames ON month = monthname
WHERE YEAR(wp_dgl_stats.datetime_stamp) = 2015
GROUP BY MONTHNAME(wp_dgl_stats.datetime_stamp)