Mysql 左连接没有任何数据的日子

Question

美好的一天，我正在尝试创建一个 SQL 语句来返回所有 4 个用户过去 14 天的状态数据。我创建了一个日历表来执行 LEFT JOIN。给定备份数据表:

+----------+-------------+------------+-----------+-------------------
--+
| customer | Category    | user       | status    | Date                
|
+----------+-------------+------------+-----------+---------------------+
| acme     | Workstation | Judi       | [Success] | 2017-10-18 10:29:08 |
| acme     | Server      | Quickbooks | [Success] | 2017-10-18 11:25:33 |
| acme     | Server      | Quickbooks | [Success] | 2017-10-18 11:33:56 |
| acme     | Workstation | Reception  | [Success] | 2017-10-18 12:14:49 |
| acme     | Workstation | Megan      | [Success] | 2017-10-18 16:10:24 |
| acme     | Workstation | Judi       | [Success] | 2017-10-18 16:11:03 |
| acme     | Workstation | Reception  | [Success] | 2017-10-19 08:04:15 |
| acme     | Server      | Quickbooks | [Success] | 2017-10-19 11:35:47 |
| acme     | Workstation | Judi       | [Success] | 2017-10-19 16:10:13 |
| acme     | Workstation | Reception  | [Success] | 2017-10-20 08:35:26 |
| acme     | Server      | Quickbooks | [Success] | 2017-10-20 13:28:08 |
| acme     | Workstation | Judi       | [Success] | 2017-10-20 16:09:00 |
| acme     | Server      | Quickbooks | [Success] | 2017-10-21 11:33:28 |
| acme     | Workstation | Judi       | [Success] | 2017-10-21 16:11:01 |
| acme     | Server      | Quickbooks | [Success] | 2017-10-22 11:33:16 |
+----------+-------------+------------+-----------+---------------------+

我想这样呈现数据:

+------------+------------+-----------+
| dt         | User       | Status    |
+------------+------------+-----------+
| 2017-10-18 | Judi       | [Success] |
| 2017-10-18 | Megan      | [Success] |
| 2017-10-18 | Quickbooks | [Success] |
| 2017-10-18 | Reception  | [Success] |
| 2017-10-19 | Judi       | [Success] |
| 2017-10-19 | Quickbooks | [Success] |
| 2017-10-19 | Reception  | [Success] |
| 2017-10-19 | Megan      | No Data   |
| 2017-10-20 | Judi       | [Success] |
| 2017-10-20 | Quickbooks | [Success] |
| 2017-10-20 | Reception  | [Success] |
| 2017-10-20 | Megan      | No Data   |
| 2017-10-21 | Judi       | [Success] |
| 2017-10-21 | Quickbooks | [Success] |
| 2017-10-21 | Megan      | No Data   |
| 2017-10-21 | Reception  | [Success] |
| 2017-10-22 | Judi       | [Success] |
| 2017-10-22 | Quickbooks | [Success] |
| 2017-10-22 | Megan      | No Data   |
| 2017-10-22 | Reception  | No Data   |
| 2017-10-23 | Judi       | [Success] |
| 2017-10-23 | Reception  | [Success] |
| 2017-10-23 | Quickbooks | No Data   |
| 2017-10-23 | Megan      | No Data   |

没有数据的日期必须对用户显示为“NO DATA”。我当前的查询正在消除空值，其中某些用户没有特定日期的状态数据，因为其他用户可能有该天的数据:

SELECT calendar_table.dt,BackupDetail.User, BackupDetail.Status
FROM calendar_table
LEFT JOIN BackupDetail ON (calendar_table.dt = 
DATE(BackupDetail.Date))
where calendar_table.dt BETWEEN date_add(curdate(), interval -14 day) 
AND curdate()
GROUP BY calendar_table.dt,BackupDetail.User

结果:

+------------+------------+-----------+
| dt         | User       | Status    |
+------------+------------+-----------+
| 2017-10-18 | Judi       | [Success] |
| 2017-10-18 | Megan      | [Success] |
| 2017-10-18 | Quickbooks | [Success] |
| 2017-10-18 | Reception  | [Success] |
| 2017-10-19 | Judi       | [Success] |
| 2017-10-19 | Quickbooks | [Success] |
| 2017-10-19 | Reception  | [Success] |
| 2017-10-20 | Judi       | [Success] |
| 2017-10-20 | Quickbooks | [Success] |
| 2017-10-20 | Reception  | [Success] |
| 2017-10-21 | Judi       | [Success] |
| 2017-10-21 | Quickbooks | [Success] |
| 2017-10-22 | Judi       | [Success] |
| 2017-10-22 | Quickbooks | [Success] |
| 2017-10-23 | Judi       | [Success] |
| 2017-10-23 | Reception  | [Success] |
| 2017-10-24 | Judi       | [Success] |
| 2017-10-24 | Megan      | [Failed]  |
| 2017-10-24 | Quickbooks | [Success] |
| 2017-10-25 | Judi       | [Success] |
| 2017-10-25 | Megan      | [Success] |
| 2017-10-25 | Quickbooks | [Success] |
| 2017-10-25 | Reception  | [Success] |
| 2017-10-26 | Judi       | [Success] |
| 2017-10-26 | Megan      | [Success] |
| 2017-10-26 | Quickbooks | [Success] |
| 2017-10-26 | Reception  | [Success] |
| 2017-10-27 | Judi       | [Success] |
| 2017-10-27 | Quickbooks | [Success] |
| 2017-10-27 | Reception  | [Success] |
| 2017-10-28 | Judi       | [Success] |
| 2017-10-28 | Quickbooks | [Success] |
| 2017-10-29 | Judi       | [Success] |
| 2017-10-29 | Quickbooks | [Success] |
| 2017-10-30 | Judi       | [Success] |
| 2017-10-30 | Megan      | [Success] |
| 2017-10-30 | Quickbooks | [Success] |
| 2017-10-30 | Reception  | [Success] |
| 2017-10-31 | Megan      | [Success] |
| 2017-10-31 | Reception  | [Success] |
| 2017-11-01 | NULL       | NULL      |
+------------+------------+-----------+

如果我更改查询以查询 JOIN 中的特定用户:

SELECT calendar_table.dt,BackupDetail.User, BackupDetail.Status
FROM calendar_table 
LEFT JOIN BackupDetail ON (calendar_table.dt = 
DATE(BackupDetail.Date) and BackupDetail.User = 'Quickbooks')

where calendar_table.dt BETWEEN date_add(curdate(), interval -14 day) 
AND curdate()

GROUP BY calendar_table.dt,BackupDetail.User

我更接近期望的结果:

+------------+------------+-----------+
| dt         | User       | Status    |
+------------+------------+-----------+
| 2017-10-18 | Quickbooks | [Success] |
| 2017-10-19 | Quickbooks | [Success] |
| 2017-10-20 | Quickbooks | [Success] |
| 2017-10-21 | Quickbooks | [Success] |
| 2017-10-22 | Quickbooks | [Success] |
| 2017-10-23 | NULL       | NULL      |
| 2017-10-24 | Quickbooks | [Success] |
| 2017-10-25 | Quickbooks | [Success] |
| 2017-10-26 | Quickbooks | [Success] |
| 2017-10-27 | Quickbooks | [Success] |
| 2017-10-28 | Quickbooks | [Success] |
| 2017-10-29 | Quickbooks | [Success] |
| 2017-10-30 | Quickbooks | [Success] |
| 2017-10-31 | NULL       | NULL      |
| 2017-11-01 | NULL       | NULL      |
+------------+------------+-----------+

如果将结果与第一个表进行比较，您可以看到第 23 个和第 31 个条目已从结果中删除，因为它为空。

创建过程或联合语句的其他内容。最优雅的解决方案是什么？

谢谢。

Answer 1

让我们先获取日期:

select dt 
from calendar_table 
where dt between date_add(curdate(), interval -14 day) and curdate();

然后用户:

select distinct user from backupdetail;

如果您有一个用户表(您应该有)，您可以从该表中选择用户，而不是当然。

然后让我们获取每天和用户的状态:

select user, date, group_concat(distinct status order by status) as statuses
from backupdetail
group by user, date;

如果您只对 status = 'Success' 感兴趣，您当然可以将其设为:

select distinct user, date, status
from backupdetail
where status = 'Success';

现在加入:首先创建日期和用户的所有组合 (CROSS JOIN)，然后外部联接现有的备份详细记录。

SELECT
 c.dt,
 u.user,
 COALESCE(b.status, 'NO DATA') as status
FROM
(
  select dt 
  from calendar_table 
  where dt between date_add(curdate(), interval -14 day) and curdate()
) c
CROSS JOIN (select distinct user from backupdetail) u
LEFT JOIN
(
  select distinct user, date, status
  from backupdetail
  where status = 'Success'
) b ON b.user = u.user AND date(b.date) = c.dt
ORDER BY c.dt, u.user;