CN113535756A - Data query method, device, equipment and storage medium - Google Patents

Data query method, device, equipment and storage medium Download PDF

Info

Publication number
CN113535756A
CN113535756A CN202110873375.9A CN202110873375A CN113535756A CN 113535756 A CN113535756 A CN 113535756A CN 202110873375 A CN202110873375 A CN 202110873375A CN 113535756 A CN113535756 A CN 113535756A
Authority
CN
China
Prior art keywords
data table
data
query
query statement
condition
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
Application number
CN202110873375.9A
Other languages
Chinese (zh)
Other versions
CN113535756B (en
Inventor
孟正凌
朱仲颖
韩朱忠
Current Assignee (The listed assignees may be inaccurate. Google has not performed a legal analysis and makes no representation or warranty as to the accuracy of the list.)
Shanghai Dameng Database Co Ltd
Original Assignee
Shanghai Dameng Database Co Ltd
Priority date (The priority date 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 date listed.)
Filing date
Publication date
Application filed by Shanghai Dameng Database Co Ltd filed Critical Shanghai Dameng Database Co Ltd
Priority to CN202110873375.9A priority Critical patent/CN113535756B/en
Publication of CN113535756A publication Critical patent/CN113535756A/en
Application granted granted Critical
Publication of CN113535756B publication Critical patent/CN113535756B/en
Active legal-status Critical Current
Anticipated expiration legal-status Critical

Links

Images

Classifications

    • G—PHYSICS
    • G06—COMPUTING; CALCULATING OR COUNTING
    • G06F—ELECTRIC DIGITAL DATA PROCESSING
    • G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
    • G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
    • G06F16/24—Querying
    • G06F16/242—Query formulation
    • G06F16/2433—Query languages
    • G—PHYSICS
    • G06—COMPUTING; CALCULATING OR COUNTING
    • G06F—ELECTRIC DIGITAL DATA PROCESSING
    • G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
    • G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
    • G06F16/22—Indexing; Data structures therefor; Storage structures
    • G06F16/2282—Tablespace storage structures; Management thereof
    • G—PHYSICS
    • G06—COMPUTING; CALCULATING OR COUNTING
    • G06F—ELECTRIC DIGITAL DATA PROCESSING
    • G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
    • G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
    • G06F16/24—Querying
    • G06F16/245—Query processing
    • G06F16/2453—Query optimisation
    • G06F16/24534—Query rewriting; Transformation
    • Y—GENERAL TAGGING OF NEW TECHNOLOGICAL DEVELOPMENTS; GENERAL TAGGING OF CROSS-SECTIONAL TECHNOLOGIES SPANNING OVER SEVERAL SECTIONS OF THE IPC; TECHNICAL SUBJECTS COVERED BY FORMER USPC CROSS-REFERENCE ART COLLECTIONS [XRACs] AND DIGESTS
    • Y02—TECHNOLOGIES OR APPLICATIONS FOR MITIGATION OR ADAPTATION AGAINST CLIMATE CHANGE
    • Y02D—CLIMATE CHANGE MITIGATION TECHNOLOGIES IN INFORMATION AND COMMUNICATION TECHNOLOGIES [ICT], I.E. INFORMATION AND COMMUNICATION TECHNOLOGIES AIMING AT THE REDUCTION OF THEIR OWN ENERGY USE
    • Y02D10/00—Energy efficient computing, e.g. low power processors, power management or thermal management

Landscapes

  • Engineering & Computer Science (AREA)
  • Theoretical Computer Science (AREA)
  • Physics & Mathematics (AREA)
  • Data Mining & Analysis (AREA)
  • Databases & Information Systems (AREA)
  • General Engineering & Computer Science (AREA)
  • General Physics & Mathematics (AREA)
  • Computational Linguistics (AREA)
  • Mathematical Physics (AREA)
  • Software Systems (AREA)
  • Information Retrieval, Db Structures And Fs Structures Therefor (AREA)

Abstract

The embodiment of the invention discloses a data query method, a device, equipment and a storage medium, wherein the method comprises the steps of if a query statement input by a user is a multi-table connection query statement and meets a set condition; dividing the plurality of data tables into a first data table set and a second data table set according to the query items; wherein the data tables in the first set of data tables are directly or indirectly related to the query term; the data tables in the second set of data tables are not related to the query term; removing the data table information in the second data table set from the query statement, and generating a supplemental condition according to the second data table set; splicing the supplementary conditions and the query statements of the removed data table to form new query statements; and performing data query based on the new query statement. By using the method, the supplement conditions are spliced with the query statements with the data tables removed, and the query statements are optimized and rewritten to eliminate redundant tables in the query statements, so that the generation of intermediate result sets can be reduced, and the query efficiency is improved.

Description

Data query method, device, equipment and storage medium
Technical Field
The embodiment of the invention relates to the technical field of databases, in particular to a data query method, a data query device, data query equipment and a storage medium.
Background
When two or more data tables are involved in a Query statement (e.g., Structured Query Language (SQL)), a join Query needs to be performed on the data tables. Multi-table join queries may produce a relatively large set of intermediate results, thereby affecting query efficiency.
Disclosure of Invention
Embodiments of the present invention provide a data query method, apparatus, device, and storage medium, which can reduce generation of an intermediate result set by rewriting a query statement, thereby improving query efficiency.
In a first aspect, an embodiment of the present invention provides a data query method, where the method includes:
if the query statement input by the user is a multi-table connection query statement and meets the set condition; wherein the setting conditions include: carrying set keywords and containing no set functions; the query statement comprises a plurality of data table information;
dividing the plurality of data tables into a first data table set and a second data table set according to the query items; wherein a data table of the first set of data tables is directly or indirectly related to the query term; data tables in the second set of data tables are not related to the query term;
removing the data table information in the second data table set from the query statement, and generating a supplemental condition according to the second data table set;
splicing the supplementary condition with the query statement of the removed data table to form a new query statement;
and performing data query based on the new query statement.
In a second aspect, an embodiment of the present invention further provides a data query apparatus, where the apparatus includes:
the query statement judging module is used for judging whether the query statement input by the user is a multi-table connection query statement and meets a set condition; wherein the setting conditions include: carrying set keywords and containing no set functions; the query statement comprises a plurality of data table information;
the data table dividing module is used for dividing the data tables into a first data table set and a second data table set according to the query terms when the query statement input by the user is a multi-table connection query statement and meets a set condition; wherein a data table of the first set of data tables is directly or indirectly related to the query term; data tables in the second set of data tables are not related to the query term;
a data table information removing module, configured to remove data table information in the second data table set from the query statement, and generate a supplemental condition according to the second data table set;
the query statement splicing module is used for splicing the supplement condition and the query statement of the removed data table to form a new query statement;
and the data query module is used for carrying out data query based on the new query statement.
In a third aspect, an embodiment of the present invention further provides a computer device, including: a memory and one or more processors;
the memory for storing one or more programs;
when executed by the one or more processors, cause the one or more processors to implement the data query method as described in the first aspect above.
In a fourth aspect, embodiments of the present invention also provide a storage medium containing computer-executable instructions, which when executed by a computer processor, are configured to perform the data query method according to the first aspect.
The embodiment of the invention discloses a data query method, a device, equipment and a storage medium, wherein the method comprises the steps of if a query statement input by a user is a multi-table connection query statement and meets a set condition; wherein the setting conditions include: carrying set keywords and containing no set functions; the query statement comprises a plurality of data table information; dividing the plurality of data tables into a first data table set and a second data table set according to the query items; wherein a data table of the first set of data tables is directly or indirectly related to the query term; data tables in the second set of data tables are not related to the query term; removing the data table information in the second data table set from the query statement, and generating a supplemental condition according to the second data table set; splicing the supplementary condition with the query statement of the removed data table to form a new query statement; and performing data query based on the new query statement. By using the method, the data table set irrelevant to the query item is removed from the original query statement, the supplement condition is generated, the supplement condition is spliced with the query statement from which the data table is removed, and the query statement is optimized and rewritten to eliminate the redundant table therein, so that the generation of an intermediate result set can be reduced, and the query efficiency is improved.
Drawings
Fig. 1 is a flowchart of a data query method according to an embodiment of the present invention;
fig. 2 is a flowchart of a data query method according to a second embodiment of the present invention;
fig. 3 is a flowchart of a data query method according to a third embodiment of the present invention;
fig. 4 is a block diagram of a data query apparatus according to a fourth embodiment of the present invention;
fig. 5 is a block diagram of a computer device according to a fifth embodiment of the present invention.
Detailed Description
The present invention will be described in further detail with reference to the accompanying drawings and examples. It is to be understood that the specific embodiments described herein are merely illustrative of the invention and are not limiting of the invention. It should be further noted that, for the convenience of description, only some of the structures related to the present invention are shown in the drawings, not all of the structures.
Example one
Fig. 1 is a flowchart of a data query method according to an embodiment of the present invention, where the present embodiment is applicable to a case where a connection query is performed on multiple data tables, and the method may be performed by a data query apparatus, and the apparatus may be implemented by software and/or hardware.
It should be noted that, in the data query method of this embodiment, syntax and semantic parsing is performed on a query statement input by a user, and equivalent semantics are converted and optimized for a query statement meeting a preset form, so as to narrow a query or search range of data, and query is performed in a corresponding data table according to the converted statement. The method specifically comprises the following steps:
and S110, if the query statement input by the user is a multi-table connection query statement and meets the set condition.
Wherein the setting conditions include: carrying set keywords and containing no set functions; and the query statement comprises a plurality of data table information.
The keywords may be words used for characterizing the function of the sentence, such as the keyword SELECT is used for characterizing the selection of data from the data table, the DISTINCT is used for characterizing the repeated records in the elimination result set, and the WHERE is used for characterizing the implementation of selectively capturing data. In this embodiment, the keywords are set as the keywords representing the deduplication query, such as: "DISTINCT". I.e., one condition that the query statement satisfies is to carry a "DISTINCT" key. The set function is an aggregation function in the query statement.
Specifically, the query statement input by the user is analyzed to determine whether the query statement contains a plurality of data table information, carries the set keyword, and does not contain the set function, and if the query statement contains a plurality of data table information, carries the set keyword, and does not contain the set function, the query statement is an optimized object in this embodiment. The query statement is used for matching data in the data table with the set keyword.
Illustratively, a query statement may be represented as:
SELECT DISTINCT T1.c1 FROM T1, T2, T3, T4, T5 WHERE T2.c2>100 AND T2.c2< T1.c2 AND T4.c2>0 AND T3.c1 ═ T4.c1 AND T5.c1< 200; it can be seen that the data tables involved in the query statement include T1, T2, T3, T4 and T5, the query term includes the keyword DISTINCT, and the query statement is a multi-table join query statement that does not include a set function.
Optionally, the query statement carries condition information.
The condition information may include: the relationship between tables, what contents exist in a certain column of a table, what conditions are satisfied by the contents, and the like. In this embodiment, the condition information may be derived from the keyword "WHERE", that is, the content behind the "WHERE" is the condition information.
Specifically, the query statement carries a query portion and condition information, and the condition information may specifically be a condition that needs to be satisfied by the query data. Taking the query statement exemplified in the above embodiments as an example: "SELECT DISTINCT T1.c1 FROM T1, T2, T3, T4, T5" this section is a query section, "WHERE T2.c2>100 AND T2.c2< T1.c2 AND T4.c2>0 AND T3.c1 ═ T4.c1 AND T5.c1< 200" is a condition information section.
And S120, dividing the plurality of data tables into a first data table set and a second data table set according to the query items.
Wherein the data tables in the first set of data tables are directly or indirectly related to the query term; the data tables in the second set of data tables are unrelated to the query term. The query term may be a "column term" or a "row term" in the table that the query statement is intended to query. Taking the query statement exemplified in the above embodiment as an example, the query term of the query statement is "T1. C1", that is, the data with the column name of "C1" in the table T1 is to be queried.
Specifically, when the query statement input by the user is a multi-table connection query statement and meets a set condition, the data tables related to the query statement are collected, the data tables related to all queried items are divided into a first data table set, and the rest data tables are divided into a second data table set.
Illustratively, a query statement is represented as:
SELECT DISTINCT T1.C1 FROM T1,T2,T3,T4,T5 WHERE T2.C2>100 AND T2.C2<T1.C2 AND T4.C2>0 AND T3.C1＝T4.C1 AND T5.C1<200；
the first set of data tables may be denoted as Ts and the second set of data tables may be denoted as Tr. It can be seen that the data table related to the query statement is: t1, T2, T3, T4, T5. Wherein the data table T1 is partitioned into a first set of data tables Ts, as the data table T1 is directly related to the query term T1.c 1; data tables T2, T3, T4, T5 are not directly related to query terms, and data tables T2, T3, T4, T5 are partitioned into a second set of data tables Tr. Then, the data table in Tr which has a connection relation with the data table in Ts is moved to Ts. Wherein, the data table in Tr having connection relation with the data table in Ts is the data table indirectly related to the query term.
S130, removing the data table information in the second data table set from the query statement, and generating a supplement condition according to the second data table set.
The supplementary condition may be composed of the removed data table information and condition information corresponding to the removed data table information.
In particular, the data tables in the second set of data tables are not associated with the query term, so that the data tables in the second set of data tables are removed from the query statement and a supplemental condition is generated based on the removed data tables.
Illustratively, the supplemental condition may be expressed as EXISTS (SELECT TOP 11 FROM tab1, tab2, …, tab WHERE exp), WHERE tab1, tab2, …, tab x are all data tables in the second set of data tables, and exp is all conditions in the query statement that are related to data tables tab1, tab2, …, tab x.
And S140, splicing the supplement condition and the query statement of the removed data table to form a new query statement.
The manner of splicing the supplemental condition with the query statement of the removed data table may be: and (3) placing the supplementary conditions in a WHERE clause of the query statement of the removed data table, and splicing the supplementary conditions with the original query conditions by adopting set fields.
Wherein the setting field may be "AND". The number of the supplementary conditions may be one or more, for example, a plurality of supplementary conditions may be concatenated by setting a field. For example, the WHERE clause of the new query statement may be in the form of: WHERE original query condition AND supplement condition.
And S150, performing data query based on the new query statement.
Specifically, the optimized new query statement is used for data query.
The embodiment of the invention discloses a data query method, which comprises the steps that if a query statement input by a user is a multi-table connection query statement and meets a set condition; wherein the setting conditions include: carrying set keywords and containing no set functions; the query statement comprises a plurality of data table information; dividing the plurality of data tables into a first data table set and a second data table set according to the query items; wherein the data tables in the first set of data tables are directly or indirectly related to the query term; the data tables in the second set of data tables are not related to the query term; removing the data table information in the second data table set from the query statement, and generating a supplemental condition according to the second data table set; splicing the supplementary conditions and the query statements of the removed data table to form new query statements; and performing data query based on the new query statement. By using the method, the data table set irrelevant to the query item is removed from the original query statement, the supplement condition is generated, the supplement condition is spliced with the query statement from which the data table is removed, and the query statement is optimized and rewritten to eliminate the redundant table therein, so that the generation of an intermediate result set can be reduced, and the query efficiency is improved.
Example two
Fig. 2 is a flowchart of a data query method according to a second embodiment of the present invention, and the present embodiment further optimizes the data query method based on the above embodiments. The method specifically comprises the following steps:
s210, if the query statement input by the user is a multi-table connection query statement and meets the set condition.
S220, dividing the data tables directly related to the query items into a first data table set, and dividing the rest data tables into a second data table set.
Specifically, when the query statement input by the user is a multi-table connection query statement and meets a set condition, the data tables involved in the query statement are collected, the data tables directly involved in the query terms of all the query statements are divided into a first data table set, and the rest data tables are divided into a second data table set.
Illustratively, if a query statement is expressed as:
SELECT DISTINCT T1.C1 FROM T1,T2,T3,T4,T5 WHERE T2.C2>100 AND T2.C2<T1.C2 AND T4.C2>0 AND T3.C1＝T4.C1 AND T5.C1<200；
it can be seen that the data table related to the query statement is: t1, T2, T3, T4, T5, wherein the data table T1 is directly related to the query term of the query statement and the data tables T2, T3, T4, T5 are not directly related to the query term of the query statement. If the first set of data tables can be represented as Ts and the second set of data tables can be represented as Tr, the data table T1 is divided into the first set of data tables Ts and the data tables T2, T3, T4 and T5 are divided into the second set of data tables Tr.
S230, traversing the data tables in the second data table set, and judging whether connection conditions exist between the traversed data tables and the data tables in the first data table set or not according to the condition information; if so, go to step 240.
It will be clear that the data tables in the first set of data tables are directly or indirectly related to the query term. This step determines whether the data tables in the second set of data tables are indirectly related to the query terms of the query statement by determining whether a connection condition exists between the data tables in the second set of data tables and the data tables in the first set of data tables.
Specifically, traversing the data tables in the second data table set, and judging whether connection conditions exist between the traversed data tables and the data tables in the first data table set according to the condition part of the query statement. The join condition may be a typical join operation such as a comparison operator, or may be an internal join, and the join condition is not particularly limited herein.
Illustratively, as described in the above example, the condition part of the above example query statement is: WHERE T2.c2>100 AND T2.c2< T1.c2 AND T4.c2>0 AND T3.c1 ═ T4.c1 AND T5.c1<200, it can be seen that only the connection condition T2.c2< T1.c2 exists between data table T2 AND data table T1.
S240, moving the traversed data table to the first data table set to form a new first data table set and a new second data table set.
Specifically, if a connection condition exists between the data table in the second data table set and the data table in the first data table set, it indicates that the data table in the second data table set is indirectly related to the query term of the query statement, and the data table with the connection condition needs to be moved to the first data table set to form a new first data table set and a new second data table set.
Illustratively, as described in the above example, in the above example query statement, only if there is a connection condition T2.c2< T1.c2 between data table T2 and data table T1, and there is no connection condition between data tables T3, T4, T5 and data table T1, then data table T2 is moved from the second set of data tables to the first set of data tables. Finally, the first data table set Ts comprises data tables T1 and T2, and the second data table set Tr comprises data tables T3, T4 and T5.
And S250, for the new second data table set, returning and executing the operation of traversing the data tables in the second data table set, and judging whether the traversed data tables and the data tables in the first data table set have connection conditions according to the condition information until the data tables directly or indirectly related to the query item are all divided into the first data table set.
Specifically, for a newly added data table in the first data table set, the data table in the second data table set is traversed again, whether a connection condition exists between the traversed data table and the newly added data table in the first data table set is judged according to the condition information, if the connection condition exists, the data table is moved from the second data table set to the first data table set until the data tables directly or indirectly related to the query item are all divided into the first data table set, and the data tables in the second data table set are all unrelated to the query item.
S260, removing the data table information in the second data table set from the query statement, and generating a supplementary condition according to the second data table set.
And S270, splicing the supplement condition and the query statement of the removed data table to form a new query statement.
And S280, performing data query based on the new query statement.
The embodiment of the invention discloses a data query method, which is optimized on the basis of the embodiment, and the steps of dividing a first data table set and a second data table set are refined.
EXAMPLE III
Fig. 3 is a flowchart of a data query method according to a third embodiment of the present invention, and the present embodiment further optimizes the data query method based on the foregoing embodiments. The method specifically comprises the following steps:
s310, if the query statement input by the user is a multi-table connection query statement and meets the set condition.
Wherein the setting conditions include: carrying set keywords and containing no set functions; the query statement comprises a plurality of data table information;
and S320, dividing the plurality of data tables into a first data table set and a second data table set according to the query items.
Wherein the data tables in the first set of data tables are directly or indirectly related to the query term; the data tables in the second set of data tables are unrelated to the query term.
S330, grouping the data table information in the second data table set according to the condition information to obtain at least one data table group.
Wherein, the connection condition exists between the data tables in the data table group.
Specifically, according to the condition information of the query statement, the data tables with the connection conditions in the second data table set are grouped into one group, and a data table group is formed. The join condition may be a typical join operation, such as a comparison operator, or an internal join, and the join condition is not particularly limited herein. It can be known that, when there is no connection condition between a certain data table and other data tables in the second data table set, the data table alone forms a data table group.
Illustratively, as described in the above example, the second data set Tr is divided into several minimum sets (i.e. data table sets) Tr1, Tr2, …, Trm without mutual connection condition, where m is the number of finally formed data table sets. In the above example query statement, a connection condition T3.c1 is present between the data tables T3 and T4 in the second data table set Tr, and a connection condition is not present between the data tables T3 and T5, and between the data tables T4 and T5, so that the tables T3 and T4 are divided into the set Tr1, and the data table T5 is divided into the set Tr 2.
S340, removing the data tables from the query statement in sequence according to the at least one data table group, and generating at least one supplementary condition according to the at least one data table group.
It should be noted that, in this step, since the data table in each data table group has neither a connection condition with the data tables in other data table groups nor participates in the projection of the final result set, the effect is to make p copies of the result of the connection of other data tables outside the data table group only according to the filtered number p of intermediate result set lines of the set itself. And because the query is a DISTINCT query, repeated records are filtered out finally, so that the data tables in the data table group actually belong to redundant tables, and all the data tables in the data table group can be removed from the query statement. However, when the result set generated by the data table group is empty, the result set of the query is always empty, and at this time, if all the data table information in the data table group is directly removed from the query statement, the result set of the query may not be empty any more, and thus the final query result may also change, so that the data table is removed from the query statement, and a supplement condition needs to be generated according to the data table group, and the supplement condition can ensure that the final query result will not change after all the data tables in the data table group are removed no matter whether the result set generated by the data table group is empty or not.
In particular, the data tables involved in the set of data tables are independent of the query term of the query statement, and therefore the data tables are removed from the query statement and a supplemental condition is generated based on the set of data tables.
Optionally, at least one data table group is traversed, the data table information in the traversed data table group is removed from the query statement, and a supplemental condition is generated according to the traversed data table group.
Illustratively, as described above with reference to the example, the example query statement may be expressed as: SELECT DISTINCT T1.c1 FROM T1, T2, T3, T4, T5 WHERE T2.c2>100 AND T2.c2< T1.c2 AND T4.c2>0 AND T3.c1 ═ T4.c1 AND T5.c1< 200;
the data table group Tr1 includes data tables T3 AND T4, so that the data table information about T3 AND T4 is removed FROM the query statement to obtain SELECT DISTINCT T1.c1 FROM T1, T2, T5 WHERE T2.c2>100 AND T2.c2< T1.c2 AND T5.c1<200, AND then the complementary condition EXISTS is generated according to the traversed data tables T3 AND T4 (SELECT TOP 11 FROM T3, T4 WHERE T4.c2>0 AND T3.c1 ═ T4.c 1).
And S350, splicing the supplementary condition and the query statement of the removed data table through a set field.
Wherein the setting field may be "AND".
Illustratively, as described in the above example, the supplemented condition AND the original condition of the query statement may be connected by AND, resulting in SELECT DISTINCT T1.c1 FROM T1, T2, T5 WHERE T2.c2>100 AND T2.c2< T1.c2 AND T5.c1<200 AND EXISTS (SELECT TOP 11 FROM T3, T4 WHERE T4.c2>0 AND T3.c1 ═ T4.c 1).
And S360, if the supplementary conditions comprise two or more than two supplementary conditions, splicing the supplementary conditions by adopting set fields to form a new query statement.
Wherein the setting field may be "AND".
Specifically, when the supplementary condition includes two or more than two, the corresponding set fields are adopted for splicing according to the relationship of the supplementary condition to form a new query statement.
Illustratively, as described in the above example, Tr2 includes table T5, so that data table T5 is first removed FROM the query statement, resulting in SELECT DISTINCT T1.c1 FROM T1, T2 WHERE T2.c2>100 AND T2.c2< T1.c2 AND EXISTS (SELECT TOP 11 FROM T3, T4 WHERE T4.c2>0 AND T3.c1 ═ T4.c1), then the query statement is supplemented with condition EXISTS (SELECT TOP 11 FROM T7 WHERE T5.c1<200), the supplemented condition is connected with the original condition, resulting in SELECT DISTINCT T1.c1 FROM T1, T2 WHERE T2.2 >100 AND T2.c2< T1.c 2< T3.c 2> T3, T3.c 2 AND T3.c1 > T4 AND T3.c 1< T4.c1 > T4.c 1.
And S370, performing data query based on the new query statement.
Illustratively, the original query statement may be represented as:
SELECT DISTINCT T1.C1 FROM T1,T2,T3,T4,T5 WHERE T2.C2>100 AND T2.C2<T1.C2 AND T4.C2>0 AND T3.C1＝T4.C1 AND T5.C1<200；
after the optimization of the method, the new query statement can be expressed as:
SELECT DISTINCT T1.C1 FROM T1,T2 WHERE T2.C2>100 AND T2.C2<T1.C2 AND EXISTS(SELECT TOP 1 1 FROM T3,T4 WHERE T4.C2>0 AND T3.C1＝T4.C1)AND EXISTS(SELECT TOP 1 1 FROM T5 WHERE T5.C1<200)；
the embodiment of the invention discloses a data query method, which is optimized on the basis of the embodiment, and the steps of generating a supplement condition and forming a new query statement are refined.
Example four
Fig. 4 is a block diagram of a data query apparatus according to a fourth embodiment of the present invention, and as shown in fig. 4, the apparatus includes: a query statement judging module 41, a data table dividing module 42, a data table information removing module 43, a query statement splicing module 44 and a data query module 45.
A query sentence judgment module 41, configured to judge whether a query sentence input by a user is a multi-table connection query sentence and meets a set condition; wherein the setting conditions include: carrying set keywords and containing no set functions; the query statement comprises a plurality of data table information;
the data table dividing module 42 is configured to divide the multiple data tables into a first data table set and a second data table set according to the query term when the query statement input by the user is a multi-table connection query statement and meets a set condition; wherein the data tables in the first set of data tables are directly or indirectly related to the query term; the data tables in the second set of data tables are not related to the query term;
a data table information removing module 43, configured to remove the data table information in the second data table set from the query statement, and generate a supplemental condition according to the second data table set;
a query sentence splicing module 44, configured to splice the supplemental condition with the query sentence from which the data table is removed, so as to form a new query sentence;
and a data query module 45, configured to perform data query based on the new query statement.
Optionally, the query statement carries condition information.
Optionally, the data table dividing module 42 may specifically be configured to:
dividing data tables directly related to the query items into a first data table set, and dividing the rest data tables into a second data table set;
traversing the data tables in the second data table set, and judging whether connection conditions exist between the traversed data tables and the data tables in the first data table set or not according to the condition information;
if the connection condition exists, moving the traversed data table to the first data table set to form a new first data table set and a new second data table set;
and for the new second data table set, returning and executing the operation of traversing the data tables in the second data table set, and judging whether the traversed data tables and the data tables in the first data table set have connection conditions according to the condition information until the data tables directly or indirectly related to the query item are all divided into the first data table set.
Optionally, the data table information removing module 43 may include:
the data table group acquisition unit is used for grouping the data table information in the second data table set according to the condition information to obtain at least one data table group; wherein, the connection condition exists between the data tables in the data table group;
and the supplementary condition generating unit is used for removing the data tables from the query statement in sequence according to the at least one data table group and generating at least one supplementary condition according to the at least one data table group.
Optionally, the supplemental condition generating unit may be specifically configured to:
and traversing at least one data table group, removing the data table information in the traversed data table group from the query statement, and generating a supplement condition according to the traversed data table group.
Optionally, the query statement splicing module 44 may be specifically configured to:
splicing the supplementary condition and the query statement of the removed data table through a set field;
and if the supplementary conditions comprise two or more than two supplementary conditions, splicing the supplementary conditions by adopting set fields to form a new query statement.
Optionally, the supplemental condition is composed of the removed data table information and condition information corresponding to the removed data table information.
The device can execute the methods provided by all the embodiments of the invention, and has corresponding functional modules and beneficial effects for executing the methods. For details not described in detail in this embodiment, reference may be made to the methods provided in all the foregoing embodiments of the present invention.
EXAMPLE five
Fig. 5 is a block diagram of a computer device according to a fifth embodiment of the present invention, as shown in fig. 5, the computer device includes a processor 51, a memory 52, an input device 53, and an output device 54; the number of the processors 51 in the computer device may be one or more, and one processor 51 is taken as an example in fig. 5; the processor 51, the memory 52, the input device 53 and the output device 54 in the computer apparatus may be connected by a bus or other means, and the connection by the bus is exemplified in fig. 5.
The memory 52 is a computer-readable storage medium, and can be used for storing software programs, computer-executable programs, and modules, such as program instructions/modules corresponding to the data query method in the embodiment of the present invention (for example, in the data query device, the query statement determination module 41, the data table division module 42, the data table information removal module 43, the query statement concatenation module 44, and the data query module 45). The processor 51 executes various functional applications of the computer device and data processing by executing software programs, instructions and modules stored in the memory 52, that is, implements the data query method described above.
The memory 52 may mainly include a storage program area and a storage data area, wherein the storage program area may store an operating system, an application program required for at least one function; the storage data area may store data created according to the use of the terminal, and the like. Further, the memory 52 may include high speed random access memory, and may also include non-volatile memory, such as at least one magnetic disk storage device, flash memory device, or other non-volatile solid state storage device. In some examples, the memory 52 may further include memory located remotely from the processor 51, which may be connected to a computer device over a network. Examples of such networks include, but are not limited to, the internet, intranets, local area networks, mobile communication networks, and combinations thereof.
The input device 53 may be used to receive input numeric or character information and generate key signal inputs related to user settings and function controls of the computer apparatus. The output device 54 may include a display device such as a display screen.
EXAMPLE six
An embodiment of the present invention further provides a storage medium containing computer-executable instructions, which when executed by a computer processor, perform a data query method, including:
if the query statement input by the user is a multi-table connection query statement and meets the set condition; wherein the setting conditions include: carrying set keywords and containing no set functions; the query statement comprises a plurality of data table information;
dividing the plurality of data tables into a first data table set and a second data table set according to the query items; wherein the data tables in the first set of data tables are directly or indirectly related to the query term; the data tables in the second set of data tables are not related to the query term;
removing the data table information in the second data table set from the query statement, and generating a supplemental condition according to the second data table set;
splicing the supplementary conditions and the query statements of the removed data table to form new query statements;
and performing data query based on the new query statement.
Of course, the storage medium provided by the embodiment of the present invention contains computer-executable instructions, and the computer-executable instructions are not limited to the above method operations, and may also perform related operations in the data query method provided by any embodiment of the present invention.
From the above description of the embodiments, it is obvious for those skilled in the art that the present invention can be implemented by software and necessary general hardware, and certainly, can also be implemented by hardware, but the former is a better embodiment in many cases. Based on such understanding, the technical solutions of the present invention may be embodied in the form of a software product, which can be stored in a computer-readable storage medium, such as a floppy disk, a Read-Only Memory (ROM), a Random Access Memory (RAM), a FLASH Memory (FLASH), a hard disk or an optical disk of a computer, and includes several instructions for enabling a computer device (which may be a personal computer, a server, or a network device) to execute the methods of the embodiments of the present invention.
It should be noted that, in the embodiment of the data query apparatus, the included units and modules are only divided according to functional logic, but are not limited to the above division as long as the corresponding functions can be implemented; in addition, specific names of the functional units are only for convenience of distinguishing from each other, and are not used for limiting the protection scope of the present invention.
It is to be noted that the foregoing is only illustrative of the preferred embodiments of the present invention and the technical principles employed. It will be understood by those skilled in the art that the present invention is not limited to the particular embodiments illustrated herein, but is capable of various obvious changes, rearrangements and substitutions as will now become apparent to those skilled in the art without departing from the scope of the invention. Therefore, although the present invention has been described in greater detail by the above embodiments, the present invention is not limited to the above embodiments, and may include other equivalent embodiments without departing from the spirit of the present invention, and the scope of the present invention is determined by the scope of the appended claims.

Claims (10)

1. A method for querying data, comprising:
if the query statement input by the user is a multi-table connection query statement and meets the set condition; wherein the setting conditions include: carrying set keywords and containing no set functions; the query statement comprises a plurality of data table information;
dividing the plurality of data tables into a first data table set and a second data table set according to the query items; wherein a data table of the first set of data tables is directly or indirectly related to the query term; data tables in the second set of data tables are not related to the query term;
removing the data table information in the second data table set from the query statement, and generating a supplemental condition according to the second data table set;
splicing the supplementary condition with the query statement of the removed data table to form a new query statement;
and performing data query based on the new query statement.
2. The method of claim 1, wherein the query statement carries conditional information.
3. The method of claim 2, wherein partitioning the plurality of data tables into a first set of data tables and a second set of data tables according to query terms comprises:
dividing data tables directly related to the query item into a first data table set, and dividing the rest of data tables into a second data table set;
traversing the data tables in the second data table set, and judging whether connection conditions exist between the traversed data tables and the data tables in the first data table set or not according to the condition information;
if the connection condition exists, moving the traversed data table to the first data table set to form a new first data table set and a new second data table set;
and for the new second data table set, returning and executing the operation of traversing the data tables in the second data table set, and judging whether the traversed data tables and the data tables in the first data table set have connection conditions according to the condition information until the data tables directly or indirectly related to the query item are all divided into the first data table set.
4. The method of claim 2, wherein removing the data table information in the second set of data tables from the query statement and generating a supplemental condition from the second set of data tables comprises:
grouping the data table information in the second data table set according to the condition information to obtain at least one data table group; wherein, the connection condition exists between the data tables in the data table group;
and sequentially removing the data tables from the query statement according to the at least one data table group, and generating at least one supplementary condition according to the at least one data table group.
5. The method of claim 4, wherein removing data tables from the query statement in sequence according to the at least one data table set and generating at least one supplemental condition according to the at least one data table set comprises:
and traversing the at least one data table group, removing the data table information in the traversed data table group from the query statement, and generating a supplement condition according to the traversed data table group.
6. The method of claim 4, wherein splicing the supplemental condition with the query statement that removes the data table to form a new query statement comprises:
splicing the supplementary condition and the query statement of the removed data table through a set field;
and if the supplementary conditions comprise two or more than two supplementary conditions, splicing the supplementary conditions by adopting the set fields to form a new query statement.
7. The method according to any one of claims 1 or 4 to 6, wherein the supplementary condition is constituted by the removed data table information and condition information corresponding to the removed data table information.
8. A data query apparatus, comprising:
the query statement judging module is used for judging whether the query statement input by the user is a multi-table connection query statement and meets a set condition; wherein the setting conditions include: carrying set keywords and containing no set functions; the query statement comprises a plurality of data table information;
the data table dividing module is used for dividing the data tables into a first data table set and a second data table set according to the query terms when the query statement input by the user is a multi-table connection query statement and meets a set condition; wherein a data table of the first set of data tables is directly or indirectly related to the query term; data tables in the second set of data tables are not related to the query term;
a data table information removing module, configured to remove data table information in the second data table set from the query statement, and generate a supplemental condition according to the second data table set;
the query statement splicing module is used for splicing the supplement condition and the query statement of the removed data table to form a new query statement;
and the data query module is used for carrying out data query based on the new query statement.
9. A computer device, comprising: a memory and one or more processors;
the memory for storing one or more programs;
when executed by the one or more processors, cause the one or more processors to implement a data query method as claimed in any one of claims 1-7.
10. A storage medium containing computer-executable instructions for performing the data query method of any one of claims 1-7 when executed by a computer processor.
CN202110873375.9A 2021-07-30 2021-07-30 Data query method, device, equipment and storage medium Active CN113535756B (en)

Priority Applications (1)

Application Number Priority Date Filing Date Title
CN202110873375.9A CN113535756B (en) 2021-07-30 2021-07-30 Data query method, device, equipment and storage medium

Applications Claiming Priority (1)

Application Number Priority Date Filing Date Title
CN202110873375.9A CN113535756B (en) 2021-07-30 2021-07-30 Data query method, device, equipment and storage medium

Publications (2)

Publication Number Publication Date
CN113535756A true CN113535756A (en) 2021-10-22
CN113535756B CN113535756B (en) 2023-05-30

Family

ID=78089945

Family Applications (1)

Application Number Title Priority Date Filing Date
CN202110873375.9A Active CN113535756B (en) 2021-07-30 2021-07-30 Data query method, device, equipment and storage medium

Country Status (1)

Country Link
CN (1) CN113535756B (en)

Citations (9)

* Cited by examiner, † Cited by third party
Publication number Priority date Publication date Assignee Title
CN107315790A (en) * 2017-06-14 2017-11-03 腾讯科技（深圳）有限公司 A kind of optimization method and device of irrelevant subquery
CN107784030A (en) * 2016-08-31 2018-03-09 华为技术有限公司 A kind of method and device for handling Connection inquiring
CN109508339A (en) * 2018-11-12 2019-03-22 上海达梦数据库有限公司 A kind of data query method, apparatus, terminal device and storage medium
CN109947804A (en) * 2019-03-20 2019-06-28 上海达梦数据库有限公司 Optimization method, device, server and the storage medium of data acquisition system inquiry
CN109947791A (en) * 2019-03-27 2019-06-28 上海达梦数据库有限公司 A kind of database statement optimization method, device, equipment and storage medium
CN110222071A (en) * 2019-06-06 2019-09-10 上海达梦数据库有限公司 A kind of data query method, apparatus, server and storage medium
CN110471935A (en) * 2019-08-15 2019-11-19 上海达梦数据库有限公司 A kind of execution method, apparatus, equipment and the storage medium of data manipulation
CN110555035A (en) * 2018-05-31 2019-12-10 阿里巴巴集团控股有限公司 Method and device for optimizing query statement
CN112732752A (en) * 2021-01-29 2021-04-30 上海达梦数据库有限公司 Query statement optimization method, device, equipment and storage medium

Patent Citations (10)

* Cited by examiner, † Cited by third party
Publication number Priority date Publication date Assignee Title
CN107784030A (en) * 2016-08-31 2018-03-09 华为技术有限公司 A kind of method and device for handling Connection inquiring
CN107315790A (en) * 2017-06-14 2017-11-03 腾讯科技（深圳）有限公司 A kind of optimization method and device of irrelevant subquery
US20190347260A1 (en) * 2017-06-14 2019-11-14 Tencent Technology (Shenzhen) Company Limited Uncorrelated subquery optimization method and apparatus, and storage medium
CN110555035A (en) * 2018-05-31 2019-12-10 阿里巴巴集团控股有限公司 Method and device for optimizing query statement
CN109508339A (en) * 2018-11-12 2019-03-22 上海达梦数据库有限公司 A kind of data query method, apparatus, terminal device and storage medium
CN109947804A (en) * 2019-03-20 2019-06-28 上海达梦数据库有限公司 Optimization method, device, server and the storage medium of data acquisition system inquiry
CN109947791A (en) * 2019-03-27 2019-06-28 上海达梦数据库有限公司 A kind of database statement optimization method, device, equipment and storage medium
CN110222071A (en) * 2019-06-06 2019-09-10 上海达梦数据库有限公司 A kind of data query method, apparatus, server and storage medium
CN110471935A (en) * 2019-08-15 2019-11-19 上海达梦数据库有限公司 A kind of execution method, apparatus, equipment and the storage medium of data manipulation
CN112732752A (en) * 2021-01-29 2021-04-30 上海达梦数据库有限公司 Query statement optimization method, device, equipment and storage medium

Non-Patent Citations (1)

* Cited by examiner, † Cited by third party
Title
龚成鹏: "基于关系型数据库中SQL语句优化规则的研究", 《中国优秀硕士学位论文全文数据库 信息科技辑》 *

Also Published As

Publication number Publication date
CN113535756B (en) 2023-05-30

Similar Documents

Publication Publication Date Title
CN110515973B (en) Data query optimization method, device, equipment and storage medium
CN104182405B (en) Method and device for connection query
US8396852B2 (en) Evaluating execution plan changes after a wakeup threshold time
US8332389B2 (en) Join order for a database query
CN109508339B (en) Data query method and device, terminal equipment and storage medium
CN110362593B (en) Data query method, device, equipment and storage medium
CN110968593B (en) Database SQL statement optimization method, device, equipment and storage medium
CN108804554B (en) Database query method, database query device, server and storage medium
CN104636478A (en) Information query method and device
US8442971B2 (en) Execution plans with different driver sources in multiple threads
CN109791543B (en) Control method for executing multi-table connection operation and corresponding device
CN104077297A (en) Query method and query device based on body
CN110502532B (en) Method, device, equipment and storage medium for optimizing remote database object
CN112100198B (en) Database SQL statement optimization method, device, equipment and storage medium
CN111198898B (en) Big data query method and big data query device
CN109299101B (en) Data retrieval method, device, server and storage medium
CN113946600A (en) Data query method, data query device, computer equipment and medium
CN112732752A (en) Query statement optimization method, device, equipment and storage medium
CN115964374A (en) Query processing method and device based on pre-calculation scene
CN111309753A (en) Method, device and equipment for optimizing structured query statement and storage medium
CN110019380B (en) Data query method, device, server and storage medium
CN108241709B (en) Data integration method, device and system
CN112199390B (en) Data query method, device, equipment and storage medium in database
CN110704472A (en) Data query statistical method and device
CN110990423B (en) SQL statement execution method, device, equipment and storage medium

Legal Events

Date Code Title Description
PB01 Publication
PB01 Publication
SE01 Entry into force of request for substantive examination
SE01 Entry into force of request for substantive examination
GR01 Patent grant
GR01 Patent grant