Tsql union in while loop

问题:

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.

psuedo code of what I am trying to achieve:

DECLARE @var int, #tempTable
SET @var = someValue

WHILE expressionIncludingVar
  #tempTable = SELECT *
  FROM someTable
  WHERE column = @var
  UNION ALL #tempTable

  SET @var = someChangeToVar

RETRUN #tempTable

The result of the query should be #tempTable hence the weird "RETURN #tempTable".

Thank you in advance.

EDIT: 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

回答1:

Maybe Recursive CTE works for you.

You can try this.

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

回答2:

If the only thing what is different in each cycle is a counter, then why aren't you just write one query including all data?

Instead of WHERE column = @var use WHERE column >= 0 AND column <= @maxVarValue.

If your conditions are more complex, you should consider to have a small (temp) table which contains the columns to be filtered, then just join that table to your source to get the desired result.

According to the comments, you can use a tally table (or numbers table).

Example:

DECLARE @Tally (Number INT);
INSERT INTO @Tally (Number) VALUES (0),(1),(2),(3),(4),(5);

SELECT
  someAggregateColumns
FROM
  table AGG
  INNER JOIN @Tally T
    ON AGG.someDateColumn = DATEADD(month, T.Number, @date)
WHERE
  T.Number >= 0
  AND T.Number <= 3
;

The above query will return the results for the current and the next 3 months.

You can persist a numbers table and re-use it. I usually have one called util.Number.

回答3:

based in second example what you want is

 SELECT someAggregateColumns
 FROM table
 WHERE someDateColumn IN (
         @date,
         DATEADD(month, 1, @date),
         DATEADD(month, 2, @date),
         DATEADD(month, 3, @date)
        )
 GROUP BY someColumn

Now if you want a range is even easier:

 WHERE someDateColumn BETWEEN  @date
                          AND  DATEADD(month, 3, @date)

回答4:

Using a TALLY table and a CROSS APPLY you can generate the Dates to check:

DECLARE @Var INT = 4
DECLARE @Date Date = '2017-01-01'

;WITH Tally
AS
(
    SELECT ROW_NUMBER() OVER (ORDER BY Object_Id) -1 AS Num
    FROM sys.columns
)
SELECT *
FROM MyTable
CROSS APPLY Tally
WHERE Num < @var AND
    MyDate = DATEADD(month, num, @date)