declare @weeks int
set @weeks = 3

--Common Table Expressions
;with mycte as (
	-- get the lower and upper bounds to limit the recursion
	select min([table_date]) as startPoint, max([table_date]) as endPoint, [id]
	from [table]
	group by id
), mycte2 as (
	--recursive cte gets all dates that are @weeks after the startPoint and are lessthan or equal to the endPoint
	select startPoint, endPoint, id
	from mycte
	--These are very powerful and fast
	union all select DATEADD(week,@weeks,startPoint), endPoint, id
	from mycte2
	where DATEADD(week,@weeks,startPoint) <= endPoint
)
--finally, select all the items that fit into the id, and date and date + n*m weeks
select * from [table] t
inner join mycte2 m on t.id = m.id and t.[table_date] = m.date

这里有很多事情要做。以下是一些需要关注的内容

使用Common表格表格 [ ^ ]

递归查询使用公用表表达式 [ ^ ]



更新：哎呀。 Union all 是正确的语法

There is a lot going on here. Here are some things to look into
Using Common Table Expressions[^]
Recursive Queries Using Common Table Expressions[^]

UPDATE: Whoops. "Union all" is the correct syntax

试试这个：

SELECT * FROM tablename WHERE DATEDIFF(week, CONVERT(DATE,'07/03/2017',103), CONVERT(DATE,occurence,103) ) % 3 = 0

或演示 [ ^ ]