美好的一天，
我正在尝试创建一条SQL语句以返回所有4位用户最近14天的状态数据。我已经创建了一个Calendar表用于执行LEFT JOIN。
给定BackupData表：
+----------+-------------+------------+-----------+-------------------
--+
| 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 |
没有数据的日子必须为用户显示为“无数据”。
我当前的查询是消除某些用户在特定日期没有状态数据的空值，因为其他用户可能在该日期存在数据：
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条目，因为它为空。
创建过程或联合声明的其他方法。什么是最优雅的解决方案？
谢谢。
最佳答案
首先获取日期：
select dt
from calendar_table
where dt between date_add(curdate(), interval -14 day) and curdate();
然后用户：
select distinct user from backupdetail;
如果您有一个users表（应有的话），则应从该表中选择users，而不要选择。
然后，让我们获取每天和用户的状态：
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），然后外部加入现有的backupdetail记录。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;