Unequal connection
In general, the SQL language is inadvertently. Want to perform an orderly processing, such as comparing the front and rear items of a sequence, you must use a cursor. However, in some cases, another method can be employed, without a cursor, can process ordere information, which is unequal connection. First look at one example
Former flight, 9CBS netizen Buildit trust, and I discussed this question: The following table History
Create Table [History]
[TheDate] [datetime] null,
[Quantity] [int] NULL
) On [primary]
The store is a series of historical data, such as:
INSERT HISTORY VALUES ('2002-01-01 00: 00:0', 11)
Go
INSERT HISTORY VALUES ('2002-01-02 00: 00:00.0', 34)
Go
Insert History Values ('2002-01-03 00: 00:00.0', 27)
Go
INSERT HISTORY VALUES ('2002-01-04 00: 00:00.0', 43)
Go
Now, we have to query the total amount of the start date to each date. That is to say, showing such a result set:
TheDate Quantity Q_SUM
2002-01-01 00: 00: 00.0 11 11
2002-01-02 00: 00: 00.0 34 45
2002-01-03 00: 00: 00.0 27 72
2002-01-04 00: 00: 00.0 43 115
Intuitively, we can build a cursor on the Select * from history order by ourate, start from the first one, each, add once. Do you change my idea? If we establish such a result set, let each day period, the number of days, and all the quantity records before it. Then we can packet according to this date and ask for quantity. Obviously, an unqualified query is formed so. I have an error in the initial way, the following is the final statement after modification of Buildit
Select L. Hedate,
L.quantity,
Sum (r.quantity) AS Q_SUM
From History L
Join History R
On L. Hedate> = R.Thedate
GROUP BY L. ITHEDATE, L.QUANTITY
Order by l.-itdate
It is not one of the corresponding coupling itself, and its correspondence and the order have a close relationship. This is why we can take it to perform an orderly manner. Give one more natural example:
SELECT L.I, SUM (R.I)
From n l
Join N r
On L.I> = R.i
Group by L.I
Table N has only one integer column I to save natural number columns. So, there is nothing mystery, this is the sum of natural numbers. Here SUM (R.I) means that the natural number N N from zero to i is accumulated and simpler than the previous problem. However, it is obvious that this is not a place where it is uniform, because it will form a huge triangular data set, just like this.
1 1
twenty one
twenty two
3 1
3 2
3 3
...
When I execute this query for the sixteen integer list, my Athlonxp1700 / 256mddr machine has run for nearly 30 minutes. When I write down this text, it returns a data overflow error. Obviously, for this query, even the list of sixteen integers is too big. My suggestion is that when the result set does not use the formula expression, use unequal connection. As this is accumulated, we have already had a mature formula, why bother to make the computer stupid? Use the following statement Select I, ((1 i) * i) / 2
From n
Compared with the old man, the speed is very fast. When the data is overflow, there is no one second, but this computer is not thinking of this method, hehe ...
When a generation of Mathematics a generation of masters, his teacher has taken him this issue. So almost all Chinese students have been tortured by teachers. It seems that the purpose of the teacher is to tell us that our IQ is more than a Gauss. But I didn't want to be more than others.
When college, teach us the first "Mathematical Analysis", the teacher said that the computer is a fool. I just felt fun. Today, I saw it. It seems that the computer is the level of my primary school. I will never catch up with Gauss.
However, this thing is used in a place where the formula is difficult to express, it is still meaningful. For example, one of my friends has written a single screen with a number of pieces, it is fun. Although it is not more efficient than the code to be written by the code of process, it can express the essential essential importance of the screening method. Maybe we have studied the atrialism, which will use this SQL style Indication. This friend taught me a lot of computer knowledge, for respect for him, I won't copy his code. However, this statement itself is not complicated. I believe that my friends think that after using the joint query, they must write it. If you are interested, you may wish to try it. Use it to implement other number columns, then we will discuss several.
There is also a usage of unsuitable coupling, you can use it to generate a serial number, such as
SELECT Count (L.afield) AS ID,
L.afield
From MyTable L
Jion MyTable R
On L.Afield> R.afield
GROUP BY L.AFIELD
The Afield field can be a string, date, of course, can also be a value, or can be sorted by alone. This thing is a little quiet, the data is too large, it is not fun, generally use the physical line number, although not the SQL standard, but practical. This example I have seen in the MCDBA review (it is said to have been examined), but my friend will make it, everyone may have independent implementation.
The orderly operational ability of queries is obviously from the sorting and ranking of the coupling field, so it is best not to do unequal coupons on the field with repeated values (in fact, it is best not to be on the field with repeat value. Do any joint, unless you are very sure what you are doing). The equivalent connection of the data explosion is terrible, and if you don't wait, if you have explosion ... 嘿 ... ... ...
Imagine a pair of repetition values in an equivalent joint, may have two pairs of repetition results. However, if it is inequality, it is related to the replicated location. Because this is a triangle, it appears on the top, if there is a lower part of this triangle ...
I am obviously a powerful tool, but it is also one of the shortcuts. There are a few suggestions, it is my experience: if the joint will generate a large "triangle", do not use, try the child inquiry or even the cursor; the resulting result set relatively small to the original table, as much as possible Useless data is filtered first;
It is clear that the number calculation will be expressed (because it is non-processed), but it is usually not an advantage, so it is best to play, it is best to consider it.
There is also a unworthy connection not to be used in multiple connections, otherwise the leverage can be caused.
I wish you all a happy trip in this magical world!
