使用MySQL选择不存在的数据 [英] Selecting Non-existent Data With MySQL
问题描述
我正在尝试选择两个日期范围之间的数据.但是,并非每天都插入所有数据.下面是该表的示例:
I'm trying to select data between two date range. However not all data are being inserted daily. Below is sample of the table:
mysql> SELECT * FROM attendance;
+------------+-------+
| date | total |
+------------+-------+
| 2012-07-02 | 100 |
| 2012-07-04 | 70 |
| 2012-07-05 | 78 |
+------------+-------+
3 rows in set (0.00 sec)
这种情况是,我希望从2012-07-02到2012-07-04总共能出席.根据上面的数据,我会得到
The scenario is I want to get total of attendance from 2012-07-02 till 2012-07-04. Based on the data above I will get
mysql> SELECT * FROM attendance WHERE date BETWEEN '2012-07-02' AND '2012-07-04';
+------------+-------+
| date | total |
+------------+-------+
| 2012-07-02 | 100 |
| 2012-07-04 | 70 |
+------------+-------+
2 rows in set (0.00 sec)
但是我的目标是将2012-07-03包含在结果中.
However my objective is to have 2012-07-03 included in the result.
+------------+-------+
| date | total |
+------------+-------+
| 2012-07-02 | 100 |
| 2012-07-03 | 0 |
| 2012-07-04 | 70 |
+------------+-------+
这可以通过MySQL完成吗?我确实查看了临时表.但仍然无法实现目标.
Is this possible to be done through MySQL? I did look into temporary table. But still unable to achieve the objective.
推荐答案
这是使用简单解决方案的常见问题.
This is a common problem with a simple solution.
创建一个常规表(例如REF_DATE),并将所有日期存储在其中,例如3年或您需要的任何时间跨度.
Create a regular table, say REF_DATE, and store in it all dates for like 3 years or whatever time span you would need.
然后在LEFT OUTER JOIN
SELECT REF.date,IFNULL(A.total,0) as total FROM REF_DATE REF
LEFT OUTER JOIN attendance
ON REF.date=A.date
A WHERE REF.date BETWEEN '2012-07-02' AND '2012-07-04';
DATE是MySQL中的关键字,出于可读性考虑,我在这里使用了它.使用其他列名称.
DATE is a keyword in MySQL, I have used it here for readability. Use a different column name.
这篇关于使用MySQL选择不存在的数据的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!