使用 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

Then use this table on the left of a 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屋！