CN101609473A - A kind of method of Structured Query Language (SQL) of reconstruct report query and device - Google Patents
A kind of method of Structured Query Language (SQL) of reconstruct report query and device Download PDFInfo
- Publication number
- CN101609473A CN101609473A CNA2009101636032A CN200910163603A CN101609473A CN 101609473 A CN101609473 A CN 101609473A CN A2009101636032 A CNA2009101636032 A CN A2009101636032A CN 200910163603 A CN200910163603 A CN 200910163603A CN 101609473 A CN101609473 A CN 101609473A
- Authority
- CN
- China
- Prior art keywords
- sql
- query language
- tables
- data
- relational tree
- 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
Landscapes
- Information Retrieval, Db Structures And Fs Structures Therefor (AREA)
Abstract
The invention provides a kind of method and device of Structured Query Language (SQL) of reconstruct report query, described method comprises: at the initial configuration query language SQL of report query, generate first relational tree between each tables of data among this Structured Query Language (SQL) SQL, inquiry row and condition row; According to described first relational tree, inquiry row and condition column-generation second relational tree; Generate syndeton query language SQL according to described second relational tree; Coupling part among the described initial configuration query language SQL is replaced with described syndeton query language SQL.The present invention has simplified loaded down with trivial details performance history greatly, and has realized the connection of dynamic adjusting data table, has guaranteed the superperformance of Structured Query Language (SQL) SQL.
Description
Technical field
The present invention relates to data processing field, particularly relate to a kind of method and device of Structured Query Language (SQL) of reconstruct report query.
Background technology
Enterprise resource planning (Enterprise Resource Planning, ERP), be meant and be based upon on the infotech basis, all resources (logistics, cash flow, information flow or human resources) to enterprise are integrated integrated management, adopt information-based means to realize enterprise's supply and marketing chain management, thereby reach the purpose that each link on the supply chain is realized scientific management.(Structured Query Language is a kind of data base querying and programming language SQL) to Structured Query Language (SQL), is used for access data and inquiry, renewal and administrative relationships Database Systems.In ERP system, in the time of need carrying out report query, often need to analyze according to various filterconditions, and according to filtercondition structural texture query language SQL.
In the prior art, when carrying out report query in practice, because when analyzing according to filtercondition, filtercondition may relate to various aspects, and, in order to be implemented in the form analysis to various dimensions, for example: according to employee's structure analysis that employee's academic title, official rank, education landscape etc. carry out, this just needs dynamically to change content of inquiry.Therefore, for quick exploitation, generally speaking tables of data all in the current system is stitched together, then the time according to the filtercondition generating structure query language SQL of report query, be directly risk comprise the large and complete Structured Query Language (SQL) SQL of the tables of data that might relate to, if need to carry out certain dimension analysis in the reality, directly filter and get final product with this dimension.
From said process as can be seen, during generating structure query language SQL because comprised the tables of data that might relate to, causing most tables of data to connect like this there is no need, even so just cause simple report query, its process of filtering according to filtercondition also can relate to the traversal of more invalid data table, have a strong impact on the performance of Structured Query Language (SQL) SQL, can not satisfy user's requirement.
Therefore, need the urgent technical matters that solves of those skilled in the art to be exactly at present: how can propose a kind of method of Structured Query Language (SQL) of reconstruct report query, in order to solve the not high problem of Structured Query Language (SQL) SQL performance in the prior art.
Summary of the invention
Technical matters to be solved by this invention provides a kind of method of Structured Query Language (SQL) of reconstruct report query, in order to solve the not high problem of performance of Structured Query Language (SQL) SQL in the prior art, promotes Structured Query Language (SQL) SQL performance.
Another object of the present invention is that above-mentioned design is applied to provide a kind of device of Structured Query Language (SQL) of reconstruct report query in the concrete applied environment, thereby guarantees the realization and the application of this method.
For solving the problems of the technologies described above, the embodiment of the invention provides a kind of method of Structured Query Language (SQL) of reconstruct report query, comprising:
At the initial configuration query language SQL of report query, generate first relational tree between each tables of data among this Structured Query Language (SQL) SQL, inquiry row and condition row;
According to described first relational tree, inquiry row and condition column-generation second relational tree;
Generate syndeton query language SQL according to described second relational tree;
Coupling part among the described initial configuration query language SQL is replaced with described syndeton query language SQL.
Preferably, described first relational tree of described foundation, inquiry row and condition column-generation second relational tree specifically comprise:
Node with the tables of data in described inquiry row and the condition row is labeled as necessary node in described first relational tree;
According to described must node and ancestor node generate second relational tree.
Preferably, described second relational tree of described foundation generates syndeton query language SQL, specifically comprises:
Described second relational tree is carried out inorder traversal;
The link field generating structure query language SQL that the node that obtains according to traversal is stored.
Preferably, first relational tree among this Structured Query Language (SQL) of described generation SQL between each tables of data, inquiry row and condition row specifically comprise:
With the master data sheet of described initial configuration query language SQL root node as described first relational tree;
With described master data sheet direct correlation from the child node of tables of data as described root node;
With among the described initial configuration query language SQL except described master data sheet and the tables of data from the tables of data, be associated to described child node respectively as associated nodes;
The tables of data that comprises among SELECT, GROUP BY and the ORDER BY clause among the described Structured Query Language (SQL) SQL is listed as inquiry;
The tables of data that comprises among WHERE, HAVING and the ON clause among the described Structured Query Language (SQL) SQL is listed as condition.
Preferably, described coupling part among the described initial configuration query language SQL is replaced with after the described syndeton query language SQL, also comprises:
Initial configuration query language SQL after the described replacement is inquired about as the execution architecture query language SQL of report query.
The embodiment of the invention also provides a kind of device of Structured Query Language (SQL) of reconstruct report query, comprising:
First generation module is used for the initial configuration query language SQL at report query, generates first relational tree between each tables of data among this Structured Query Language (SQL) SQL, inquiry row and condition row;
Second generation module is used for according to described first relational tree, inquiry row and condition column-generation second relational tree;
The 3rd generates module, is used for generating syndeton query language SQL according to described second relational tree;
Replace module, be used for the coupling part of described initial configuration query language SQL is replaced with described syndeton query language SQL.
Preferably, described second generation module comprises:
The mark submodule is used for the node with the tables of data of described inquiry row and condition row, is labeled as necessary node in described first relational tree;
First generates submodule, be used for according to described must node and ancestor node generate second relational tree.
Preferably, the described the 3rd generates module, specifically comprises:
Spider module is used for described second relational tree is carried out inorder traversal;
Second generates submodule, the link field generating structure query language SQL that the node that is used for obtaining according to traversal is stored.
Preferably, described first generation module specifically comprises:
Produce the root node submodule, be used for the master data sheet of described initial configuration query language SQL root node as described first relational tree;
Produce the child node module, be used for described master data sheet direct correlation from the child node of tables of data as described root node;
Related submodule is used for described initial configuration query language SQL being associated to described child node except described master data sheet and the tables of data from the tables of data respectively as associated nodes;
Produce inquiry row submodule, the tables of data that is used for comprising among described Structured Query Language (SQL) SQL SELECT, GROUP BY and the ORDER BY clause is as the inquiry row;
Generation condition row submodule, the tables of data that is used for comprising among described Structured Query Language (SQL) SQL WHERE, HAVING and the ON clause is listed as condition.
Preferably, also comprise:
Enquiry module is used for the initial configuration query language SQL after the described replacement is inquired about as the execution architecture query language SQL of report query.
Compared with prior art, the present invention has the following advantages:
In the present embodiment, can generate first relational tree between each tables of data among this Structured Query Language (SQL) SQL, inquiry row and condition row at the initial configuration query language SQL of report query; According to described first relational tree, inquiry row and condition column-generation second relational tree; Generate syndeton query language SQL according to described second relational tree; Coupling part among the described initial configuration query language SQL is replaced with described syndeton query language SQL.The present invention is the process that the huge initial configuration query language SQL to report query is reconstructed, and according to filtercondition reconstruct Structured Query Language (SQL) SQL, guarantees that the final Structured Query Language (SQL) SQL that carries out is the most effective, the most succinct; Adopt method of the present invention, can so that the programmer in performance history at ordinary times, needn't be in order to improve performance, painstakingly remove hard coded particular structured query language SQL as the case may be, adjust the connection between the tables of data, only need directly be reconstructed initial configuration query language SQL according to method of the present invention and get final product; The present invention has simplified loaded down with trivial details performance history greatly, and has realized the connection of dynamic adjusting data table, has guaranteed the superperformance of Structured Query Language (SQL) SQL.
Description of drawings
In order to be illustrated more clearly in the embodiment of the invention or technical scheme of the prior art, to do to introduce simply to the accompanying drawing of required use in embodiment or the description of the Prior Art below, apparently, accompanying drawing in describing below only is some embodiments of the present invention, for those of ordinary skills, under the prerequisite of not paying creative work, can also obtain other accompanying drawing according to these accompanying drawings.
Fig. 1 is the process flow diagram of method embodiment 1 of the Structured Query Language (SQL) of a kind of reconstruct report query of the present invention;
Fig. 2 is the process flow diagram of method embodiment 2 of the Structured Query Language (SQL) of a kind of reconstruct report query of the present invention;
Fig. 3 is the structural representation of device embodiment 1 of the Structured Query Language (SQL) of a kind of reconstruct report query of the present invention;
Fig. 4 is the structural representation of device embodiment 2 of the Structured Query Language (SQL) of a kind of reconstruct report query of the present invention.
Embodiment
For above-mentioned purpose of the present invention, feature and advantage can be become apparent more, the present invention is further detailed explanation below in conjunction with the drawings and specific embodiments.
The present invention can be used in numerous general or special purpose calculation element environment or the configuration.For example: personal computer, server computer, handheld device or portable set, plate equipment, multiprocessor device, comprise distributed computing environment of above any device or equipment or the like.
The present invention can describe in the general context of the computer executable instructions of being carried out by computing machine, for example program module.Usually, program module comprises the routine carrying out particular task or realize particular abstract, program, object, assembly, data structure or the like.Also can in distributed computing environment, put into practice the present invention, in these distributed computing environment, by by communication network connected teleprocessing equipment execute the task.In distributed computing environment, program module can be arranged in the local and remote computer-readable storage medium that comprises memory device.
With reference to figure 1, show the process flow diagram of method embodiment 1 of the Structured Query Language (SQL) of a kind of reconstruct report query of the present invention, can may further comprise the steps:
Step 101:, generate first relational tree between each tables of data among this Structured Query Language (SQL) SQL, inquiry row and condition row at the initial configuration query language SQL of report query;
Wherein, described initial configuration query language SQL, it promptly is large and complete query statement according to user's filtercondition generation, comprise the tables of data that all relate to, by parsing to described initial configuration query language SQL, can generate related data structure, described data structure comprises three kinds: first relational tree between the tables of data, condition row and inquiry row; Described first relational tree is root node with the master data sheet, embodies the annexation between master data sheet and other tables of data; Described condition is classified the tables of data that relates among WHERE, the HAVING and ON clause among the initial configuration query language SQL as; The tables of data that relates among SELECT, the GROUP BY and ORDER BY clause among the initial configuration query language SQL is classified in described inquiry as;
Wherein,, therefore the described first relational tree buffer memory can also be got up, when follow-up operation execution architecture query language SQL statement, directly visit described first relational tree because when inquiries such as form, only there is the variation of inquiry row and condition row;
Step 102: according to described first relational tree, inquiry row and condition column-generation second relational tree;
All nodes of the tables of data that comprises in described inquiry row and the condition row, all be to select, therefore, all nodes with the tables of data that comprises in described inquiry row and the condition row, and ancestor node all is labeled as the node that must select in described first relational tree, then can generate second relational tree according to the described node of must selecting that marks;
Step 103: generate syndeton query language SQL according to described second relational tree;
Syndeton query language SQL according to described second relational tree generates optimizes than original initial configuration query language SQL, if initial configuration query language SQL is huger, then the space of You Huaing also can be very big;
Need to prove, in this step, when not being empty, can also change the outer connected mode in a left side of tables of data among the initial configuration query language SQL into inner connection mode according to the tables of data content in the condition row; Wherein, the implication that connects outside the described left side is that the data in the table of restriction concatenated key right-hand member must satisfy condition of contact, and whether the data that are not related in the table of left end satisfy condition of contact, all export the content in the left end table; Connecting in described is a kind of the most frequently used connection type, and interior connection inquiry is actually a kind of inquiry of any condition.When connecting in using,, then from these two tables, extract data and be combined into new record, that is to say that in interior connection inquiry, the tuple that only satisfies condition just can appear in result's relation if the relevant field of two tables satisfies condition of contact;
Step 104: the coupling part among the described initial configuration query language SQL is replaced with described syndeton query language SQL.
Coupling part among the described initial configuration query language SQL is replaced with described syndeton query language SQL, then can generate final execution architecture query language SQL, wherein, the annexation of each tables of data has been deleted unwanted tables of data annexation unlike so complicated among the initial configuration query language SQL among this execution architecture query language SQL in step 102 generation second relational tree.
Present embodiment can also comprise:
Step 105: the initial configuration query language SQL after the described replacement is inquired about as the execution architecture query language SQL of report query.
Then described execution SQL is carried out report query as the final structure query language SQL of report query, promptly be with the language of described execution SQL as the user accesses data storehouse, unique outlet as Structured Query Language (SQL) SQL execution, simultaneously, in the present embodiment switching function can also be set in addition, promptly be to be reconstructed and to control whether carrying out the Structured Query Language (SQL) sql like language.
In the present embodiment, the present invention is the process that the huge initial configuration query language SQL to report query is reconstructed, and according to filtercondition reconstruct Structured Query Language (SQL) SQL, guarantees that the final Structured Query Language (SQL) SQL that carries out is the most effective, the most succinct; Adopt method of the present invention, can so that the programmer in performance history at ordinary times, needn't be in order to improve performance, painstakingly remove hard coded particular structured query language SQL as the case may be, adjust the connection between the tables of data, only need directly be reconstructed initial configuration query language SQL according to method of the present invention and get final product; The present invention has simplified loaded down with trivial details performance history greatly, and has realized the connection of dynamic adjusting data table, has guaranteed the superperformance of Structured Query Language (SQL) SQL.
With reference to figure 2, show the process flow diagram of method embodiment 2 of the Structured Query Language (SQL) of a kind of reconstruct report query of the present invention, present embodiment can may further comprise the steps:
Step 201: at the initial configuration query language SQL of report query, with the master data sheet of described initial configuration query language SQL root node as described first relational tree;
In the present embodiment, suppose that initial SQL is as follows:
Select?A.ID?from?A
left?out?join?B?on?A.FieldB＝B.FieldA?and?B.Field＝‘b’
left?out?join?C?on?B.FieldC＝C.FieldB
inner?join?D?on?A.FieldD＝D.FieldA
where?D.Field＝‘d’
group?by?A.ID
having?B.IntField＞1
In the present embodiment, need coupling part (promptly being the center section of from and where) with this initial configuration query language SQL to generate first relational tree between the tables of data, with the master data sheet inquired about root node as first relational tree; It promptly is Table A;
Step 202: with described master data sheet direct correlation from the child node of tables of data as described root node;
Will with described master data sheet direct correlation from tables of data as child node, promptly be with tables of data A direct correlation from tables of data;
Step 203: with among the described initial configuration query language SQL except described master data sheet and the tables of data from the tables of data, be associated to described child node respectively as associated nodes;
Then with except master data sheet and from the tables of data remaining tables of data add on the corresponding associated nodes; Like this, finally produce first relational tree, can also make each tree node write down corresponding long codes, to make things convenient for the location of traversal of tree and node;
First relational tree that foregoing initial configuration query language SQL generates is as follows:
A(a)
|——B(a！b)(A.FieldB＝B.FieldA?and?B.Field＝‘b’)
| |——C(a！b！c)(B.FieldC＝C.FieldB)
|——D(a！d)(A.FieldD＝D.FieldA)
Step 204: the tables of data that comprises among SELECT, GROUP BY and the ORDER BY clause among the described Structured Query Language (SQL) SQL is listed as inquiry;
Obtain the tables of data that relates among SELECT, the GROUP BY and ORDER BY clause among the described initial configuration query language SQL, with the content of this tables of data content as the inquiry row; In this step, be recorded as { A} in the inquiry row;
Step 205: the tables of data that comprises among WHERE, HAVING and the ON clause among the described Structured Query Language (SQL) SQL is listed as condition;
Obtain the tables of data that relates among WHERE, the HAVING and ON clause among the described initial configuration query language SQL, with the content of this tables of data content as the inquiry row; Wherein need to remove the row that are used to connect among the ON clause, should remove such as the FieldB and the FieldA row of " A.FieldB=B.FieldA " in the aforementioned initial configuration query language SQL statement;
Step 206: the node with the tables of data in described inquiry row and the condition row is labeled as necessary node in described first relational tree;
In the present embodiment, then with the node of tables of data in described inquiry row and the condition row, regarding requisite node as, promptly is requisite tables of data among the execution architecture query language SQL; Specifically can do according to long codes, for example C table is necessary table, then according to the node at C table place long codes a! C, with long codes be a! C, a! The node of b, a all is labeled as must select node; For example according to where clause D.Field=' d ', comprise the D table in the condition row as can be known, the vertex ticks that then D should be shown the place is for selecting node;
Step 207: according to described must node and ancestor node generate second relational tree;
The father node of described necessary node, and the father node of father node, or the like, by that analogy, till getting root node, be referred to as ancestor node; Then according to described must node and ancestor node can generate second relational tree;
According to the example of front, second relational tree of generation specifically can be as follows:
A(a)
|——B(a！b)(A.FieldB＝B.FieldA?and?B.Field＝‘b’)
|——D(a！d)(A.FieldD＝D.FieldA)
Step 208: described second relational tree is carried out inorder traversal;
When generating syndeton query language SQL, can adopt the mode of inorder traversal to node wherein according to described second relational tree;
Step 209: the link field generating structure query language SQL that the node that obtains according to traversal is stored.
Carry out Structured Query Language (SQL) SQL splicing according to the link field of storing in each node, for example, wherein showing C can be removed, Table A can also be become interior connection with the connected mode of table B by being connected outside the left side simultaneously, the syndeton query language SQL that then generates in this step can be as follows:
A
inner?join?B?on?A.FieldB＝B.FieldA?and?B.Field＝‘b’
inner?join?D?on?A.FieldD＝D.FieldA
Step 210: the initial configuration query language SQL after the described replacement is inquired about as the execution architecture query language SQL of report query.
Need to prove, for aforesaid each method embodiment, for simple description, so it all is expressed as a series of combination of actions, but those skilled in the art should know, the present invention is not subjected to the restriction of described sequence of movement, because according to the present invention, some step can adopt other orders or carry out simultaneously.Secondly, those skilled in the art also should know, the embodiment described in the instructions all belongs to preferred embodiment, and related action and module might not be that the present invention is necessary.
With reference to figure 3, show the structured flowchart of device embodiment 1 of the Structured Query Language (SQL) of a kind of reconstruct report query of the present invention, can comprise with lower module:
Wherein, described initial configuration query language SQL, it promptly is large and complete query statement according to user's filtercondition generation, comprise the tables of data that all relate to, by parsing to described initial configuration query language SQL, can generate related data structure, described data structure comprises three kinds: first relational tree between the tables of data, condition row and inquiry row; Described first relational tree is root node with the master data sheet, embodies the annexation between master data sheet and other tables of data; Described condition is classified the tables of data that relates among WHERE, the HAVING and ON clause among the initial configuration query language SQL as; The tables of data that relates among SELECT, the GROUP BY and ORDER BY clause among the initial configuration query language SQL is classified in described inquiry as;
All nodes of the tables of data that comprises in described inquiry row and the condition row, all be to select, therefore, all nodes with the tables of data that comprises in described inquiry row and the condition row, and ancestor node all is labeled as the node that must select in described first relational tree, then can generate second relational tree according to the described node of must selecting that marks;
The 3rd generates module 303, is used for generating syndeton query language SQL according to described second relational tree;
Syndeton query language SQL according to described second relational tree generates optimizes than original initial configuration query language SQL, if initial configuration query language SQL is huger, then the space of You Huaing also can be very big;
Replace module 304, be used for the coupling part of described initial configuration query language SQL is replaced with described syndeton query language SQL.
Coupling part among the described initial configuration query language SQL is replaced with described syndeton query language SQL, then can generate final execution architecture query language SQL, wherein, the annexation of each tables of data has been deleted unwanted tables of data annexation unlike so complicated among the initial configuration query language SQL among this execution architecture query language SQL in step 102 generation second relational tree.
In the present embodiment, device of the present invention is the process that the huge initial configuration query language SQL to report query is reconstructed, according to filtercondition reconstruct Structured Query Language (SQL) SQL, guarantee that the final Structured Query Language (SQL) SQL that carries out is the most effective, the most succinct; Adopt device of the present invention, can so that the programmer in performance history at ordinary times, needn't be in order to improve performance, painstakingly remove hard coded particular structured query language SQL as the case may be, adjust the connection between the tables of data, only need directly be reconstructed initial configuration query language SQL according to method of the present invention and get final product; The present invention has simplified loaded down with trivial details performance history greatly, and has realized the connection of dynamic adjusting data table, has guaranteed the superperformance of Structured Query Language (SQL) SQL.
With reference to figure 4, show the structured flowchart of device embodiment 2 of the Structured Query Language (SQL) of a kind of reconstruct report query of the present invention, can comprise with lower module:
Produce root node submodule 401, be used for the master data sheet of described initial configuration query language SQL root node as described first relational tree;
Produce child node module 402, be used for described master data sheet direct correlation from the child node of tables of data as described root node;
Produce inquiry row submodule 404, the tables of data that is used for comprising among described Structured Query Language (SQL) SQL SELECT, GROUP BY and the ORDER BY clause is as the inquiry row;
Generation condition row submodule 405, the tables of data that is used for comprising among described Structured Query Language (SQL) SQL WHERE, HAVING and the ON clause is listed as condition.
Mark submodule 406 is used for the node with the tables of data of described inquiry row and condition row, is labeled as necessary node in described first relational tree;
First generates submodule 407, be used for according to described must node and ancestor node generate second relational tree.
Second generates submodule 409, the link field generating structure query language SQL that the node that is used for obtaining according to traversal is stored.
Replace module 410, be used for the coupling part of described initial configuration query language SQL is replaced with described syndeton query language SQL.
Need to prove that each embodiment in this instructions all adopts the mode of going forward one by one to describe, what each embodiment stressed all is and the difference of other embodiment that identical similar part is mutually referring to getting final product between each embodiment.For device class embodiment, because it is similar substantially to method embodiment, so description is fairly simple, relevant part gets final product referring to the part explanation of method embodiment.
Need to prove, in this article, relational terms such as first and second grades only is used for an entity or operation are made a distinction with another entity or operation, and not necessarily requires or hint and have the relation of any this reality or in proper order between these entities or the operation.And, term " comprises ", " comprising " or its any other variant are intended to contain comprising of nonexcludability, thereby make and comprise that process, method, article or the equipment of a series of key elements not only comprise those key elements, but also comprise other key elements of clearly not listing, or also be included as this process, method, article or equipment intrinsic key element.Do not having under the situation of more restrictions, the key element that limits by statement " comprising ... ", and be not precluded within process, method, article or the equipment that comprises described key element and also have other identical element.
More than the device of the Structured Query Language (SQL) of the method for the Structured Query Language (SQL) of a kind of reconstruct report query provided by the present invention and reconstruct report query is described in detail, used specific case herein principle of the present invention and embodiment are set forth, the explanation of above embodiment just is used for helping to understand method of the present invention and core concept thereof; Simultaneously, for one of ordinary skill in the art, according to thought of the present invention, the part that all can change in specific embodiments and applications, in sum, this description should not be construed as limitation of the present invention.
Claims (10)
1, a kind of method of Structured Query Language (SQL) of reconstruct report query is characterized in that, comprising:
At the initial configuration query language SQL of report query, generate first relational tree between each tables of data among this Structured Query Language (SQL) SQL, inquiry row and condition row;
According to described first relational tree, inquiry row and condition column-generation second relational tree;
Generate syndeton query language SQL according to described second relational tree;
Coupling part among the described initial configuration query language SQL is replaced with described syndeton query language SQL.
2, the method for claim 1 is characterized in that, described first relational tree of described foundation, inquiry row and condition column-generation second relational tree specifically comprise:
Node with the tables of data in described inquiry row and the condition row is labeled as necessary node in described first relational tree;
According to described must node and ancestor node generate second relational tree.
3, the method for claim 1 is characterized in that, described second relational tree of described foundation generates syndeton query language SQL, specifically comprises:
Described second relational tree is carried out inorder traversal;
The link field generating structure query language SQL that the node that obtains according to traversal is stored.
4, the method for claim 1 is characterized in that, first relational tree among this Structured Query Language (SQL) of described generation SQL between each tables of data, inquiry row and condition row specifically comprise:
With the master data sheet of described initial configuration query language SQL root node as described first relational tree;
With described master data sheet direct correlation from the child node of tables of data as described root node;
With among the described initial configuration query language SQL except described master data sheet and the tables of data from the tables of data, be associated to described child node respectively as associated nodes;
The tables of data that comprises among SELECT, GROUP BY and the ORDER BY clause among the described Structured Query Language (SQL) SQL is listed as inquiry;
The tables of data that comprises among WHERE, HAVING and the ON clause among the described Structured Query Language (SQL) SQL is listed as condition.
5, as any described method of claim 1-4, it is characterized in that, described coupling part among the described initial configuration query language SQL replaced with after the described syndeton query language SQL, also comprise:
Initial configuration query language SQL after the described replacement is inquired about as the execution architecture query language SQL of report query.
6, a kind of device of Structured Query Language (SQL) of reconstruct report query is characterized in that, comprising:
First generation module is used for the initial configuration query language SQL at report query, generates first relational tree between each tables of data among this Structured Query Language (SQL) SQL, inquiry row and condition row;
Second generation module is used for according to described first relational tree, inquiry row and condition column-generation second relational tree;
The 3rd generates module, is used for generating syndeton query language SQL according to described second relational tree;
Replace module, be used for the coupling part of described initial configuration query language SQL is replaced with described syndeton query language SQL.
7, device as claimed in claim 6 is characterized in that, described second generation module comprises:
The mark submodule is used for the node with the tables of data of described inquiry row and condition row, is labeled as necessary node in described first relational tree;
First generates submodule, be used for according to described must node and ancestor node generate second relational tree.
8, device as claimed in claim 6 is characterized in that, the described the 3rd generates module, specifically comprises:
Spider module is used for described second relational tree is carried out inorder traversal;
Second generates submodule, the link field generating structure query language SQL that the node that is used for obtaining according to traversal is stored.
9, device as claimed in claim 6 is characterized in that, described first generation module specifically comprises:
Produce the root node submodule, be used for the master data sheet of described initial configuration query language SQL root node as described first relational tree;
Produce the child node module, be used for described master data sheet direct correlation from the child node of tables of data as described root node;
Related submodule is used for described initial configuration query language SQL being associated to described child node except described master data sheet and the tables of data from the tables of data respectively as associated nodes;
Produce inquiry row submodule, the tables of data that is used for comprising among described Structured Query Language (SQL) SQL SELECT, GROUP BY and the ORDER BY clause is as the inquiry row;
Generation condition row submodule, the tables of data that is used for comprising among described Structured Query Language (SQL) SQL WHERE, HAVING and the ON clause is listed as condition.
10, device as claimed in claim 6 is characterized in that, also comprises:
Enquiry module is used for the initial configuration query language SQL after the described replacement is inquired about as the execution architecture query language SQL of report query.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN2009101636032A CN101609473B (en) | 2009-07-30 | 2009-07-30 | Method and device for reconstructing structured query language of report inquiry |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN2009101636032A CN101609473B (en) | 2009-07-30 | 2009-07-30 | Method and device for reconstructing structured query language of report inquiry |
Publications (2)
| Publication Number | Publication Date |
|---|---|
| CN101609473A true CN101609473A (en) | 2009-12-23 |
| CN101609473B CN101609473B (en) | 2012-07-25 |
Family
ID=41483227
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN2009101636032A Active CN101609473B (en) | 2009-07-30 | 2009-07-30 | Method and device for reconstructing structured query language of report inquiry |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN101609473B (en) |
Cited By (10)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN101833580A (en) * | 2010-05-12 | 2010-09-15 | 中兴通讯股份有限公司 | Report inquiring system and data acquisition method and device thereof |
| CN103902653A (en) * | 2014-02-28 | 2014-07-02 | 珠海多玩信息技术有限公司 | Method and device for creating data warehouse table blood relationship graph |
| CN104008191A (en) * | 2014-06-12 | 2014-08-27 | 北京京东尚科信息技术有限公司 | Data inquiring method |
| CN104036040A (en) * | 2014-06-30 | 2014-09-10 | 贝壳网际（北京）安全技术有限公司 | Method and device for generating report |
| CN104714974A (en) * | 2013-12-17 | 2015-06-17 | 航天信息股份有限公司 | Method and device for parsing and reprocessing query statement |
| CN106933893A (en) * | 2015-12-31 | 2017-07-07 | 北京国双科技有限公司 | The querying method and device of multi-dimensional data |
| CN106933894A (en) * | 2015-12-31 | 2017-07-07 | 北京国双科技有限公司 | The querying method and device of multi-dimensional data |
| CN106933909A (en) * | 2015-12-31 | 2017-07-07 | 北京国双科技有限公司 | The querying method and device of multi-dimensional data |
| CN110874364A (en) * | 2019-11-19 | 2020-03-10 | 北京启迪区块链科技发展有限公司 | Query statement processing method, device, equipment and storage medium |
| CN110990423A (en) * | 2019-12-12 | 2020-04-10 | 上海达梦数据库有限公司 | SQL statement execution method, device, equipment and storage medium |
-
2009
- 2009-07-30 CN CN2009101636032A patent/CN101609473B/en active Active
Cited By (16)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN101833580A (en) * | 2010-05-12 | 2010-09-15 | 中兴通讯股份有限公司 | Report inquiring system and data acquisition method and device thereof |
| WO2011140759A1 (en) * | 2010-05-12 | 2011-11-17 | 中兴通讯股份有限公司 | Report form querying system and method and device for collecting data thereof |
| CN104714974A (en) * | 2013-12-17 | 2015-06-17 | 航天信息股份有限公司 | Method and device for parsing and reprocessing query statement |
| CN103902653A (en) * | 2014-02-28 | 2014-07-02 | 珠海多玩信息技术有限公司 | Method and device for creating data warehouse table blood relationship graph |
| CN103902653B (en) * | 2014-02-28 | 2017-08-01 | 珠海多玩信息技术有限公司 | A kind of method and apparatus for building data warehouse table genetic connection figure |
| CN104008191A (en) * | 2014-06-12 | 2014-08-27 | 北京京东尚科信息技术有限公司 | Data inquiring method |
| CN104036040A (en) * | 2014-06-30 | 2014-09-10 | 贝壳网际（北京）安全技术有限公司 | Method and device for generating report |
| CN104036040B (en) * | 2014-06-30 | 2017-11-03 | 北京猎豹移动科技有限公司 | Report form generation method and device |
| CN106933909A (en) * | 2015-12-31 | 2017-07-07 | 北京国双科技有限公司 | The querying method and device of multi-dimensional data |
| CN106933894A (en) * | 2015-12-31 | 2017-07-07 | 北京国双科技有限公司 | The querying method and device of multi-dimensional data |
| CN106933893A (en) * | 2015-12-31 | 2017-07-07 | 北京国双科技有限公司 | The querying method and device of multi-dimensional data |
| CN106933893B (en) * | 2015-12-31 | 2019-12-10 | 北京国双科技有限公司 | multi-dimensional data query method and device |
| CN110874364A (en) * | 2019-11-19 | 2020-03-10 | 北京启迪区块链科技发展有限公司 | Query statement processing method, device, equipment and storage medium |
| CN110874364B (en) * | 2019-11-19 | 2023-04-11 | 北京启迪区块链科技发展有限公司 | Query statement processing method, device, equipment and storage medium |
| CN110990423A (en) * | 2019-12-12 | 2020-04-10 | 上海达梦数据库有限公司 | SQL statement execution method, device, equipment and storage medium |
| CN110990423B (en) * | 2019-12-12 | 2023-04-18 | 上海达梦数据库有限公司 | SQL statement execution method, device, equipment and storage medium |
Also Published As
| Publication number | Publication date |
|---|---|
| CN101609473B (en) | 2012-07-25 |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| CN101609473B (en) | Method and device for reconstructing structured query language of report inquiry | |
| JP7273045B2 (en) | Dimensional Context Propagation Techniques for Optimizing SQL Query Plans | |
| US9800675B2 (en) | Methods for dynamically generating an application interface for a modeled entity and devices thereof | |
| Dourish | No SQL: The shifting materialities of database technology | |
| Holzschuher et al. | Querying a graph database–language selection and performance considerations | |
| Simitsis | Modeling and managing ETL processes. | |
| Gui et al. | IFC-based partial data model retrieval for distributed collaborative design | |
| Gómez et al. | TemporalEMF: A temporal metamodeling framework | |
| US10409790B2 (en) | Data retention rule generator | |
| El Beggar et al. | DAREF: MDA framework for modelling data warehouse requirements and deducing the multidimensional schema | |
| Bender et al. | A proposal for future data organization in enterprise systems—an analysis of established database approaches | |
| Cao et al. | Support mechanisms for cloud configuration using XML filtering techniques: A case study in SaaS | |
| US20140136257A1 (en) | In-memory analysis scenario builder | |
| Naeem et al. | An ontology based digital preservation system for enterprise collaboration | |
| Sivaganesh et al. | Optimization of ETL work flow in data warehouse | |
| CN101159049A (en) | Dynamic configuring arrange method and system | |
| Regardt et al. | Anchor Modeling: An Agile Modeling Technique Using the Sixth Normal Form for Structurally and Temporally Evolving Data | |
| CN105518672A (en) | Data retrieval across multiple models | |
| Du et al. | A schema aware ETL workflow generator | |
| Durkin et al. | SQL Server 2017 Developer’s Guide: A professional guide to designing and developing enterprise database applications | |
| Brighen et al. | An economical query cost model in the cloud | |
| Yaman et al. | LinkedDataOps: quality oriented end-to-end geospatial linked data production governance | |
| CN105518670A (en) | Data model indexing for model queries | |
| Ma et al. | Using meta-structures in database design | |
| US11847118B1 (en) | Query set optimization in a data analytics pipeline |
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 |