sql为每个月选择一条记录，该月记录的总和

Question

我有一个具有“约会表”和“服务表”的数据库。每个appt都有一个服务，每个服务都有一个价格。我想要的是一个查询，该查询将始终返回12行（每月一行），并包含几个月的总和（基于其服务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  |
+---------------+------+-------+

问题是，如果一个月没有任何订单，我不会在查询中返回该月。我希望该月有一个记录，只是它的“ 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

您走在正确的轨道上，只是LEFTJOIN对月份名称表的查询结果。然后，结果中存在的名称将返回一个匹配项，并且NULL将返回不在结果中的月份。无论您喜欢什么，aCOALESCE或NVLorCASE构造都可以将其NULL变成直线0。

您不需要制作真实的月度表，就可以使用内联视图获取-只需生成所有月度数据的查询即可。

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并与文字日期时间进行比较，您可以拥有一个满足要求的高效查询。

另外，如果您GROUP BY使用month(appt_date_time)mysql，将确保结果也会按月升序排列。因此无需单独使用ORDER BY。mysql也将保留UNION查询的“分支”顺序。