sql TABLE DESIGN是
CREATE TABLE [dbo].[EVENTS] ([ID] [int] IDENTITY(1,1) NOT NULL,[Name] [nvarchar](255) NOT NULL)
和
CREATE TABLE [dbo].[EVENTS_Meta]( [ID] [int] IDENTITY(1,[event_id] [int] NOT NULL,[Meta_key] [varchar](255) NOT NULL,[Meta_value] [bigint] NOT NULL)
事件数据是
事件元数据是
我跟着Repeating calendar events and some final maths,我写了下面的查询
列出截止日期前的所有事件
SELECT EV.* FROM events AS EV RIGHT JOIN events_Meta AS EM1 ON EM1.event_id = EV.id RIGHT JOIN events_Meta AS EM2 ON EM2.Meta_key = 'repeat_interval_'+ CAST(EM1.id as Varchar(100)) WHERE EM1.Meta_key = 'repeat_start' AND ((1391040000 - EM1.Meta_value ) % EM2.Meta_value) = 0
我没有什么东西我想以给定的间隔显示repeat_start之后的所有日期.
示例这里第一个活动开始于(2014年1月3日,上午10点)unixtimestamp = 1388743200,并在每个星期五(7天)继续,我们还计划在星期六(2014年1月1日)1388858400开始的第一个活动,并每7天继续一次(星期六) )
它可以是一个月/每日/等等.所以我们将间隔定义为秒.
如果我给出一些输入,如2014年1月30日,即= 1391040000(2014年1月30日00:00:00)
预期结果
Billa访问,2014年1月3日10 A.M
Billa访问,2014年1月4日10 A.M
Billa Visit,10 Jan 2014 10 A.M
Billa访问,2014年1月11日10 A.M
Billa访问,2014年1月17日10 A.M
Billa访问,2014年1月18日10 A.M
Billa访问,2014年1月24日10 A.M
Billa访问,2014年1月25日10 A.M
解决方法
SELECT EventID = e.ID,e.Name,StartDateTime = DATEADD(SECOND,rs.Meta_Value,'19700101'),RepeatInterval = ri.Meta_Value FROM dbo.Events e INNER JOIN dbo.Events_Meta rs ON rs.Event_ID = e.ID AND rs.Meta_Key = 'repeat_start' INNER JOIN dbo.Events_Meta ri ON ri.Event_ID = e.ID AND ri.Meta_Key = 'repeat_interval_' + CAST(e.ID AS VARCHAR(10));
这给出：
EventID | Name | StartDateTime | RepeatInterval --------+--------------+---------------------+----------------- 1 | Billa Vist | 2014-01-03 10:00:00 | 604800 1 | Billa Vist | 2014-01-04 18:00:00 | 604800
为了得到这个重复,你将需要一个数字表交叉加入,如果你没有一个有很多方法来生成一个在运行,为了简单的原因,我将使用：
WITH Numbers AS
( SELECT Number = ROW_NUMBER() OVER(ORDER BY a.object_id) - 1
FROM sys.all_objects a
)
SELECT Number
FROM Numbers;
为进一步阅读,Aaron Bertrand已经做了一些深入比较的方法来生成序列号：
> Generate a set or sequence without loops – part
1
> Generate a set or sequence without loops – part
2
> Generate a set or sequence without loops – part
3
如果我们将数字表限制在0-5,而只看第一个事件,交叉加入两个将给出：
EventID | Name | StartDateTime | RepeatInterval | Number --------+--------------+---------------------+----------------+--------- 1 | Billa Vist | 2014-01-03 10:00:00 | 604800 | 0 1 | Billa Vist | 2014-01-03 10:00:00 | 604800 | 1 1 | Billa Vist | 2014-01-03 10:00:00 | 604800 | 2 1 | Billa Vist | 2014-01-03 10:00:00 | 604800 | 3 1 | Billa Vist | 2014-01-03 10:00:00 | 604800 | 4 1 | Billa Vist | 2014-01-03 10:00:00 | 604800 | 5
然后,您可以通过在事件开始时间添加RepeatInterval * Number来获得您的出现：
DECLARE @EndDate DATETIME = '20140130'; WITH EventData AS ( SELECT EventID = e.ID,RepeatInterval = ri.Meta_Value FROM dbo.Events e INNER JOIN dbo.Events_Meta rs ON rs.Event_ID = e.ID AND rs.Meta_Key = 'repeat_start' INNER JOIN dbo.Events_Meta ri ON ri.Event_ID = e.ID AND ri.Meta_Key = 'repeat_interval_' + CAST(rs.ID AS VARCHAR(10)) ),Numbers AS ( SELECT Number = ROW_NUMBER() OVER(ORDER BY a.object_id) - 1 FROM sys.all_objects a ) SELECT e.EventID,EventDate = DATEADD(SECOND,n.Number * e.RepeatInterval,e.StartDateTime) FROM EventData e CROSS JOIN Numbers n WHERE DATEADD(SECOND,e.StartDateTime) < @EndDate ORDER BY e.EventID,EventDate;
这将给出您的预期输出：
EVENTID | NAME | EVENTDATE --------+---------------+-------------------------------- 1 | Billa Vist | January,03 2014 10:00:00+0000 1 | Billa Vist | January,04 2014 18:00:00+0000 1 | Billa Vist | January,10 2014 10:00:00+0000 1 | Billa Vist | January,11 2014 18:00:00+0000 1 | Billa Vist | January,17 2014 10:00:00+0000 1 | Billa Vist | January,18 2014 18:00:00+0000 1 | Billa Vist | January,24 2014 10:00:00+0000 1 | Billa Vist | January,25 2014 18:00:00+0000
我认为你的模式是有问题的,加入：
Meta_Key = 'repeat_interval_' + CAST(rs.ID AS VARCHAR(10))
是最好的脆弱.我认为你将开始日期和重复间隔存储在一起会更好：
CREATE TABLE dbo.Events_Meta ( ID INT IDENTITY(1,Event_ID INT NOT NULL,StartDateTime DATETIME2 NOT NULL,IntervalRepeat INT NULL,-- NULLABLE FOR SINGLE EVENTS RepeatEndDate DATETIME2 NULL,-- NULLABLE FOR EVENTS THAT NEVER END CONSTRAINT PK_Events_Meta__ID PRIMARY KEY (ID),CONSTRAINT FK_Events_Meta__Event_ID FOREIGN KEY (Event_ID) REFERENCES dbo.Events (ID) );
这将简化您的数据：
EventID | StartDateTime | RepeatInterval | RepeatEndDate --------+---------------------+----------------+--------------- 1 | 2014-01-03 10:00:00 | 604800 | NULL 1 | 2014-01-04 18:00:00 | 604800 | NULL
它还允许您为您的重复添加结束日期,即如果您只想重复一个星期.这样您的查询就可以模拟为：
DECLARE @EndDate DATETIME = '20140130';
WITH Numbers AS
( SELECT Number = ROW_NUMBER() OVER(ORDER BY a.object_id) - 1
FROM sys.all_objects a
)
SELECT e.ID,n.Number * em.IntervalRepeat,em.StartDateTime)
FROM Events e
INNER JOIN Events_Meta em
ON em.Event_ID = e.ID
CROSS JOIN Numbers n
WHERE DATEADD(SECOND,em.StartDateTime) <= @EndDate
AND ( DATEADD(SECOND,em.StartDateTime) <= em.RepeatEndDate
OR em.RepeatEndDate IS NULL
)
ORDER BY EventDate;
过去我不会给我完整的模式,但是我会给出一个很好的例子,你可以希望建立自己的.我将只会在周一至周五每周发生的事件中增加一个例子：
在上述ER RepeatEvent中,存储重复事件的基本信息,然后根据重复类型(每日,每周,每月),填充一个或多个其他表.在每周活动的例子中,它将存储在RepeatDay表中重复的一周中的所有天.如果这只需要限制在某些月份,您可以将这些月份存储在RepeatMonth中,依此类推.
然后使用日历表,您可以在第一个日期之后获得所有可能的日期,并将其限制为与一年中的某个日期匹配的日期等等：
WITH RepeatingEvents AS
( SELECT e.Name,re.StartDateTime,re.EndDateTime,re.TimesToRepeat,RepeatEventDate = CAST(c.DateKey AS DATETIME) + CAST(re.StartTime AS DATETIME),RepeatNumber = ROW_NUMBER() OVER(PARTITION BY re.RepeatEventID ORDER BY c.Datekey)
FROM dbo.Event e
INNER JOIN dbo.RepeatEvent re
ON e.EventID = re.EventID
INNER JOIN dbo.RepeatType rt
ON rt.RepeatTypeID = re.RepeatTypeID
INNER JOIN dbo.Calendar c
ON c.DateKey >= re.StartDate
INNER JOIN dbo.RepeatDayOfWeek rdw
ON rdw.RepeatEventID = re.RepeatEventID
AND rdw.DayNumberOfWeek = c.DayNumberOfWeek
WHERE rt.Name = 'Weekly'
)
SELECT Name,StartDateTime,RepeatEventDate,RepeatNumber
FROM RepeatingEvents
WHERE (TimesToRepeat IS NULL OR RepeatNumber <= TimesToRepeat)
AND (EndDateTime IS NULL OR RepeatEventDate <= EndDateTime);
这只是我如何实现它的一个非常基本的表示,例如我实际上完全使用任何关于重复数据的查询视图,以便在RepeatDayOfWeek中没有条目的任何事件将被假定为每天重复,而不是从不.随着这个和其他答案中的所有其他细节,你应该希望足够让你开始.
