CN101477555A - Fast retrieval and generation display method for task tree based on SQL database - Google Patents
Fast retrieval and generation display method for task tree based on SQL database Download PDFInfo
- Publication number
- CN101477555A CN101477555A CNA2009100281080A CN200910028108A CN101477555A CN 101477555 A CN101477555 A CN 101477555A CN A2009100281080 A CNA2009100281080 A CN A2009100281080A CN 200910028108 A CN200910028108 A CN 200910028108A CN 101477555 A CN101477555 A CN 101477555A
- Authority
- CN
- China
- Prior art keywords
- task
- node
- sql
- tree
- ordering
- Prior art date
- Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
- Granted
Links
Images
Abstract
The invention discloses a method for fast retrieving, generating and displaying a task tree based on an SQL database. The method is characterized in that the number of task nodes of the task tree is converted into information capable of ordering for SQL, particularly, the numerical value weighting process and the code conversion are performed to each group of number for the number of task nodes, so as to form ordering numerical values through which the database can judge the size, ordering of the task nodes is performed through SQL language according to the obtained ordering numerical values, so as to realize retrieval of the task tree, and the further generating display is performed. Wherein, the numerical value weighting process adopts the way that each group of number for the number of the task nodes is multiplied by weighting factors to achieve the weighting process; the code conversion adopts the way that each group of number for the number of task nodes after the weighting process is connected in the form of character strings in sequence in the number of task nodes, and final format conversion values or ordering numerical values of the number of task nodes are obtained.
Description
Technical field
The present invention relates to Structured Query Language (SQL) SQL (Structured Query Language) retrieval technique, be specially the quick retrieval of a kind of task tree based on SQL database and generate the method that shows.
Background technology
In sql like language, provide a large amount of inquiry, sort methods.Foundation for ordering in the sql like language is that certain requirement is arranged, as simple numerical ordering, according to numerical values recited; The character ordering is according to its ASCII character size ordering; And time-sequencing, according to the time order and function order.
The statement of same realization purpose, different combinations, it is carried out efficient and differs greatly.In the actual utilization, if the sort by that the needs default sort rule of accord with SQL not, but its ordering rule that can't discern is generally handled this problem by following several modes:
1) data is extracted from database, in internal memory, judge ordering, draw ranking results according to specific regular program;
2) key word with ordering carries out format conversion, is converted to the ordering form that sql like language can be discerned, and the ranking function of utilizing sql like language again is at the data sorting after changing.
System is in handling above-mentioned sequencer procedure, and the implementation method of ordering is an one critical function, realizes that the speed of ordering also is an important performance reference target.Because in interactively mode of operation, the far-end operation person has certain tolerance for the response time of system.If the long-time not response of operation, system value can't be approved.Should in the system performance design, take into full account this demand.
In the project management, set the distribution situation that can understand task by the task of browsing project, being distributed on the logical topology of task is the structure of a binary tree, as Fig. 1.Because assigning of task be unordered in time, thereby the position that is kept in the database also is unordered arrangement, as table 1:
Table 1
Sequence number node number task names task definition is assigned start time time plan concluding time physical end time state
1 1.1 task A 2008-1-1
2 1.3 task B 2008-1-4
3 2.1 task C 2008-1-4
4 3.1 task D 2008-1-5
5 2.2 task E 2008-1-9
6 1.1.1 task F 2008-1-10
Unique foundation that can sort to task node is the node serial number of task, as: 1.1.1,2.1 etc.Multistage task node numbering is a required attribute in the task tree, but because be in the SQL statement to numerical value, time conventional among the SQL, or simply sorting operation is carried out in character combination, and the character of task node numbering form is relatively, SQL does not support at present.Therefore conventional SQL sorting operation can't obtain desired data at short notice.
The data of how unordered database space being preserved are output as orderly data, are the problems that the retrieval tasks tree will be considered in the project management system.In result's while of exporting, owing in program, need judgement, calculating, arrangement, processing to need the plenty of time, so the response time of system also should be considered.Therefore how fast and effectively the task node in the task tree being sorted, is the problem that needs reality to face in the design.
Summary of the invention
The problem to be solved in the present invention is: sql like language can not be retrieved fast to its ordering rule that can't discern, especially at the tree of the task in the project management, needs the method for task node information in the quick searching database table of a kind of energy.
Technical scheme of the present invention is: based on the quick retrieval of the task of SQL database tree and generate the method that shows, the task node numbering of task tree is converted to the information that SQL can sort, be specially each group numeral of task node numbering is carried out the numerical value weighted, carry out code conversion again, form database and can differentiate the ordering numerical value of size, the ordering numerical value according to obtaining sorts to task node by sql like language, realization is to the retrieval of task tree, and further generation shows; Wherein the numerical value weighted is: determine weighting factor f according to the maximum son node number that task is set under each node, weighting factor f is 10
nIn greater than the minimum value of son node number, each group numeral of task node numbering be multiply by weighting factor, finish weighted; Code conversion is: with each group numeral of the numbering of the task node after the weighted, carry out the connection of character string by its order in the task node serial number, obtain the Final Format conversion value of task node numbering, numerical value promptly sorts.
Quick retrieval of task of the present invention tree and generation are presented in the system based on browser and server B/S framework to be carried out, and is convenient to operated from a distance, and SQL database is arranged on server end, and concrete steps are:
Step 1, the maximum node number under the layering of node that sets the tasks is determined the size of weighting factor f;
Step 2, the maximum number of plies of the tree that sets the tasks is determined the conversion value zero padding number of node level less than the maximum layer of node;
Step 3, carry out from task node number the ordering numerical value format conversion, format conversion is carried out at server end, the ordering numerical value that obtains deposits SQL database in, by sql like language ordering numerical value is differentiated size, realize ordering, obtain arranging orderly data set task node;
Step 4, after the browser of client obtains to arrange orderly data set, existing sequence according to data set presents net result by the Javascript language by tree-like structure, can reflect the distribution situation of current task tree, and the generation of realization task tree shows.
For avoiding server end with the frequent data interaction between the SQL database, in SQL database, utilize view to realize format conversion, database manipulation function by Java, the task node numbering is read out one by one, carry out format conversion, be inserted into again in the temporary table space of interim generation, at last temporary table is carried out the SQL data retrieval, sort according to the data that generate,, be and arrange orderly data set result's output.
The non-ordered data that can't directly sort with sql like language that the present invention can be fast, effectively database space is preserved is output as orderly data, retrieval of task tree and generation at project management shows especially, be shown as example with a normal task tree, the task node number is 1000, original query time is about 1 minute, and after employing the inventive method, query time shortens to 1～2 second, thereby realizes fast worktodo tree search function.
Description of drawings
Fig. 1 is project task tree synoptic diagram.
Embodiment
The form of task node numbering can't be discerned its magnitude relationship for the SQL basic statement.The inquiry ordering of SQL statement is to have utilized the size of the key word of ordering to sort, thereby enumerates the SQL query result.What need solution is how the task node numbering to be converted to the information that SQL can sort.
The present invention is converted to the information that SQL can sort with the task node numbering of task tree, be specially each group numeral of task node numbering is carried out the numerical value weighted, carry out code conversion again, form database and can differentiate the ordering numerical value of size, according to the ordering numerical value that obtains, by sql like language task node is sorted, realize retrieval, and further generate demonstration the task tree; Wherein the numerical value weighted is: determine weighting factor f according to the maximum son node number that task is set under each node, weighting factor f is 10
nIn greater than the minimum value of son node number, each group numeral of task node numbering be multiply by weighting factor, finish weighted; Code conversion is: the maximum number of plies of determining the current task tree, determine node zero padding disposal route, with each group numeral of the numbering of the task node after the weighted, carry out the connection of character string by its order in the task node serial number, obtain the Final Format conversion value of task node numbering, numerical value promptly sorts.
Weighting factor f is 10
nIn a value, if maximum son node number is 400 under the individual node of task tree, so, 10
2＜400＜10
3, the value of f is 1000; Maximum son node number is 99 under the individual node of task tree, so, and 10
1＜99＜10
2, the value of f is 100.
Determine the maximum number of plies of current task tree, determine that node conversion zero padding disposal route is meant, as: node serial number is a.b.c, this node layer is 3, and the maximum layer of task tree is 4, and the form that can represent is a.b.c.d, this moment, we thought that consideration at node a.b.c is need be according to 4 layers node processing, the three first layers value is according to the normal handling method, and last demand is handled according to zero padding, and the figure place of zero padding is a weighting factor f figure place.
If node serial number is a.b.c, the maximum layer of task tree is 4, carries out numerical value weighted and code conversion:
Value＝a*f+b*f+c*f+f*0
Value is final format conversion value, the numerical value that promptly sorts, and what plus sige was represented in formula is the connection of character string.For example maximum son node number is 450 under the individual node of task tree, then the value of f is 1000, the maximum layer of task tree is 4, task node is numbered 1.1.1, then be divided into 1,1,1 three group of numeral, ordering numerical value Value=1*1000+1*1000+1*1000+1000*0=1000100010000000, in like manner task node is numbered 2.1, ordering numerical value Value=2*1000+1*1000+0*1000+0*1000=2000100000000000, SQL database is carried out retrieval ordering to ordering numerical value, realization is to the orderly output of project management task tree, as table 2:
Table 2
Sequence number node number task names task definition is assigned start time time plan concluding time physical end time state
1 1.1 task A 2008-1-1
6 1.1.1 task F 2008-1-10
2 1.3 task B 2008-1-4
3 2.1 task C 2008-1-4
5 2.2 task E 2008-1-9
4 3.1 task D 2008-1-5
Compare with table 1, effectively realized task node being sorted by the task node numbering.
During specific implementation of the present invention, quick retrieval of task tree and generation are presented in the system based on browser and server B/S framework to be carried out, and is convenient to operated from a distance, and SQL database is arranged on server end, and concrete steps are:
Step 1, maximum node number under layering of node sets the tasks, determine the size of weighting factor f, for example, according to daily task statistics, according to the expression of this task node form of a.b.c, a, b, the span of these three numerical value of c is more than or equal to 0, smaller or equal to 1000, then determine a task tree down the maximum node tree of one deck be 1000;
Step 2, the maximum number of plies of the tree that sets the tasks, by determining the clear and definite value of maximum layer space, for example the node form of a.b.c represents that the task node number of plies is 3 layers;
Step 3, carry out numbering the format conversion of ordering numerical value from task node, because system is the design architecture of B/S, format conversion is carried out at server end, the ordering numerical value that obtains deposits SQL database in, by sql like language ordering numerical value is differentiated size, realize ordering, obtain arranging orderly data set task node;
Step 4, after the browser of client obtains to arrange orderly data set, existing sequence according to data set presents net result by the Javascript language by tree-like structure, can reflect the distribution situation of current task tree, and the generation of realization task tree shows.
For avoiding server end with the frequent data interaction between the SQL database, in SQL database, utilize view to realize format conversion, database manipulation function by Java, the task node numbering is read out one by one, carry out format conversion, be inserted into again in the temporary table space of interim generation, at last temporary table is carried out the SQL data retrieval, sort according to the data that generate,, be and arrange orderly data set result's output.
Claims (3)
1, based on the quick retrieval of the task of SQL database tree and generate the method that shows, it is characterized in that the task node numbering of task tree is converted to the information that SQL can sort, be specially each group numeral of task node numbering is carried out the numerical value weighted, carry out code conversion again, form database and can differentiate the ordering numerical value of size, the ordering numerical value according to obtaining sorts to task node by sql like language, realization is to the retrieval of task tree, and further generation shows; Wherein the numerical value weighted is: determine weighting factor f according to the maximum son node number that task is set under each node, weighting factor f is 10
nIn greater than the minimum value of son node number, each group numeral of task node numbering be multiply by weighting factor, finish weighted; Code conversion is: with each group numeral of the numbering of the task node after the weighted, carry out the connection of character string by its order in the task node serial number, obtain the Final Format conversion value of task node numbering, numerical value promptly sorts.
2, the quick retrieval of the task tree based on SQL database according to claim 1 and generate the method that shows, it is characterized in that the quick retrieval of task tree and generation are presented in the system based on browser and server B/S framework carries out, SQL database is arranged on server end, and concrete steps are:
Step 1, the maximum node number under the layering of node that sets the tasks is determined the size of weighting factor f;
Step 2, the maximum number of plies of the tree that sets the tasks is determined the conversion value zero padding number of node level less than the maximum layer of node;
Step 3, carry out from task node number the ordering numerical value format conversion, format conversion is carried out at server end, the ordering numerical value that obtains deposits SQL database in, by sql like language ordering numerical value is differentiated size, realize ordering, obtain arranging orderly data set task node;
Step 4, after the browser of client obtains to arrange orderly data set, existing sequence according to data set presents net result by the Javascript language by tree-like structure, can reflect the distribution situation of current task tree, and the generation of realization task tree shows.
3, the quick retrieval of the task tree based on SQL database according to claim 2 and generate the method that shows, it is characterized in that in SQL database, utilizing the SQL statement of writing according to the implementation method of describing in the claim 2, generate the SQL view, thereby realize format conversion.Database manipulation function by Java, the task node numbering is read out one by one, carry out format conversion, be inserted into again in the temporary table space of interim generation, at last temporary table is carried out the SQL data retrieval, sort according to the data that generate,, be and arrange orderly data set result's output.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN2009100281080A CN101477555B (en) | 2009-01-09 | 2009-01-09 | Fast retrieval and generation display method for task tree based on SQL database |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN2009100281080A CN101477555B (en) | 2009-01-09 | 2009-01-09 | Fast retrieval and generation display method for task tree based on SQL database |
Publications (2)
| Publication Number | Publication Date |
|---|---|
| CN101477555A true CN101477555A (en) | 2009-07-08 |
| CN101477555B CN101477555B (en) | 2010-09-08 |
Family
ID=40838271
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN2009100281080A Expired - Fee Related CN101477555B (en) | 2009-01-09 | 2009-01-09 | Fast retrieval and generation display method for task tree based on SQL database |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN101477555B (en) |
Cited By (8)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN102063329A (en) * | 2010-12-22 | 2011-05-18 | 北京邮电大学 | Multi-agent coordination based management task dynamic decomposition method and system |
| CN102332030A (en) * | 2011-10-17 | 2012-01-25 | 中国科学院计算技术研究所 | Data storing, managing and inquiring method and system for distributed key-value storage system |
| CN102841891A (en) * | 2011-06-21 | 2012-12-26 | 金蝶软件（中国）有限公司 | Method and device for ordering tree structure nodes, and enquiry system |
| CN102867059A (en) * | 2012-09-19 | 2013-01-09 | 浪潮(北京)电子信息产业有限公司 | Method and system for processing data in treelike structures |
| CN103699648A (en) * | 2013-12-26 | 2014-04-02 | 成都市卓睿科技有限公司 | Tree-form data structure used for quick retrieval and implementation method of tree-form data structure |
| CN107194608A (en) * | 2017-06-13 | 2017-09-22 | 复旦大学 | A kind of mass-rent towards disabled person community marks Task Assigned Policy |
| CN110928667A (en) * | 2019-12-06 | 2020-03-27 | 北京明略软件系统有限公司 | Task scheduling method and device, electronic equipment and storage medium |
| CN113378008A (en) * | 2021-04-29 | 2021-09-10 | 上海工程技术大学 | Operation withdrawing method supporting tree model |
-
2009
- 2009-01-09 CN CN2009100281080A patent/CN101477555B/en not_active Expired - Fee Related
Cited By (13)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN102063329B (en) * | 2010-12-22 | 2012-12-12 | 北京邮电大学 | Multi-agent coordination based management task dynamic decomposition method and system |
| CN102063329A (en) * | 2010-12-22 | 2011-05-18 | 北京邮电大学 | Multi-agent coordination based management task dynamic decomposition method and system |
| CN102841891B (en) * | 2011-06-21 | 2017-02-15 | 金蝶软件（中国）有限公司 | Method and device for ordering tree structure nodes, and enquiry system |
| CN102841891A (en) * | 2011-06-21 | 2012-12-26 | 金蝶软件（中国）有限公司 | Method and device for ordering tree structure nodes, and enquiry system |
| CN102332030A (en) * | 2011-10-17 | 2012-01-25 | 中国科学院计算技术研究所 | Data storing, managing and inquiring method and system for distributed key-value storage system |
| CN102867059A (en) * | 2012-09-19 | 2013-01-09 | 浪潮(北京)电子信息产业有限公司 | Method and system for processing data in treelike structures |
| CN103699648A (en) * | 2013-12-26 | 2014-04-02 | 成都市卓睿科技有限公司 | Tree-form data structure used for quick retrieval and implementation method of tree-form data structure |
| CN107194608A (en) * | 2017-06-13 | 2017-09-22 | 复旦大学 | A kind of mass-rent towards disabled person community marks Task Assigned Policy |
| CN107194608B (en) * | 2017-06-13 | 2021-09-17 | 复旦大学 | Crowd-sourcing labeling task allocation method for disabled person community |
| CN110928667A (en) * | 2019-12-06 | 2020-03-27 | 北京明略软件系统有限公司 | Task scheduling method and device, electronic equipment and storage medium |
| CN110928667B (en) * | 2019-12-06 | 2022-03-01 | 北京明略软件系统有限公司 | Task scheduling method and device, electronic equipment and storage medium |
| CN113378008A (en) * | 2021-04-29 | 2021-09-10 | 上海工程技术大学 | Operation withdrawing method supporting tree model |
| CN113378008B (en) * | 2021-04-29 | 2022-10-11 | 上海工程技术大学 | Operation withdrawing method supporting tree model |
Also Published As
| Publication number | Publication date |
|---|---|
| CN101477555B (en) | 2010-09-08 |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| CN101477555B (en) | Fast retrieval and generation display method for task tree based on SQL database | |
| CN104375992B (en) | A kind of method and apparatus of address matching | |
| US6738759B1 (en) | System and method for performing similarity searching using pointer optimization | |
| CN102541529B (en) | A kind of query page generating device and method | |
| US6618727B1 (en) | System and method for performing similarity searching | |
| CN104021198B (en) | The relational database information search method and device indexed based on Ontology | |
| US5933796A (en) | Data extracting system based on characteristic quantities of data distribution | |
| CN101136027B (en) | System and method for database indexing, searching and data retrieval | |
| CN103177094B (en) | Cleaning method of data of internet of things | |
| CN111506621B (en) | Data statistical method and device | |
| CN107943952A (en) | A kind of implementation method that full-text search is carried out based on Spark frames | |
| CN115391495B (en) | Method, device and equipment for searching keywords in Chinese context | |
| CN116775661A (en) | Big space data storage and management method based on Beidou grid technology | |
| CN101957860A (en) | Method and device for releasing and searching information | |
| CN104281578A (en) | Region marking method and device for data file | |
| CN106484815B (en) | A kind of automatic identification optimization method based on mass data class SQL retrieval scene | |
| CN106127863B (en) | A kind of refrigerator foaming die series design system and its method | |
| CN111984673B (en) | Fuzzy retrieval method and device for tree structure of power grid electric energy metering system | |
| CN104102694A (en) | Tree node sorting method and tree node sorting device | |
| CN103500222A (en) | Method and device for searching for chat object through communication software | |
| CN103678513A (en) | Interactive search generation method and system | |
| CN103365960A (en) | Off-line searching method of structured data of electric power multistage dispatching management | |
| CN103455551B (en) | Working days data processing method based on performance resource and device | |
| JP2007535009A (en) | A data structure and management system for a superset of relational databases. | |
| CN101297290B (en) | Method for controlling relational database system |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| C06 | Publication | ||
| PB01 | Publication | ||
| C10 | Entry into substantive examination | ||
| SE01 | Entry into force of request for substantive examination | ||
| C14 | Grant of patent or utility model | ||
| GR01 | Patent grant | ||
| CF01 | Termination of patent right due to non-payment of annual fee | ||
| CF01 | Termination of patent right due to non-payment of annual fee |
Granted publication date: 20100908 Termination date: 20220109 |