Frog Frog Recommendation: Solution to a Tree Type Question.
My question: 1. A sales system, with a list of agents, the table of each agent is the database structure table 1: Agent data sheet [ID] Auto number number [Lishu] Long integrity field [xiaoshoue] Long integer sales [Ticheng] Long integer commission [add_date] date / time agent add time requirements 1. Let the agent grade, point out that an agent can calculate his superior agent and subordinate agent. 2. The proportion of the proposal is determined by the sales of sales, the proportion of the sales of 2000 is 20%, and the proportion of sales between 2000 and 6000 is 25%, and the proportion of sales between 6000 and 18000. It is 30%, the proportion of sales between 18,000 to 30000 is 35%, and the proportion of sales between 30000 to 60000 is 40%, and the proportion of sales in sales over 600,000 is 45% .3. Commission is Cally, for example, the first month's sales is 2000, then his commission is 2000 * 20% = 400, then if the second month's sales is 2000, then his total sales is the first Plus the second month of 2000 2000 = 4000, because 4000 is bound to between 2000 and 6000, so his monthly proportion should be 25%, then his monthly commission is the sales of this month. 25%, is 2000 * 25% = 5004. The new sales of the lower agents should be added to the sales of his superior agent, such as the first-level agent, b is a subordinate agent, that is Secondary agents, the sales of this month is 2000, and the sales of this month is also 3000, but because B is a lower-level agent, a sales of this month is B this month's sales plus He's sales this month. 2000 3000 = 4000 But a month this month should not be 5,000 multiplication by 25%, should be his own sales 3000 * 25%, then add his lower level agent to give He added a difference between 2000 * 25% -2000 * 20%, the whole calculation A. This month should be achieved 3000 * 25% 2000 * 25% -20 00 * 20% = 5000 * 25% -2000 * 20% = 850, but a impossible only B an agent, he and its other sub-agency's commission differences are calculated. If A this month Sales of sales is 0, and the sales of B is 2000. The result of this month should be achieved by 2000 * 20% -2000 * 20% = 0, that is, if A and B are the same sales segment, a will not from B There is a difference in the difference. But A is not possible to make B a lower agent, so even if the month is 0, he has 2 lower-level agent, and each sub-agent sales is 2000, he is this month. The result is 4000 * 25% -2000 * 20% -2000 * 20% = 2005. If a agent's lower-level agent has 1 sales over 6000, then they belong to the same proportional paragraph, So there is no difference between them, but if there are 3 sub-agent sales at more than 600,000, he can borify 5% of the extra 600,000 part of these three lower-level agents. And some other Detailed request, I don't say it first, some of which 1.2.3.5 and 4 I have already realized. Mainly 4 algorithms are too difficult, I feel that I have to redesign the data structure, or if I don't even Out, the efficiency will be very slow. Let's take a look at the specific achievement of the requirements 4, there is no classic algorithm and solution, thank you for your pointing, if you really have a short time, this honication is not Due, even if the previous part is my own yourself. I am a boss of the boss, I am so grateful. I know that it is recurrent, but open the database of each agent to open many times, such performance Very poor, you should store itself in the array, then call, then open the record set of each sub-agent,
After calculation and the first recording set comparison, I am chaotic, I can't think of how many RecordSets have been established. How many times, how many times, how to update a data to two records, a lot of trouble, no hair . Is it a good implementation? I feel how to solve the stored procedure or array. My current practice is to open two database links, but this time is very wrong, it is best to Come, first open a link, take the required data, save the array. Open the second link, loop the array above, and operate. If you need to update the first link, you will finally update, the principle know, it is written I can't come out. Complicated recursive algorithms I really can't get it, especially in the for cycle, I still get a few IF nested. I don't know which state is in the end. Please ask this situation. The skills are analyzed. Below is a reply from the river.
This is a multi-level tree (Tree), in fact, the principle is similar to the structure of our forum, we are the main data structure of this to achieve [ID] long integer [NUM_REPLIED] double precision [Num_Followed] double Accuracy [NUM_LASTTIME] Double-precision type When a customer is the top: first-level customer, it should be num_replied = num_followed if it is the second-level customer should be: Num_rePlied = new time code Num_Followed = The last level number Num_Replied is also Say, secondary or non-primary classification customers should be num_replied <> num_followed and update Num_LastTime when updating Num_LastTime when a lower customer or a lower customer is added, and it is to say that the entire customer tree is complete, as long as Num_LastTime The same, must be the same principal classification, which is easy to get a complete customer tree, get the first step of the category Num_LastTime, according to two time yards, the whole tree structure is organized but the problem Yes: If we have to get all the superiors of a certain customer or all the lower levels? According to the above data structure, a customer can exist aspects of the same level of relationships, as long as he is not a primary classification, all Num_Replied is the same, and Num_LastTime is a level; but they may not belong to The same superior customers, all, the above structure cannot be directly completed, do the following changes: the data structure after the change [ID] long integer [Mark] long integer [Num_Replied] double precision [num_followed] double precision [Num_LastTime] double-precision type adds a Mark field, used to represent the number of customers, first-level customers with 0, 2nd 1 represented, according to such push ... So, give all the following levels of a customer This is the same, indicating the same level customer 2.mark> The customer's mark3.num_replied> The customer's NUM_REPLIED is here, you can find all the superiors of a customer, you should also know how to do it. If you think too much Trouble, I hope to use a field to express the affiliated relationship like your data structure. I have to learn how to scientifically and efficiently encode we can come to 32-bit binary strings to represent a customer code such as: 0111 0010 0001 0100 0111 0001 0101 0111 The front four bits used to represent the first level of customers, if it is a customer, then his back should be 0 is similar to: 0001 0000 00 00 00 00 00 00 00 00 If it is the second level Customers, the second paragraph should have a number, such as 0001 0000 0010 0000 00 00 00 00 0000 If it is one of the next level, there is also: 0001 0000 00000000 0000 0000 000 0000 like a secondary push, unfortunately this algorithm is used :
Bits and calculations, and this is not supported in VBS and Access, but unfortunately. Another 2 classic tree algorithm is in the construction of website, often requires processing of trees with tree-type data structures such as commodity classification, column classification, forum topics. If these categories are not encoded, the efficiency of the program is very low. So how do I design a highly efficient encoding algorithm? Here is a highly efficient classification algorithm. I am in many of our company's web applications, including e-commerce, download sites, news distribution systems, have applied such encoding algorithms, and the effect is very good. Problems to be resolved in the classification algorithm in the construction of the website, the application of classification algorithms is very common. When designing an electronic store, you should involve a commodity classification; when designing a publishing system, it is related to the column or channel classification; when the design software downloads such a program, it is related to the software classification; so, wait. It can be said that classification is a very common problem. I often interview some programmers, and I have no exception to ask them some questions about classification algorithms. Here's a few questions I often ask. Do you think you can answer it easily ^ _ ^. 1, classification algorithm often manifest as a tree representation and traversal problem. So, then, if you express the tree class classification with a table in the database, should there be a few fields? 2, how to quickly restore a tree from this table; 3, how to determine if a classification is another classification; 4, how to find all the products of a classification; 5, how to generate the path where the classification is located. 6, how to add classification; these problems are not easy to answer if the number of classifications of the classification and the number of classifications per level. This article tries to solve these problems. Classified data structure We know: The classified data structure is actually a tree. In the "Data Structure" course, everyone may learn TREE algorithms. Since we use the database in the construction of our website, we will talk from the storage of Tree in the database. To simplify the problem, we assume that each node only needs to reserve this information. We need to numbered each node. There are many ways to numbered. The commonly used in the database is the automatic number. This is like this in Access, SQL Server, Oracle. Suppose the number field is ID. In order to indicate a node ID1 is the parent node of another node ID2, we need to keep a field in the database, indicating which node belongs to the son. Ten this field name FatherID. As the ID2 here, its FatherID is ID1. In this way, we get the data sheet definition of Catalog Catalog: CREATE TABLE [Catalog] ([ID] [INT] NOT NULL, [NAME] [NVARCHAR] (50) Not null, [FatherId] [INT] NOT NULL); Agreement: We agree to use -1 as the top-level father code. Classification of -1. This is a virtual classification. It is not recorded in the database. How to restore the biggest advantage of the Catalog definition above a tree is that it can easily recover a tree-classification tree. To make a clearer display algorithm, we first consider a simple question: How to display the next class classification of a classification.
We know, to check the next class classification of a classification FID, the SQL statement is very simple: select name from catalog where fitherid = FID shows these categories, we simply use
The code is approximately as follows: <% function getAllid (Oconn, FID) DIM strTemp if fid = -1 Then straTemp = "" Else Strtemp = "" end if strsql = "SELECT NAME from catalog where fatherid =" & FID set RSCATALOG = OCONN. Execute (strSQL) Do while not rsCatalog.Eof strTemp = strTemp & rsCatalog ( "ID") & GetAllID (oConn, Catalog ( "ID")) REM recursively Loop rsCatalog.Close GetAllID = strTempEnd Function REM strConn-- connection string database, Please modify the situation based on the situation ("AdoDb.Connection" Oconn.open strconn fid = request.QueryString ("FID") strsql = "SELECT TOP 100 * from Product WHERE FatherID in (" & getAllid (Oconn, FID) ) & ")" SET RSPRODUCT = Oconn.execute (strsql)%>
Obviously, if we get a classification encoded to 1092787200, we know: Because of its code to 0100 0001001 00010 0111000 0000000, it is the fourth classification. The binary code of its parent class is 0100 0001001 0001010 0000000 0000000, and the decimal number is 1092780032. In turn, we can also know that the parent class code of its parent class is 0100 0001001 0000000 0000000 0000000, the parent class of the parent class of his parent class is 0100 0000000 0000000 0000000 0000000. (I am not too Luo:, but this is very important. Looking back to see the fifth question we mentioned earlier. Haha, this will not get the classification path where the category 1092787200 is?). Now we discuss the category code in general. The level of the category is K, the number of encoding bits of the i-th layer is Ni, then the total number of encoding bits is n (N1 N2 . NK). We get any coded form as follows: 2 ^ (N- (N1 N2 Ni)) * J parent class encodes, i represents the i-th layer, j represents the JU JU of the current layer. In this way, we divide any coded encoding into two parts, some of which are its layer coding, part of its parent class code. K a K can we be called by the following formula, we are called the signature: (because i can take K value, so there is k) 2 ^ n-2 ^ (N- (N1 N2 Ni)) for any given Category ID, if we "phase with K" ",", the non-0 encoding is the code of all the parent class!
Connect the top: Bit coding algorithm to any order encoded Catalog table, we can design a bit coding algorithm to encode all category coding specifications. In the specific implementation, let's create a temporary table: Create Tempcatalog ([Oldid] [INT] NOT NULL, [NEWID] [INT] NOT NULL, [OLDFATHERID] [INT] NOT NULL, [NEWFATHERID] [INT] NOT NULL In this table, we keep all the original category number Oldid and its parent class number OldfatherID, and recalculate the corresponding number NEWID, NewfatherId, which satisfies bit coding requirements. The procedure is as follows: <% Rem Oconn --- Database connection, the REM oldfather - the original parent class number Rem newfather --- New parent class number Rem n --- The total number of codes Rem ni - each Level Code Bit Number Rem Level - Current Sub Formatallid (Oconn, Oldfather, Newfather, N, NM, Ni Byref, Level) strsql = "Select Catalog Where FatherId =" & Oldfather Set Rscatalog = Oconn.execute (strsql) j = 1 do while not rscatalog.eof i = 2 ^ (n - n - n - n - n - nm) * J if Level Ten i = i newfather Oldcatalog = rscatalog ("catalogid" newcatalog = i REM write temporary table strSQL = "Insert into TempCatalog (OldCatalogID, NewCatalogID, OldFatherID, NewFatherID)" strSQL = strSQL & "values (" & OldCatalog & "," & NewCatalog & "," & OldFather & "," & NewFather & ")" Conn. Execute Strsql REM Recursive Call Formatallid NM = NM NI (Level 1) Formatallid Oconn, Oldcatalog, Newcatalog, N, NM, Ni, Level 1 RSCATALOG.MOVENEXT J = J 1 Loop RsCatalog.closend Sub%> Call this algorithm One example is as follows: < % REM defines the encoding parameters, where n is the total number, Ni is the number of bits per level.
DIM N, NI (5) Ni (1) = 4 n = Ni (1) for i = 2 to 5 ni (i) = 7 n = n Ni (i) Next REM Open the database, create a temporary table strsql = " Create Tempcatalog ([Oldid] [INT] NOT NULL, [NEWID] [INT] NOT NULL, [OLDFATHERID] [INT] NOT NULL, [NewfatherID] [INT] NOT NULL); "SET CONN = Server.createObject (" AdoDB .Connection ") Conn.open Application (" strconn ") conn.execute strsql Rem call Specification Roller FormatallId CONN, -1, -1, N, Ni (1), Ni, 0 Rem ------- -------------------------------------------------- -------------- Rem Update the category of all related tables to new encoding. Rem ------------------------------------- ----------------------- REM Close Database strsql = "Drop Table Tempcatalog;" Conn.execute strsql conn.close%> Fourth question Now let's go back Take a look at the fourth question: How to get all the products under certain categories. Due to the use of bitcodes, the problem is now simple. We are easy to estimate: a product belongs to a category is Product.fatherID & (Specifier of Catalog.ID) = Catalog.ID. Where "&" represents the bit and algorithm. This is directly supported in SQL Server. For example: the category belongs to: 1092787200, and the current category is 1092780032. The current category corresponds to: 4294950912, because 1092787200 & 4294950912 = 8537400, this product belongs to classification 8537400. We have given a formula for calculating the feature code. There are not many features, and it is easy to calculate, consider calculating when the Application_onstart time trigger in Global.asa is triggered, stored in an APPLICATION ("Mark") array. Of course, there is a signature, we can also get more efficient algorithms. We know, although we use bit coding, it is actually a way to encode. The classification coding of Group I is definitely smaller than the coding of the i 1 class. According to this feature, we can also get two signatures from the FID, one of which is the characteristic code FID0 of this level, one is the upper level feature code FID1. The full essential condition of the product belongs to a classification FID is: Product.fatherID> FID0 and product.fatherid Since the data table product has indexed FatherID, the query speed is extremely fast: <% Rem Oconn --- Database connection, the Rem FID has been opened - the current classification remoteMark --- feature value array, typically Application ("Mark") REM K - A number of elements of array, is also the classification of the class SUB GetAllProduct (Oconn, FID, FIDMARK BYREF, K) REM calculates the feature value FID0, FID1 for i = k to 1 IF according to FID FID and FIDMARK = FID) THEN EXIXT strsql = "Select Name from Product Where" "FIDMARK (I) &" and FatherId <"Fidmark (i-1) set rsproduct = Oconn.execute (strsql)%> <% Do while not rsproduct.eof%>
Sub mainlist () Show Subject On Error Resume Next Set Rs = Server.createObject ("AdoDb.Recordset") SQL = "SELECT *" & BBS_ID & "WHERE LANP_LEVEL = 1 Order By LANP_SUBDATE DESC" RS.Open SQL, CONN, 1 1SET RS = conn.execute ("BBS_LEVEL1" & BBS_ID & ") if not = 15 rs.abs.pageSize = 15 rs.absolutepage = 1 if Request (" Page ") <>" "" " ") rowcount = rs.pagesize session (" pagecu ") = rs.pagecount response.write"
WHERE LANP_ID IN ("& LANP_REPLY &") "LANRS.OPEN SQL2, CONN, 1, 1 set lanrs = conn.execute (" BBS_LEVELN "& BBS_ID &", "& LANP_Reply &") Response.write "
"Word)" end if llanu = lanrs ("lanu_nickn") response.write "( "& llanu &" " & lanchp_date & "" "Show post time response.write" Read: "& lanch_reads1 &") "Show Posts Number if Datediff ("D", LANP_DATE, DATEADD ("D", - 1, now ())) <0 THEN If it is within 2 or two days, labeled "new" picture response.write " "end if response.write" "rule paragraph display end if LANP_Reply1 <>" "and lanch" "0" then if the post still respond (Substims), then call this subroutine ("LANP_Reply", LANP_ID Else end if lanrs.movenext moves to the next record loop response.write "" LANRS.CLOSE SET LANRS = Nothingend Sub This is a relatively simple tree-made implementation provided by the developer club.
The tree structure is still very common in our application, such as file directory, BBS, permission setting, department settings, etc. These data information use hierarchical structures, and it is difficult to express in our current relational database. So how to handle how to process the tree structure problem in the program? Recently, the author has solved a problem through an ASP permission management, and now it is now sorted out to readers. First, the hierarchical data model is to be transformed into a relational data model. That is to say how to design this data structure in our Access, SQL Server, Oracle and other relational databases. Tell you, such as one of the following data: Document management 1 | ---- New Document 2 | ---- Document Modification 3 | ---- Document Archive 4 | ---- View Archive Information 5 | | ---- Delete Archive Information 6 | | | ---- Delete History Document 7 | | | - Delete Official Document 8 | ---- System Management 9 | ---- User Management 10 Personnel Management 11 Administrative Management 12 Financial Management 13 This is a typical hierarchical structure data, then everyone wants to think, how to express it through two-dimensional tables? It's hard to look at it, is it. But carefully scrutinize or have a doorway drilled. It can be described, regarding all the permissions above as a permission field, then this permission field is definitely there is an ID value. We will give this relational data sheet to add a field-membership ID field, which means that this permission is under the level of permissions, that is, this ID value is part of the ID value. For example: "Viewing the Archive Information" permission ID value is "5", it is under "Document Archive" permission, then its belonging to the value of the Id field should be "4". OK, if this can be understood, then our relationship transformation work is basically completed. Below we start designing this relational data sheet (as examples of SQL Server 7.0): ----------- --------- --- ------ ----------- ---------- | Field Name | Field Meaning | Field Type | Field Size | Field Properties | - -------- ----------- ---------- ----------- ----- ----- | Selfid | Permissions ID | INT | 4 | PK || PowerName | Permissions | VARCHAR | 50 | NOT NULL || PowerInfo | Permission Information | VARCHAR | 500 | || BelongID | Particular ID | INT | 4 | ----------- -------- ---------- ---------- - -------- Good, structural design, you can easily enter your test data. Then, we are the most critical steps for how to imitate the hierarchy in the web page.
Program List: PowerList.asp <% Database Connection SET CONN = Server.CreateObject ("AdoDb.Connection") Conn.open "Driver = {SQL Server}; server = chaiwei; database = chaiwei; uid = sa; pwd =" Open All parent data set = server.createObject ("adoDb.recordset") rs.open "Select * from power" Main program section Do while not rs.eof prints parent data information response.write " "& RS (" PowerName ") &" " subroutine call, sublayer data processing CALL LISTSUBPOWER (RS ("PowerID")) rs.MovenextLoop Close parent data set rs.closset RS = Nothing sub-data processing subripes SUB ListSubpower (ID) Open all sub-data information sub-data information belonging to the upper PowerID set rs_sub = server.createObject ("AdoDb.Recordset") rs_sub.open "Select * from Powers Where Belongid = "& id &" Order by PowerID ", CONN, 1, 3 List Data Do WHILE NOT RS_SUB.EOF Hierarchical State Variable Incigrant Format_i = Format_i 1 Cyclic Rebate Format Control, Because the top layer is not Need to indent, so reference this block for i = format_i to 3 step -1Response.write "|" response.write "NEXT Print sub-data information respon SE.WRITE "| ----" Response.write "" & rs_sub ("POWERNAME") & "" recursive recursive subprint itself, gradually handling listsubpower (RS_SUB ("PowerID") rs_sub.movenextLoop Hierarchical Statement Variables Retreat reduction format_i =
Format_i-1 Test sublayer Data Set rs_sub.closset rs_sub = Nothing End Sub%> PowerList.asp program, let's open the top data first, display it in the loop; then design a subroutine listSubpower, in the loop through the recursive algorithm Call, with this to open the sub-layer data information, and call yourself repeatedly in the inner cycle of the subroutine to create deep data by layer. In addition, a static variable Format_i is also used in the program to control the indent display format. In this paper, the tree structure is simple attempt in data design, program control, with the purpose of throwing bricks, hoping that readers get more revelation this is the storage and maintenance of 9CBS's double cadres written.
Storage of Tree Structure Data Adoption: Tree (ID, ParentID, Remark) If only for storage, it is undoubtedly the most economical! But use this structure to provide some of the application performance form of a slightly complex point, should be said Quite low! Such as: Query a node, if you want to query, we can work hard when maintaining data! We take a tree Structure Forum as an example: Tree (ID, ParentID, rootid, OrderID, Maxid , Indent, Title, Content, Remark ID: INTEGER Post iDParentId: INTEGER Pupire ID ORDERID: INTEGER Renali ID ORDERID: Integer In the same root post, post order ID Maxid: Integer is used to make new posted on top Indenter Regeneration Title: VARCHAR Post Title Content: VARCHAR Posts Remark: VARCHAR Posts Remark: VARCHAR In addition to ID, Parentid's Post-Word Clues As long as you maintain each field, you will improve the efficiency of the query! Please see the maintenance procedure below: - - =================================================== ilter procedure appsp_addnew @ id integer, @Title varchar (8000) = null, @ Content varchar (8000) = NULLAS - DECLARE @ID INT - set @ id = 0if @ id = 0 Begin Insert Into Tree (ParentID, OrderID, Indenter, Title, Content) VALUES (0, 0, 0, @ title, @ Content) - Top the post to the top: Update Tree set rootid = id, maxid = (Select max (id) from tree) Where Rootid Is Null Endelse Begin - Adjust the same "root post", internal order: update tree set orderid = Orderid 1 where rootid = (Select rootid from tree where id = @ID) And ORDERID>
(Select Orderid from Tree WHERE ID = @ID) - Insert a reply post while maintaining rootid, parentid, orderid, indent, remark, title, content insert Into Tree, REMARK, TITLERID, IND, Remark, Title, Content ) Select rootid, @ id, orderid 1, indent 1, Case When Remark is Null Then Cast (Parentid As Varchar) Else Remark - Cast (Parentid As Varchar) End, Isnull (@ title, Re: Title) , @ Content from tree where id = @ ID - Top the post to the top: Update tree set maxid = (Select max (id) from tree ) Where rootid = (select rootid from tree where id = @ id) end - ================================ ======== This program is used for 1. Add new stickers: Appspus_addnew 0, the first question, the earth is round? 2. Reply to post: Appspsp_addnew 1, Re: The first problem, the earth is circle This! Simply query: SELECT *, REMARK CAST (PARENTID As Varchar) - Cast (ID as varchar), Space (Indent)
