当数据库包含多个记录时，MS SQL Server子查询错误

Question

我创建了两个表，一个是"时钟"；另一个是"；预约"；桌子时间时钟表包含例如

1. 12:00
2. 12:05
3. 12:10
4. 12:15
5. 12:20
6. 12:25
7. 12:30
8. 12:35
9. 12:40等

预约表当预约表包含记录(预约(时

1. 日期：2020-09-26从12:10到12:15
2. 日期：2020-09-26 12:20至12:30

然后TimeClock表的查询结果如下，

1. 12:00
2. 12:05
3. 12:35
4. 12:40等

当我运行下面的查询时，当数据库包含1条以上的记录时，我会收到一个错误，但当数据库包含一条记录时，它会正常工作。解决方案是什么？

SELECT Time24
FROM GlobalSetup.TimeClock
WHERE
Time24 not between
(SELECT start_time FROM Customer.Appointment WHERE start_date = '2020-09-26' and EmployeeRecID = 1)
and
(SELECT end_time FROM Customer.Appointment WHERE start_date = '2020-09-26' and EmployeeRecID = 1)
and Every5Mins = 1
Order By Time24

Answer 1

您似乎想要不在约会之间的时间。GSerg提出的CCD_ 1是合适的。我不知道为什么那个答案被删除了。

我会这样写：

SELECT tc.Time24
FROM GlobalSetup.TimeClock tc
WHERE NOT EXISTS (SELECT 1
FROM Customer.Appointment a
WHERE a.EmployeeRecID = 1 AND
tc.Time24 >= a.start_time AND
tc.Time24 <= a.end_time
) AND
tc.Every5Mins = 1 AND
tc.Time24 >= '2020-09-26' AND
tc.Time24 < '2020-09-27'
ORDER BY Time24;

以上假设开始和结束时间包括日期。如果这不是真的，那么需要一个额外的比较：

SELECT tc.Time24
FROM GlobalSetup.TimeClock tc
WHERE NOT EXISTS (SELECT 1
FROM Customer.Appointment a
WHERE a.EmployeeRecID = 1 AND
a.start_date = '2020-09-26' AND
tc.Time24 >= a.start_time AND
tc.Time24 <= a.end_time
) AND
tc.Every5Mins = 1 AND
tc.Time24 >= '2020-09-26' AND
tc.Time24 < '2020-09-27'
ORDER BY Time24;