问题描述
我试图在更改 where 子句中的某些值时将同一个表与其自身联合在一起.我的问题是循环之间的联合.我不能使用表变量,因为架构太复杂,无法每次都手动编写.临时表似乎是要走的路,但我不知道如何让它工作和正确的语法.
I am trying to union the same table together with itself while changing some value in the where clause. The problem i have is with the union between the loops. I can not use a table variable since the schema is too complicated to write by hand each time. Temp tables seem to be the way to go but I do not know how to get it to work and the correct syntax.
我想要实现的伪代码:
DECLARE @var int, #tempTable SET @var = someValue WHILE expressionIncludingVar #tempTable = SELECT * FROM someTable WHERE column = @var UNION ALL #tempTable SET @var = someChangeToVar RETRUN #tempTable
查询的结果应该是 #tempTable 因此奇怪的RETURN #tempTable".
The result of the query should be #tempTable hence the weird "RETURN #tempTable".
提前致谢.
另一个硬编码示例:我正在尝试对这样的东西进行硬编码:
Another hardcoded example: I am trying to unhardcode something like this:
SELECT someAggregateColumns FROM table WHERE someDateColumn > @date and < someDateColumn < DATEADD(month, 2, @date) GROUP BY someColumn UNION ALL SELECT someAggregateColumns FROM table WHERE someDateColumn > DATEADD(month, 1, @date) and and < someDateColumn < DATEADD(month, 1, DATEADD(month, 3, @date)) GROUP BY someColumn SELECT someAggregateColumns FROM table WHERE someDateColumn = DATEADD(month, 2, @date) DATEADD(month, 1, DATEADD(month, 4, @date)) GROUP BY someColumn UNION ALL ....etc
推荐答案
也许递归 CTE 适合您.
Maybe Recursive CTE works for you.
你可以试试这个.
DECLARE @MyTable TABLE(ID INT, ColumnA VARCHAR(10), ColumnB VARCHAR(10)) INSERT INTO @MyTable VALUES (1,'A', '10'), (2,'B', '11'), (3,'C', '12'), (4,'D', '13'), (5,'E', '14'), (6,'F', '15'), (7,'H', '16') DECLARE @var INT = 4 ;WITH CTE AS ( SELECT * FROM @MyTable WHERE ID = @var UNION ALL SELECT T.* FROM CTE INNER JOIN @MyTable T ON CTE.ID - 1 = T.ID ) SELECT * INTO #tempTable FROM CTE SELECT * FROM #tempTable DROP TABLE #tempTable