Shulou(Shulou.com)06/03 Report--

In the development of database applications, we often have to face complex SQL computing, and multi-level association is one of them. The join statement of SQL is abstract and only suitable for expressing simple association relations. Once there are more levels of association, the corresponding code will become very complex. On the other hand, SPL can use object references to express associations, thus making the code more intuitive, which is illustrated by an example below.

Table channel stores the correspondence between all the channels of a website and their parent channels, which are represented by ID and PARENT fields respectively, up to four levels, where root represents the site itself (that is, the root node). Some of the data in the table are as follows:

IDPARENTNAMEp1rootnewsp2roothealthp3rootmanagec11p1Scenic Introductionc12p1Places of Interestc13p1Local culturec21p2Scenic Servicec22p2E-commercec31p3Travel Tipsd111c11Investment Projectsd112c11Virtual tourd113c11Places of Interestd114c11Historical legendsd115c11Resort weatherd121c12Ticket booking

Now you want to hierarchically list the names of all the subordinates of the channel based on the ID entered by the parameters, separated by commas between the subordinates of the same layer. Assuming that the value of the parameter arg1 is p1, the expected result is shown in the following figure:

The SPL code is as follows:

AB1=db.query ("select * from channel") > A1.switch (PARENT,A1:ID) 2=create (ID,LEVEL,SUB)

3=A1.select (PARENT.ID==arg1) > A2.insert (0quotient arg1) > A2.insert A3. (NAME). String () 4=A1.select (PARENT.PARENT.ID==arg1) > A2.insert (0meme arg1jue 2 5=A1.select A4. (NAME). String () 5=A1.select (PARENT.PARENT.PARENT.ID==arg1) > A2.insert (0Eng Arg1) 3PowerA5. (NAME). String () 6 > file ("channel.xlsx") .xlsexport @ t (A2).

A1: query table channel. Some of the results are shown below:

B1: > A1.switch (PARENT,A1:ID), use the function switch to replace the PARENT field in the table with the corresponding record reference, as shown below:

After switching, you can directly use PARENT.ID to represent the superior (parent) channel, while PARENT. PARENT. PARENT.ID can directly express the level 3 (great-grandfather) channel. Although join can also be used to represent this kind of self-connection in SQL, it is obvious that confusion can occur when there are more levels.

A2:=create (ID,LEVEL,SUB) to create an empty table to store the final calculation results.

A3:=A1.select (PARENT.ID==arg1), query the records in which the parent channel is equal to the parameter arg1 from the table, that is, the next level (child) channel of arg1. The calculated results are as follows:

B3: > A2.insert, add a record to A2, the first field value is arg1;, the second field value is 1, indicating the first level subchannel, and the third field is the expression A3. (NAME). String (), which means to take out the column NAME in A3 and spell it into a comma-separated string. The calculated results are as follows:

A4:=A1.select (PARENT.PARENT.ID==arg1), which is similar to A3, indicates that the next two (grandson) channels of arg1 are queried from the table. The results are as follows:

A5 is similar to A4, which means taking out the lower three (great-grandchild) channels of arg1. You can easily take out N-level channels in a similar way.

B4, B5 and B3 are similar in that they all append new records to A2, except that the level field is changed to 2 and 3. After executing B5, A2 is the final result of this operation:

Just now, the value of parameter arg1 is p1. If you enter c12, the calculation result is as follows:

Sometimes we want to see clearer data, such as listing all the subordinate channels of a channel one by one and marking the hierarchy. As shown in the following figure:

To implement this algorithm, you can use the following code:

AB1=db.query ("select * from channel") > A1.switch (PARENT,A1:ID) 2=create (ID,LEVEL,SUB)

3=A1.select (PARENT.ID==arg1) = A2.insert 4=A1.select (PARENT.PARENT.ID==arg1) = A2.insert) 5=A1.select (PARENT.PARENT.PARENT.ID==arg1) = A2.insert (0lemagne Arg1Magne3) 6=A2. (~ .SUB.new (A2.IDLING A2.LEVELMAL IDRANAME))

7=A6.union ()

The red font is the changed code, where the code in B3 is = A2.insert. This means that the record of A3 is directly stored in A2. Assuming that the value of parameter arg1 is p1, the calculation result is as follows:

Click on the SUB field and you can see the detailed record:

As you can see, the field values of SPL are generic, and you can store record groups, or individual records. The essence of the function switch is to switch the foreign key to the corresponding record in the main table.

After executing B5, the result in A2 is as follows:

A6:=A2. (~ .SUB.new (A2.IDMagaza 2.Level ID ghet.SUBID _ name)), which is used to put the ID and LEVEL in A2 into each record in the SUB field. A2. () indicates the calculation of A2. In the calculation, "~" can be used to represent each record in A2, and ~ .SUB represents the SUB field (record group) of each record. The function new is used to generate a new sequence table, that is, ID field and LEVEL field in A2, ID field and NAME field in SUB. After the calculation is complete, the value of A6 is as follows:

A7=A6.union (), this code is used to put the records of A7 together to form the final calculation result:

Sometimes we need to list all the subordinate channels of each channel directly. To implement this algorithm, you can use SPL's for statement, as follows:

ABC1=db.query ("select * from channel") > A1.switch (PARENT,A1:ID)

2=create (ID,LEVEL,SUB)

3for A1. (ID) = A1.select (PARENT.ID==A3) = A2.insert.

= A1.select (PARENT.PARENT.ID==A3) = A2.insert (0meme A3jin2 B4) 5

= A1.select (PARENT.PARENT.PARENT.ID==A3) = A2.insert (0recorder A3JEI B5) 6=A2. (~ .SUB.new (A2.IDMere A2.LEVELMIDRIN SUBID _ name))

7=A6.union ()

The code for A1. (ID) in A3 represents the ID field of loop A1, one at a time, and the loop variable can be represented by the cell A3 where the loop statement is located. The scope of the loop can be expressed by indentation, and the scope of the loop in the example is B 3:C5. The final calculation result is in A7, and some of the data are as follows:

From these examples, we can see that when using SPL to simplify the multi-level association problem, the train of thought is intuitive, the code is simple and easy to understand, and the hierarchical relationship is clearly visible. Compared with SQL, such SPL code can greatly reduce development costs and greatly simplify post-optimization and maintenance work.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.