我有表格,其中列c_date
为datetime
,total
为mysql中的int
类型,我想打印出每天的销售,每月的总销售额,以及每年的总销售额,包括没有销售的日,月,年。
目前用于每日销售,我正在运行以下查询:
mysql> select date(c_date) as date, sum(total) as total_sale from sale group by date;
+------------+------------+
| date | total_sale |
+------------+------------+
| 2013-10-3 | 798 |
| 2013-10-6 | 114 |
+------------+------------+
但是,我想要这样的东西:
mysql> select date(c_date) as date, sum(total) as total_sale from sale group by date;
+------------+------------+
| date | total_sale |
+------------+------------+
| 2013-10-1 | 0 |
| 2013-10-2 | 0 |
| 2013-10-3 | 798 |
| 2013-10-4 | 0 |
| 2013-10-5 | 0 |
| 2013-10-6 | 114 |
+------------+------------+
对于每月,我得到这个:
mysql> select c_date, month(c_date) as month, year(c_date) as year, sum(total) as total from sale group by c_date order by c_date;
+---------------------+-------+------+-------+
| c_date | month | year | total |
+---------------------+-------+------+-------+
| 2013-10-3 02:40:06 | 10 | 2013 | 228 |
| 2013-10-3 02:41:58 | 10 | 2013 | 114 |
| 2013-10-3 02:44:36 | 10 | 2013 | 114 |
| 2013-10-3 02:46:40 | 10 | 2013 | 114 |
| 2013-10-3 02:49:15 | 10 | 2013 | 114 |
| 2013-10-3 02:53:36 | 10 | 2013 | 114 |
| 2013-10-6 07:43:27 | 10 | 2013 | 114 |
+---------------------+-------+------+-------+
但我想要这样的东西:
mysql> select c_date, month(c_date) as month, year(c_date) as year, sum(total) as total from sale group by c_date order by c_date;
+---------------------+-------+------+-------+
| c_date | month | year | total |
+---------------------+-------+------+-------+
| 2013-1-3 02:40:06 | 1 | 2013 | 0 |
| 2013-2-3 02:41:58 | 2 | 2013 | 0 |
| 2013-3-3 02:44:36 | 3 | 2013 | 0 |
| 2013-4-3 02:46:40 | 4 | 2013 | 0 |
| 2013-5-3 02:49:15 | 5 | 2013 | 0 |
| 2013-6-3 02:53:36 | 6 | 2013 | 0 |
| 2013-7-6 07:43:27 | 7 | 2013 | 0 |
| 2013-8-3 02:44:36 | 8 | 2013 | 0 |
| 2013-9-3 02:46:40 | 9 | 2013 | 0 |
| 2013-10-3 02:49:15 | 10 | 2013 | 912 |
| 2013-11-3 02:53:36 | 11 | 2013 | 0 |
| 2013-12-6 07:43:27 | 12 | 2013 | 0 |
+---------------------+-------+------+-------+
MysqL可以做到这一点吗?
由于不可能
在MySQL中使用序列(实际上,它们根本不存在),因此您必须先创建日期范围表。那就像:
CREATE TABLE dates_range (record_date DATE)
然后用日期填充此表,从日期中的最小日期开始,sale
表中存在,直到最大值。
在此之后,使用 SQL LEFT JOIN
运算符,您将能够像这样聚合数据:
SELECT
YEAR(dates_range.record_date),
MONTH(dates_range.record_date),
DAY(dates_range.record_date),
COALESCE(SUM(sale.total), 0) AS total_sum
FROM
dates_range
LEFT JOIN sale
ON dates_range.record_date=DATE(sale.c_date)
GROUP BY
YEAR(dates_range.record_date),
MONTH(dates_range.record_date),
DAY(dates_range.record_date)
在我看来
,您需要一个带有日历表的外部连接。
想象一个日历表,填充如下:
日历
Year Month Day
2013 201310 2013-10-1
2013 201310 2013-10-2
...
然后你可以写一个查询,比如
select date(c_day) as date,
sum(total) as total_sale
from calendar c
left outer join sale s
on c.day = s.c_date
where c.month = 201310
group by c_day
having c_day <= max(s.c_date); -- this is to avoid to show all
-- days for October