sql 为每个月选择一条记录以及该月记录的总和 [英] sql to select one record for every month with a sum of that months records

问题描述

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

I have a database that has an "appointments table" and "services table". Each appt has a service and each service has a price. What I would like is one query, that will always return 12 rows (one row for each month) and contain a sum of the months appts (based on it's service id). So far I have:

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;

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

Currently, this returns something like:

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

问题是，如果一个月没有任何应用程序，我不会在查询中返回该月份.我希望那个月有一个记录，只要它的monthly_total"等于零.

The problem is that if a month doesn't have any appts, I do not get that month returned in the query. I would like that month to have a record, just have it's "monthly_total" equal zero.

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

Below is what I would like the query to return:

+---------------+------+-------+
| 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   |
+---------------+------+-------+

有什么想法吗?

推荐答案

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

You're on the right track, just LEFT JOIN the result of your query to a table of monthnames. Then the names that exist in your result will return a match, and a NULL will be returned for those months that are not in your result. A COALESCE or NVL or CASE construct, whatever you like, can be used to turn that NULL into a straight 0.

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

You don't need make a real table of monthts, you can get by using an inline view - simply a query that generates all the month data.

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 并与文字日期时间进行比较，您可以获得满足要求的非常有效的查询.

As for returning everyting for a particular year, look at the WHERE condition. I am asuming appt_date_time is a datetime or timestamp. You don't want to write YEAR(appt_date_time) = 2012 because that will ruin the chances of using an index on that column (I am assuming that column appears as a first column in an index). By using a BETWEEN...AND and comparing against literal datetimes, you can have a pretty efficient query that meets the requirement.

此外，如果您在 month(appt_date_time) 上 GROUP BY mysql 将确保结果也将按月升序排序.所以不需要单独的ORDER BY.UNION查询的'legs'的顺序也会被mysql保留.

Also, if you GROUP BY on month(appt_date_time) mysql will ensure results will by sorted ascending by month too. So no need for a separate ORDER BY. The order of the 'legs' of the UNION query will also be preserved by mysql.

这篇关于sql 为每个月选择一条记录以及该月记录的总和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！