Problems to be solved by algorithm classification algorithm
In the website construction, the classification algorithm application is very widespread. When designing an electronic store, it involves the classification of goods, the classification of columns or channels when designing a publishing system, and the classification of software when designing software downloads; It can be said that classification is a very common problem.
I often interview some programmers, and I almost invariably ask them some questions about classification algorithms. Here are a few questions that I often ask. Do you think you can easily answer ^_^?
1, the classification algorithm often manifests as the tree representation and the traversal question. So, excuse me: If you use a table in the database to express the tree category, how many fields should there be?
2. How to quickly restore a tree from this table;
3, how to determine whether a classification is another subcategory of the subclass;
4, how to find a category of all products;
5, how to generate the path of the classification.
6, how to add new classification;
These questions are not easily answered without limiting the number of levels and categories of classification. This article tries to solve these problems.
Data structure of the classification
We know: The data structure of the classification is actually a tree. In the course of data structure, you may have studied the tree algorithm. Since we use a lot of database in the construction of the website, we will talk about the storage of tree in the database.
To simplify the problem, we assume that each node only needs to keep the name of this information. We need to number each node. There are many kinds of numbering methods. An automatic number is often used in a database. This is true in Access, SQL Server, and Oracle. Assume the Number field ID.
In order to indicate that a node ID1 is a parent node of another node ID2, we need to keep a field in the database to show which node this taxonomy belongs to. Name this field Fatherid. such as the ID2 here, its fatherid is ID1.
In this way, we get the data table definition for the classification catalog:
Create Table [Catalog] (
[ID] [int] not NULL,
[Name] [nvarchar] () not NULL,
[Fatherid] [INT] Not NULL
);
Agreement: We agreed to use-1 as the first layer of the classification of the Father code. The category is numbered-1. This is a virtual classification. It has no records in the database.
How to restore a tree
The biggest advantage of the catalog definition above is that it makes it easy to recover a tree-a classification tree. To show the algorithm more clearly, let's consider a simple question: How to display the next level of classification for a category. We know that to query the next level classification of a taxonomy FID, the SQL statement is very simple:
Select Name from Catalog where Fatherid=fid
When displaying these categories, we simply use <LI> to:
<%
REM oconn---Database connection, opened when calling GetChildren
REM FID-----Number of the current category
Function GetChildren (Oconn,fid)
strSQL = "Select id,name from Catalog where fatherid=" &fid
Set rscatalog = Oconn.execute (strSQL)
%>
<UL>
<%
Do as not rscatalog.eof
%>
<li><%=rscatalog ("Name")%>
<%
Loop
%>
</UL>
<%
Rscatalog.close
End Function
%>
Now let's look at how to show all the classifications under the FID. This requires a recursive algorithm. All we need to do is simply call all IDs in the GetChildren function: GetChildren (Oconn,catalog ("ID") is OK.
<%
REM oconn---Database connection, already open
REM FID-----Number of the current category
Function GetChildren (Oconn,fid)
strSQL = "Select Name from Catalog where fatherid=" &fid
Set rscatalog = Oconn.execute (strSQL)
%>
<UL>
<%
Do as not rscatalog.eof
%>
<li><%=rscatalog ("Name")%>
<%=getchildren (Oconn,catalog ("ID"))%>
<%
Loop
%>
</UL>
<%
Rscatalog.close
End Function
%>
The modified GetChildren can complete the task of displaying all subcategories of the FID classification. To show all the categories, just call it:
<%
REM strconn--the string to connect to the database, modify it as appropriate
set oconn = Server.CreateObject ("ADODB. Connection ")
oConn.Open strconn
=getchildren (oconn,-1)
Oconn.close
%>
How to find all products of a category;
Now to address the fourth question we raised earlier. The third question is left as an exercise. We assume that the data table for the product is defined as follows:
Create Table Product (
[ID] [int] not NULL,
[Name] [Nvchar] Not NULL,
[Fatherid] [INT] Not NULL
);
Where the ID is the product number, name is the product, and the Fatherid is the category to which the product belongs.
For the fourth question, it's easy to think of a way to find all the subclasses of this taxonomy, and then query all the products under all subclasses. Implementing this algorithm is actually very complex. The code is roughly as follows:
<%
Function Getallid (Oconn,fid)
Dim strtemp
If Fid=-1 Then
strtemp = ""
Else
strtemp = ","
End If
strSQL = "Select Name from Catalog where fatherid=" &fid
Set rscatalog = Oconn.execute (strSQL)
Do as not rscatalog.eof
Strtemp=strtemp&rscatalog ("id") &getallid (Oconn,catalog ("id")) REM recursive call
Loop
Rscatalog.close
Getallid = strtemp
End Function
REM strconn--the string to connect to the database, modify it as appropriate
set oconn = Server.CreateObject ("ADODB. Connection ")
oConn.Open strconn
FID = Request.QueryString ("FID")
strSQL = "SELECT top * from Product where Fatherid in (" &getallid (Oconn,fid) & ")"
Set Rsproduct=oconn.execute (strSQL)
%>
<ul><%
Do as not rsproduct.eof
%>
<li><%=rsproduct ("Name")%>
<%
Lo
