Data extraction
Theoretical relational database, data is present in the form of a relationship. Usually we can see them as a collection. In this way, the data is typically present in disorderly form. The benefits of this approach do not have to discuss more, but we have to admit that sometimes it will bring us some troubles. I have encountered such a recent.
I met my old classmate online, and he made such a problem. There is a table (assuming is called myTable), there are three intellectual fields in the table (assuming about A11, A12, A13), there is a unique key constraint. Now he wants to take some samples in the data of this table. He wants to take a record from each of the value of the A13. Then I asked him, what is the requirements for A11 and A12? He said, no, how can you take it, there is no rule. I think of course, simple, give me twenty minutes.
There is a joke I don't know if you have heard it. It has been a long time ago, a big mathematician lecture course, there are students asking him that he can prove the four-color theorem. He said, that is easy, I will give you now ... then he wrote ah ... I thought I thought ... I always thought that I didn't have a priority, and then played a big thunder outside. He said, God said, God I am also angry with my arrogance ...
What, how is you asking me what is the problem? I tell you, no, no, forty minutes, no, it seems to be an hour ... later arrived in the middle of the night, began to thunder ... I said to my classmate, my brother, I am wrong, let me Take this question back to think about it first ...
The following is an example, I will record some data.
A11 A12 A13
--------------------- -----------
0 0 0
0 0 1
0 1 1
1 1 1
1 1 2
1 2 2
2 2 2
2 2 1
2 2 3
2 1 3
2 3 3
3 3 1
3 3 2
3 2 3
3 2 1
3 2 2
3 1 1
3 1 2
3 1 3
At first, I want to use a SELECT MAX (A11), Max (A12), A13 from MyTable Group by A13, and later think that it is impossible to use toes. Because Max (A11) and Max (A12) are only dependent on group clauses Group BY A13. It is apparent that MAX (A11) and Max (A12) are not necessarily data in the same record. Just like this:
A13
--------------------- -----------
0 0 0
3 3 1
3 3 2
3 3 3
Note that in fact, 3, 3, 3 this line does not exist.
Then I think SELECT MAX (A11), Max (A12), A13 from MyTable Group By A13, A12 line does not work? Try the test with SQL Server, nor is it. Because of this, it will first press A13, A12 to group the returned result set, so it is much more than what we want. Just like this
A13
--------------------- -----------
0 0 0
0 0 1
3 1 1
3 1 2
3 1 3
3 2 1
3 2 2
3 2 3
3 3 1
3 3 2
2 3 3
I still have a lot of stupid things, and later I finally want to understand what data I want: Take A12, A13, so that after all the Value of the A13, take the largest A12 in each group, then refine the record, make A11 is the maximum value after the A12 and A13 satisfy the aforementioned conditions. In this way, I wrote a statement: SELECT
(Select Max (I.A11) from MyTable I where I.a13 = o.a13 and I.A12 = max (O.A12)) A11
, Max (O.A12) A12
, O.a13 a13
From MyTable O
Group by .13
The return value of this line statement is as follows:
A11 A12 A13
--------------------- -----------
0 0 0
3 3 1
3 3 2
2 3 3
Similarly, we can explain it for the most two result sets, one is SELECT MAX (A11), Max (A12), A13 from MyTable Group by A13, one is SELECT MAX (A12), A13 from MyTable Group by A13, A12 However, the A12 and A13 are pressed by equivalence connection, which is:
SELECT MAX (L.A11) A11
, Max (L.A12) A12
, L.A13 A13
From MyTable L
Join MyTable R
On L.A13 = R.A13
Group by L.A13, L.A12
Having l.a12 = max (r.a12)
I think this is relatively refreshing, I don't know what you do?
In fact, this type of data is extracted, it is impossible to use a simple query because there is a field to participate in packets and statistics at the same time. Only (in fact, as long as it) describes the way our problems will be described in the way, the answer is also obtained, everything is so simple.
For a class of randomly extracted, the problem is solved by TOP N, there is a good idea to solve the technology in the database system that supports the physical line number, which is solved with the technology such as RowID or identity column. Simple, there is not much to say.
Add a sentence, I have said that INTERBASE does not support automatic identity, but in fact, it can also be indirectly implemented. In Interbase, we can create a global variable called Generator, then use a trigger, combined with a function called Gen_ID, you can do it. Although it is a bit trouble, it is not impossible. For details, you can query InterBase related technical documentation.
Welcome to letter and I discuss technical issues: lx1978@263.net.
