如何用时间段分组天来知道我是否有一周的SQL

Question

我有一个SQL表，它包含以下列的每周插槽：

| [id] | [dayOfWeek] | [startTime] | [endTime]

此表对应商店开门时的时间段：例如：(1，2，14:00:00，16:00:00(===商店星期二(一周中的第二天(营业时间为14小时至16小时

如果我一周中的每一天都有相同的时间段(例如：14h=>16h(，我怎么能用sql函数知道呢？

编辑

这是我的数据示例：

| id | dayOfWeek | startTime | endTime  |
|====|===========|===========|==========|
| 1  |     1     | 07:00:00  | 08:00:00 |
| 2  |     1     | 09:00:00  | 10:00:00 |
| 3  |     0     | 14:00:00  | 18:00:00 |
| 4  |     1     | 14:00:00  | 18:00:00 |
| 5  |     2     | 14:00:00  | 18:00:00 |
| 6  |     3     | 14:00:00  | 18:00:00 |
| 7  |     4     | 14:00:00  | 18:00:00 |
| 8  |     5     | 14:00:00  | 18:00:00 |
| 9  |     6     | 14:00:00  | 18:00:00 |
| 10 |     3     | 16:00:00  | 19:00:00 |
| 11 |     5     | 13:00:00  | 23:00:00 |

我希望我的请求返回：

| dayOfWeek | startTime | endTime  |
|===========|===========|==========|
|     1     | 07:00:00  | 08:00:00 |
|     1     | 09:00:00  | 10:00:00 |
|           | 14:00:00  | 18:00:00 | --> my all week (id 3 --> 9)
|     3     | 16:00:00  | 19:00:00 |
|     5     | 13:00:00  | 23:00:00 |

Answer 1

这就是您想要的吗？

IF OBJECT_ID('tempdb..#TestData', 'U') IS NOT NULL 
BEGIN DROP TABLE #TestData; END;
CREATE TABLE #TestData (
id INT NOT NULL,
[dayOfWeek] TINYINT NOT NULL,
startTime TIME(0) NOT NULL,
endTime TIME(0) NOT NULL 
);
INSERT #TestData(id, dayOfWeek, startTime, endTime) VALUES
(1 , 1, '07:00:00', '08:00:00'),
(2 , 1, '09:00:00', '10:00:00'),
(3 , 0, '14:00:00', '18:00:00'),
(4 , 1, '14:00:00', '18:00:00'),
(5 , 2, '14:00:00', '18:00:00'),
(6 , 3, '14:00:00', '18:00:00'),
(7 , 4, '14:00:00', '18:00:00'),
(8 , 5, '14:00:00', '18:00:00'),
(9 , 6, '14:00:00', '18:00:00'),
(10, 3, '16:00:00', '19:00:00'),
(11, 5, '13:00:00', '23:00:00');
--=====================================
SELECT 
td1.startTime,
td1.endTime,
dw.daysOfWeek
FROM (
SELECT DISTINCT
td.startTime,
td.endTime          
FROM
#TestData td
) td1
CROSS APPLY (
SELECT 
STUFF((
SELECT 
CONCAT(', ', td2.dayOfWeek)
FROM
#TestData td2
WHERE
td1.startTime = td2.startTime
AND td1.endTime = td2.endTime
FOR XML PATH('')
), 1, 2, '')
) dw (daysOfWeek);

结果：

startTime        endTime          daysOfWeek
---------------- ---------------- -------------------------
07:00:00         08:00:00         1
09:00:00         10:00:00         1
13:00:00         23:00:00         5
14:00:00         18:00:00         0, 1, 2, 3, 4, 5, 6
16:00:00         19:00:00         3

Answer 2

以下返回一周中每天14:00-16:00打开的id：

select id
from t
where startTime <= '14:00:00' and endTime >= '16:00:00'
group by id
having count(*) = 7