 |
|||||
go
Equivalent of recursive CTEs |
|||||||||||||||||||||||||||||||||
|
Let’s consider a simple query generating a Fibonacci sequence (in a Fibonacci sequence, every number is the sum of the two preceding ones, and the first two numbers are 1 and 1):
This query generates 18 Fibonacci numbers (excluding the first two):
Now, let’s analyze how it works. Lines 5) and 6) generate 9 rows. So far, there is nothing extraordinary here. In line 7), two variables @I, @J are declared and assigned the value 1. The third line does the following: initially, the variable @I is assigned the sum of values of two variables. Then, the same expression is assigned to the variable @J – but with the changed value of @I. In other words, values in the SELECT statement are calculated from left to right – also see the note at the beginning of this article. Take into account that the variables are assigned new values for each of our 9 data records, that is, when processing of a new row begins @I and @J contain values calculated for the preceding row. To fulfill this task in other DBMSs, we’d have to resort to a recursive query!
Notes:
Variables have to be declared in a separate subquery (see line 7); if a variable was declared directly within the SELECT statement, it most likely would be evaluated only once (although the actual behavior depends on the server version). The type of the variable is stipulated by the value it is initialized with. If the variable is assigned a NULL value, its type will be BLOB. As mentioned before, the order the records are processed in by the SELECT statement depends on custom sorting. Here is a simple illustration of how custom row numbering is done:
|
|||||||||||||||||||||||||||||||||
