mysql - sql为每个月选择一条记录，其中包含该月记录的总和

Question

我有一个包含“约会表”和“服务表”的数据库。每个 appt 都有一个服务，每个服务都有一个价格。我想要的是一个查询，它将始终返回 12 行(每个月一行)并包含月份 appts 的总和(基于它的服务 ID)。到目前为止，我有:

select sum(service_price) as monthly_total, 
       year(appt_date_time) as year, 
       monthname(appt_date_time) as month 
from appt_tbl 
     join services_tbl on appt_tbl.service_id = services_tbl.service_id 
group by month(appt_date_time), 
         year(appt_date_time) 
order by month(appt_date_time) asc;

目前，这会返回如下内容:

+---------------+------+-------+
| monthly_total | year | month |
+---------------+------+-------+
|        120.00 | 2012 | July  |
+---------------+------+-------+

问题是如果一个月没有任何 appts，我不会在查询中返回那个月。我希望那个月有一个记录，只是让它的“monthly_total”等于零。

下面是我希望查询返回的内容:

+---------------+------+-------+
| monthly_total | year | month |
+---------------+------+-------+
|          0.00 | 2012 | Jan   |
+---------------+------+-------+
|          0.00 | 2012 | Feb   |
+---------------+------+-------+
|          0.00 | 2012 | March |
+---------------+------+-------+
|          0.00 | 2012 | April |
+---------------+------+-------+
|          0.00 | 2012 | May   |
+---------------+------+-------+
|          0.00 | 2012 | June  |
+---------------+------+-------+
|        120.00 | 2012 | July  |
+---------------+------+-------+
|          0.00 | 2012 | August|
+---------------+------+-------+
|          0.00 | 2012 | Sept  |
+---------------+------+-------+
|          0.00 | 2012 | Oct   |
+---------------+------+-------+
|          0.00 | 2012 | Nov   |
+---------------+------+-------+
|          0.00 | 2012 | Dec   |
+---------------+------+-------+

有什么想法吗？

Answer 1

您走在正确的轨道上，只需LEFT JOIN 查询结果到月名表。然后，结果中存在的名称将返回一个匹配项，对于结果中不存在的那些月份，将返回一个 NULL。 COALESCE 或 NVL 或 CASE 构造，无论您喜欢什么，都可用于将 NULL 转换为直线0。

您不需要制作一个真正的月份表，您可以通过使用内联 View 来获取 - 只需一个生成所有月份数据的查询。

select     months.month, coalesce(appts.monthly_total, 0) monthly_total
from       (
                      select 'January' as month
           union all  select 'February' 
           union all  select 'March'

           ...etc...

           union all  select 'December'
           ) months
left join  (
            select     sum(service_price)        as monthly_total, 
                       monthname(appt_date_time) as month 
            from       appt_tbl 
            inner join services_tbl 
            on         appt_tbl.service_id = services_tbl.service_id
            where      appt_date_time between '2012-01-01 00:00:00'
                                          and '2012-12-31 23:59:59'         
            group by   month(appt_date_time)
           ) appts
on         months.month = appts.month 

至于返回特定年份的所有内容，请查看 WHERE 条件。我假设 appt_date_time 是日期时间或时间戳。您不想写 YEAR(appt_date_time) = 2012 因为这会破坏在该列上使用索引的机会(我假设该列显示为索引中的第一列)。通过使用 BETWEEN...AND 并与文字日期时间进行比较，您可以获得满足要求的非常高效的查询。

此外，如果您在 month(appt_date_time) 上GROUP BY，mysql 将确保结果也按月份升序排序。所以不需要单独的 ORDER BY。 UNION 查询的“分支”顺序也将由 mysql 保留。