我在使用MySQL查询时遇到问题。
以下是我的三个表格:
员工:
id initials deleted
------ -------- ---------
1 TV 0
2 AH 0
3 JE 0
4 MA 0
5 MJ 0
6 CE 0
7 KB 1
8 KL 1
附表:
id place start end deleted
------ -------------- ------------------- ------------------- ---------
1 Somewhere 2018-05-11 16:48:17 2018-05-15 16:48:26 0
2 Somewhere else 2018-05-12 16:48:50 2018-05-14 16:48:55 1
3 Here 2018-05-13 00:00:00 2018-05-13 00:00:00 0
4 Not here 2018-05-18 16:49:42 2018-05-16 16:49:48 0
schedule_link :
id id_employee id_schedule
------ ----------- -------------
1 1 1
2 1 2
3 4 3
4 5 4
我想要一个请求,该请求返回每个员工的日期之前与所有有关的内容。即使员工没有找到任何记录,我也希望查询在其他列中返回带有 NULL 的首字母缩写。
这是我当前的查询:
SELECT
`employees`.`id`,
`employees`.`initials`,
`schedule`.`place`,
`schedule`.`start`,
`schedule`.`end`,
`schedule`.`deleted`
FROM
`employees`
LEFT JOIN `schedule_link`
ON (
`employees`.`id` = `schedule_link`.`id_employee`
)
LEFT JOIN `schedule`
ON (
`schedule_link`.`id_schedule` = `schedule`.`id`
)
WHERE (
`employees`.`deleted` = '0'
AND `schedule`.`deleted` = '0'
)
AND (
DATE(CURDATE()) BETWEEN CAST(schedule.start AS DATE)
AND CAST(schedule.end AS DATE)
)
这向我返回以下数据:
id initials place start end deleted
------ -------- --------- ------------------- ------------------- ---------
1 TV Somewhere 2018-05-11 16:48:17 2018-05-15 16:48:26 0
4 MA Here 2018-05-13 00:00:00 2018-05-13 00:00:00 0
这是正确的,但我想要的是以下结果:
id initials place START END deleted
------ -------- --------- ------------------- ------------------- ---------
1 TV Somewhere 2018-05-11 16:48:17 2018-05-15 16:48:26 0
4 MA Here 2018-05-13 00:00:00 2018-05-13 00:00:00 0
2 AH NULL NULL NULL 0
3 JE NULL NULL NULL 0
5 MJ NULL NULL NULL 0
6 CE NULL NULL NULL 0
是否可以通过单个请求获得此结果?
提前感谢您的帮助。
您需要将条件放在on
子句中除第一个表之外的所有表上。where
子句正在将外部连接转换为内部连接。
我还有其他一些建议:
SELECT e.id, e.initials, s.place, s.start, s.end, s.deleted
FROM employees e LEFT JOIN
schedule_link sl
ON e.id = sl.id_employee LEFT JOIN
schedule s
ON sl.id_schedule = s.id AND s.deleted = 0 AND
CURDATE() BETWEEN CAST(s.start AS DATE) AND CAST(s.end AS DATE)
WHERE e.deleted = 0;
笔记:
- 表别名使查询更易于编写和读取。
- 反引号只会使查询更难读取和写入。
- 不要使用
start
和end
作为列名(即,如果可以,请重命名它们(。 它们是关键字(尽管不是保留的(,因此它们在 SQL 语句中具有其他用途。 - 我猜
deleted
是数字。 不要使用单引号进行比较(除非列确实是字符串(。 CURDATE()
已经是一个日期了。 无需转换。- 我不建议将
BETWEEN
与日期一起使用,因为可能存在延迟的时间部分。 但是,您使用的是显式转换,因此代码可以明确地执行您想要的操作(可能面临不使用可用索引的风险(。
编辑:
明白了。 由于日期条件位于第三个表中,而不是第二个表中,因此您得到的是重复的行。 我认为这将解决您的问题:
SELECT e.id, e.initials, ss.place, ss.start, ss.end, ss.deleted
FROM employees e LEFT JOIN
(SELECT sl.id_employee, s.*
FROM schedule_link sl JOIN
schedule s
ON sl.id_schedule = s.id AND s.deleted = 0
WHERE CURDATE() BETWEEN CAST(s.start AS DATE) AND CAST(s.end AS DATE)
) ss
ON e.id = ss.id_employee
WHERE e.deleted = 0;
这将包括每个在时间范围内不匹配的员工一次。 如果有多个匹配项,您仍将从schedule
获得每条记录。
您实际上可以在没有子查询的情况下表达这一点:
SELECT e.id, e.initials, s.place, s.start, s.end, s.deleted
FROM employees e LEFT JOIN
(schedule_link sl JOIN
schedule s
ON sl.id_schedule = s.id AND s.deleted = 0 AND
CURDATE() BETWEEN CAST(s.start AS DATE) AND CAST(s.end AS DATE)
)
ON e.id = sl.id_employee
WHERE e.deleted = 0;
我发现子查询版本更容易遵循。
我的一般看法(没有测试查询,因为我不在手机 ATM 上发帖(:
-
应在查询中的第二次联接上使用
RIGHT JOIN
。 -
您还需要额外的 WHERE 条款,即
where employees.id is not null
-
不要忘记将
ifull()
用于时间表中的所有字段,即ifnull(schedule_link.id, schedule_link.id, schedule.myfield)
请注意,应该对要显示的所有字段执行此操作,这些字段来自此查询中的schedule
表
希望这些指南对您有所帮助。