我需要一些帮助来编写 mysql 查询。我需要为特定的业务 ID/项目 ID 查找给定日期范围内所有错过的报告。

基本上，对于给定的业务 ID，我需要知道项目的名称以及报告缺失或未标记为已完成的所有日期。

我正在使用日历表技巧(如 here 和 here 所述)来查找丢失的报告日期，但我在加入项目表以查找报告丢失的相关项目/业务时遇到问题.

我基本上需要一个结果集来给我类似这样的数据:

+------------+-----------+--------------+
| project_id | name      | missing_date |
+------------+-----------+--------------+
| 1          | Project 1 | 2014-01-01   |
| 1          | Project 1 | 2014-01-03   |
| 1          | Project 1 | 2014-01-04   |
| 1          | Project 1 | 2014-01-07   |
| 1          | Project 1 | 2014-01-09   |
| 2          | Project 2 | 2014-01-02   |
| 2          | Project 2 | 2014-01-03   |
| 2          | Project 2 | 2014-01-04   |
+------------+-----------+--------------+

这是我的架构:

projects table:
+----------------+------------------+------+-----+-------------------+----------------+
| Field          | Type             | Null | Key | Default           | Extra          |
+----------------+------------------+------+-----+-------------------+----------------+
| project_id     | int(10) unsigned | NO   | PRI | NULL              | auto_increment |
| business_id    | int(10) unsigned | NO   | MUL | NULL              |                |
| name           | tinytext         | YES  |     | NULL              |                |
+----------------+------------------+------+-----+-------------------+----------------+

reports table:
+---------------------+------------------+------+-----+-------------------+----------------+
| Field               | Type             | Null | Key | Default           | Extra          |
+---------------------+------------------+------+-----+-------------------+----------------+
| report_id           | int(10) unsigned | NO   | PRI | NULL              | auto_increment |
| project_id          | int(10) unsigned | NO   | MUL | NULL              |                |
| report_date         | date             | NO   | MUL | NULL              |                |
| completed           | bit(1)           | NO   |     | b'0'              |                |
+---------------------+------------------+------+-----+-------------------+----------------+


calendar table:
+--------------+-------------+------+-----+---------+-------+
| Field        | Type        | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| dt           | date        | NO   | PRI | NULL    |       |
| month_name   | varchar(9)  | YES  |     | NULL    |       |
| day_name     | varchar(9)  | YES  |     | NULL    |       |
| y            | smallint(6) | YES  |     | NULL    |       |
| q            | tinyint(4)  | YES  |     | NULL    |       |
| m            | tinyint(4)  | YES  |     | NULL    |       |
| d            | tinyint(4)  | YES  |     | NULL    |       |
| dw           | tinyint(4)  | YES  |     | NULL    |       |
| w            | tinyint(4)  | YES  |     | NULL    |       |
| is_weekday   | bit(1)      | YES  |     | NULL    |       |
| is_holiday   | bit(1)      | YES  |     | NULL    |       |
| holiday_desc | varchar(32) | YES  |     | NULL    |       |
+--------------+-------------+------+-----+---------+-------+

下面的查询可以返回未完成报告的列表，但我仍然需要用根本没有报告记录的日期来填补空白。

select 
    p.project_id,
    p.name,
    c.dt as missing_date,
    r.completed
from reports r
join projects p on (r.project_id = p.project_id)
right join calendar c on (c.dt = r.report_date)
where c.dt >= '2014-02-01'
and c.dt <= '2014-02-10'
-- and r.report_date is null /** THE RESULT SET IS EMPTY IF I UNCOMMENT THIS **/
and r.completed = false
and c.is_holiday = false
and c.is_weekday = true
and p.business_id = 1001
order by p.project_id, r.report_date, c.dt;

如有任何帮助，我们将不胜感激!

最佳答案

我阅读了您提供的链接，这种日期存储方法很棒。 您在查询中使用的任何方式:

right join calendar c on (c.dt = r.report_date)

但我在您的报告表中没有看到任何 report_date。 如果问题是因为它，我建议你检查它，因为除此之外你的查询似乎工作正常。

关于Mysql - 使用多个连接表查找缺失日期，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/22078220/