問題描述
我試圖在更改 where 子句中的某些值時將同一個表與其自身聯(lián)合在一起.我的問題是循環(huán)之間的聯(lián)合.我不能使用表變量，因為架構(gòu)太復(fù)雜，無法每次都手動編寫.臨時表似乎是要走的路，但我不知道如何讓它工作和正確的語法.
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.
我想要實現(xiàn)的偽代碼:
DECLARE @var int, #tempTable
SET @var = someValue
WHILE expressionIncludingVar
#tempTable = SELECT *
FROM someTable
WHERE column = @var
UNION ALL #tempTable
SET @var = someChangeToVar
RETRUN #tempTable
查詢的結(jié)果應(yīng)該是 #tempTable 因此奇怪的RETURN #tempTable".
The result of the query should be #tempTable hence the weird "RETURN #tempTable".
提前致謝.
另一個硬編碼示例:我正在嘗試對這樣的東西進(jìn)行硬編碼:
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
這篇關(guān)于while循環(huán)中的Tsql聯(lián)合的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！