問(wèn)題描述
我正在嘗試將具有開(kāi)始時(shí)間、結(jié)束時(shí)間、頻率和持續(xù)時(shí)間的時(shí)間頻率拆分為單獨(dú)的行.以下是一些示例數(shù)據(jù):
I am trying to split a time frequency that has a start time, an end time, a frequency and a duration into separate rows. Here is some example data:
+------+------------+----------+-----------------+---------------+
| Name | Start_Time | End_Time | Frequency_Hours | Duration_Mins |
+------+------------+----------+-----------------+---------------+
| A | 08:00:00 | 18:00:00 | 2 | 2 |
| B | 00:00:00 | 23:59:59 | 1 | 5 |
| C | 00:00:00 | 23:59:59 | 4 | 15 |
+------+------------+----------+-----------------+---------------+
可以使用以下查詢創(chuàng)建:
Can be created using the following query:
DECLARE @Tmp AS TABLE(Name VARCHAR(128)
,Start_Time VARCHAR(8)
,End_Time VARCHAR(8)
,Frequency_Hours INT
,Duration_Mins INT)
INSERT INTO @Tmp VALUES ('A','08:00:00', '18:00:00', 2,2)
,('B','00:00:00', '23:59:59', 1,5)
,('C','00:00:00', '23:59:59', 4,15)
這是我想要的輸出(然后我將使用它來(lái)驅(qū)動(dòng)甘特圖可視化):
Here is my desired output (I will then use this to drive a gantt chart visualisation):
+------+------------+----------+
| Name | Start_Time | End_Time |
+------+------------+----------+
| A | 08:00:00 | 08:02:00 |
| A | 10:00:00 | 10:02:00 |
| A | 12:00:00 | 12:02:00 |
| A | 14:00:00 | 14:02:00 |
| A | 16:00:00 | 16:02:00 |
| A | 18:00:00 | 18:02:00 |
| B | 00:00:00 | 00:05:00 |
| B | 01:00:00 | 01:05:00 |
| B | 02:00:00 | 02:05:00 |
| B | 03:00:00 | 03:05:00 |
| B | 04:00:00 | 04:05:00 |
| B | 05:00:00 | 05:05:00 |
| B | 06:00:00 | 06:05:00 |
| B | 07:00:00 | 07:05:00 |
| B | 08:00:00 | 08:05:00 |
| B | 09:00:00 | 09:05:00 |
| B | 10:00:00 | 10:05:00 |
| B | 11:00:00 | 11:05:00 |
| B | 12:00:00 | 12:05:00 |
| B | 13:00:00 | 13:05:00 |
| B | 14:00:00 | 14:05:00 |
| B | 15:00:00 | 15:05:00 |
| B | 16:00:00 | 16:05:00 |
| B | 17:00:00 | 17:05:00 |
| B | 18:00:00 | 18:05:00 |
| B | 19:00:00 | 19:05:00 |
| B | 20:00:00 | 20:05:00 |
| B | 21:00:00 | 21:05:00 |
| B | 22:00:00 | 22:05:00 |
| B | 23:00:00 | 23:05:00 |
| C | 00:00:00 | 00:15:00 |
| C | 04:00:00 | 04:15:00 |
| C | 08:00:00 | 08:15:00 |
| C | 12:00:00 | 12:15:00 |
| C | 16:00:00 | 16:15:00 |
| C | 20:00:00 | 20:15:00 |
+------+------------+----------+
我希望能夠從中創(chuàng)建一個(gè)視圖，因此我嘗試在沒(méi)有游標(biāo)或其他 CPU 密集型方法的情況下進(jìn)行.
I am hoping to be able to create a view out of this so I am trying to do it without cursors or other cpu intensive methods.
有什么想法嗎?
謝謝，
丹.
推薦答案
它也可以在沒(méi)有 RECURSIIVE CTE 的情況下完成.
It can be done without RECURSIIVE CTE also.
如果我們創(chuàng)建數(shù)字而不是使用選擇不同的數(shù)字 master..spt_values 那么性能會(huì)好很多.
If we create number instead of using
select distinct number master..spt_values then performance will be far better.
Like Number 表可以填充從 1 到 100.
Like Number table can be populated from 1 to 100.
用各種樣本數(shù)據(jù)試試這個(gè)，
try this with various sample data,
declare @t table(Name varchar(20), Start_Time time(0),End_Time time(0)
, Frequency_Hours int,Duration_Mins int)
insert into @t VALUES
('A','08:00:00','18:00:00', 2 , 2 )
,('B','00:00:00','23:59:59', 1 , 5 )
,('C','00:00:00','23:59:59', 4 ,15 )
SELECT NAME
,dateadd(hour, n, Start_Time) Start_Time
,dateadd(minute, Duration_Mins, (dateadd(hour, n, Start_Time))) End_Time
FROM @t t
CROSS APPLY (
SELECT DISTINCT number * Frequency_Hours n
FROM master..spt_values
WHERE number >= 0
AND number <= datediff(HOUR, t.Start_Time, t.End_Time) / Frequency_Hours
) ca
這篇關(guān)于將時(shí)間頻率拆分為行的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！