SQL story Excerpt (eight) ———— data extraction

Data
Data extraction

Theoretically relational database, data is in the form of relationship. Usually we can see them as a collection. In this way, the data is generally in the form of disorder. The benefits of this approach are not to be discussed, but we have to admit that sometimes this can also bring us some trouble. I've had this thing lately.

I met an old classmate of mine on the internet and he raised such a question. There is a table (assuming it is called mytable), and there are three integral fields (assuming called a11,a12,a13) with a unique key constraint on the table. Now he wants to take some samples from the data in this table. He wanted to take a record of each of the values from the A13. Then I asked him, what are the requirements for A11 and A12? He said, no, how to take all can be, there is no rule of the line. I take it for granted, simple, give me 20 minutes.

I don't know if you've heard a joke. Said a long time ago, a big mathematician lectures, a student asked him whether he can prove the four-color theorem. He said, that's easy, I'll testify to you now ... Then he wrote and wrote Ah ... Think about it ... have been thinking of class also did not testify out, and then played a big thunder outside, he said, "God is also my arrogance angered ..."

What, you asked me what happened to the question just now? I'm telling you, 20 minutes, no, 40, no, it's like one hours ... Later in the night, the outside began to thunder ... I said to my classmate, man, I was wrong, let me take this question back to think first ...

Here's an example where I'm just going to 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

In the beginning, I wanted to use a select Max (A11), Max (A12), A13 from MyTable GROUP by A13, and then thought about it with my toes and found it impossible. Because Max (A11) and Max (A12) only depend on group by A13. Obviously, Max (A11) and Max (A12) are not necessarily the data in the same record. Just like the following:

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.

And then I thought, SELECT Max (A11), Max (A12), A13 from MyTable GROUP by A13, A12 do not? Try it with SQL Server, or not. Because of this, it will first press A13, A12 to group the result set returned, so it will be more than we want the data. It's like the following.

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've done a lot of stupid things like that. And then I finally figured out what I wanted. Data: Take A12,a13, so that after all the values grouped by A13, take the largest A12 in each group, and then take the entire record, so that A11 is the A12 and A13 to meet the aforementioned conditions after the largest value. So, 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 O.a13

This line statement returns the following values:

A11 A12 A13

----------- ----------- -----------

0 0 0

3 3 1

3 3 2

2 3 3

Similarly, we can explain that it is the two result sets, one is select Max (A11), Max (A12), A13 from MyTable GROUP by A13, one is select Max (A12), A13 from MyTable Grou P by A13, A12, and then by A12 and A13 for an equivalent 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 looks more refreshing, I do not know what you think?

In fact, this kind of data extraction is not possible with a simple query, because one of the fields to be grouped and statistics. It's just that (in fact, as long as it is) to describe our problem in a SQL-language way of thinking, the answer is there, and it's all that simple.

For a class of completely random extraction of the problem, in addition to the top N problem solving, there is a good idea is to support the physical line number of the database system, with ROWID or identity columns, such as the technology to solve, this is relatively simple, not much to say.

As I said before, InterBase does not support automatic identity columns, but in practice it can also be implemented indirectly. In InterBase, we can create a global variable called generator, and then use a trigger, combined with a function called gen_id, that can be done. It's a bit of a hassle, but it's not impossible. Detailed information, you can query InterBase relevant technical documents.

Welcome to the letter and I discuss technical issues: Lx1978@263.net.