CN110928900B - Multi-table data query method, device, terminal and computer storage medium - Google Patents
Multi-table data query method, device, terminal and computer storage medium Download PDFInfo
- Publication number
- CN110928900B CN110928900B CN201811082460.8A CN201811082460A CN110928900B CN 110928900 B CN110928900 B CN 110928900B CN 201811082460 A CN201811082460 A CN 201811082460A CN 110928900 B CN110928900 B CN 110928900B
- Authority
- CN
- China
- Prior art keywords
- data
- query
- page
- records
- maximum
- 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.)
- Active
Links
Images
Abstract
The application discloses a multi-table data query method, a multi-table data query device, a multi-table data query terminal and a computer storage medium, wherein the query method comprises the following steps: searching data records meeting the query conditions and the number of the data records from the plurality of data tables, and determining the data table with the largest number of the data records as a maximum data table; determining to obtain the actual initial displacement and the actual number of each page of data record of the maximum data table according to the paging condition of the maximum data table and the total amount of the data records of the other data tables which meet the query condition, and sequentially taking out the actual data records of each page of the maximum data table; merging the data records of other data tables which meet the query condition into each page of data record of the maximum data table, and sorting according to a set sequence to obtain a sorted data table; a set number of data records are returned from the data table. By the method, the complexity of inquiring the multi-table data in time and space is effectively reduced, the calculated amount is reduced, and the inquiring efficiency is improved.
Description
Technical Field
The present application relates to the field of multi-table data query technologies, and in particular, to a multi-table data query method, apparatus, terminal, and computer storage medium.
Background
With the development of information technology and markets, particularly after the nineties of the twentieth century, data management is no longer just storing and managing data, but is changed into various data management modes required by users, and meanwhile, the amount of information in a database is larger and larger, and people often need to search and query various information in the database according to self needs. When the data volume is too large, various problems may occur during query, for example, server resources are exhausted, processing is overtime due to too large data transmission volume, and the like, and finally, the query cannot be completed, and the query results are usually many, even massive data. If all the query results are displayed, people often cannot find the information which the people really want to know. In addition, when the data amount of a table in the database is too large, if the table is a single table, paging is generally performed by means of the self-capability of the data table, such as limit of oracle's rownum, mysql, the data of the table is segmented according to a certain rule, such as time interval, and is stored in a plurality of tables with the same structure, and then when the data is operated, the pressure is distributed to the plurality of tables without operating a large table, so that the execution efficiency is improved. For multi-tables, sorting and paging are performed after the multi-tables are combined.
By the method, when paging inquiry is carried out, the inquiry work of a plurality of tables is finished because data can be distributed on the plurality of tables. If only a single table is queried, the implementation is relatively simple. If the data is multiple, all the data in the table need to be taken out at the same time, then merging is carried out, and paging is carried out, so that the calculation amount is large, and the performance is low.
Disclosure of Invention
The technical problem mainly solved by the application is to provide a multi-table data query method, device, terminal and computer storage medium, which can effectively reduce the complexity of querying multi-table data in time and space, reduce the amount of calculation and improve the query efficiency.
In order to solve the above technical problem, the first technical solution adopted by the present application is: a method for querying multi-table data is provided, and the method comprises the following steps: searching data records meeting the query conditions and the number of the data records from the plurality of data tables, and determining the data table with the largest number of the data records as a maximum data table;
determining to obtain the actual initial displacement and the actual number of each page of data record of the maximum data table according to the paging condition of the maximum data table and the total amount of the data records of the other data tables which meet the query condition, and sequentially taking out the actual data records of each page of the maximum data table;
merging the data records of other data tables which meet the query condition into each page of data record of the maximum data table, and sorting according to a set sequence to obtain a sorted data table;
a set number of data records are returned from the data table.
In order to solve the above technical problem, the second technical solution adopted by the present application is: provided is a multi-table data query device, comprising: a query module, a data acquisition module, a merging module and a data return module,
the query module is used for searching the data records meeting the query conditions and the quantity of the data records from the plurality of data tables and determining the data table with the largest quantity of the data records as a maximum data table;
the data acquisition module is used for determining the actual initial displacement and the actual number of each page of data record of the maximum data table according to the paging condition of the maximum data table and the total amount of the data records of the other data tables meeting the query condition, and sequentially taking out the actual data records of each page of the maximum data table;
the merging module is used for merging the data records meeting the query conditions of other data tables into each page of data record of the maximum data table, and sorting the data records according to a set sequence to obtain a sorted data table;
the data return module is used for returning the data records with the set number from the data table.
In order to solve the above technical problem, the third technical solution adopted by the present application is: the multi-table data query terminal is provided, and comprises: the system comprises a processor and a human-computer interaction control circuit which are mutually coupled, wherein the processor is matched with the human-computer interaction control circuit to realize any one of the query methods when in work.
In order to solve the above technical problem, a fourth technical solution adopted by the present application is: there is provided a computer storage medium having stored thereon program data which, when executed by a processor, implements the querying method of any one of the above.
Compared with the prior art, the beneficial effects of this application are: according to the method and the device, after the data records meeting the conditions and the number of the data records are searched from the data tables, the maximum data table with the largest number is determined, the data records of other data tables are obtained, the actual initial displacement and the actual number of the data records of each page of the maximum data table are obtained according to the total number of the data records of other data tables and the paging condition of the maximum data table, and the actual data records of each page of the maximum data table are sequentially taken out. All data records of other data tables are obtained firstly, and then the data record of the largest data table is obtained in the paging mode, the data obtaining process is always in a one-dimensional space, the data volume obtained every time is not large, the actual calculation amount can be effectively reduced, and the query efficiency is improved. The data records of other data tables which meet the query conditions are merged into each page of data record of the maximum data table and are sorted according to the set sequence to obtain the sorted data table, so that each piece of data in the other data tables can be guaranteed to be acquired and displayed, and because the data records of the other data tables are small in number relative to the maximum data table, the calculation amount in sorting can be greatly reduced when merging and sorting are carried out, and the query efficiency is improved compared with the existing situation that all the data records are sorted together.
Drawings
FIG. 1 is a schematic flow chart diagram illustrating an embodiment of a method for querying multi-table data according to the present application;
FIG. 2 is a flowchart illustrating an embodiment of the step of FIG. 1 of retrieving actual data records for each page of the largest data table in sequence;
FIG. 3 is a schematic structural diagram of an embodiment of an apparatus for querying multi-table data according to the present application;
FIG. 4 is a schematic structural diagram of another embodiment of the apparatus for querying multi-table data according to the present application;
FIG. 5 is a schematic structural diagram of an embodiment of a job monitoring terminal according to the present application;
FIG. 6 is a schematic structural diagram of an embodiment of a computer storage medium according to the present application.
Detailed Description
The technical solutions in the embodiments of the present application will be clearly and completely described below with reference to the drawings in the embodiments of the present application, and it is obvious that the described embodiments are only a part of the embodiments of the present application, and not all of the embodiments. All other embodiments, which can be derived by a person skilled in the art from the embodiments given herein without making any creative effort, shall fall within the protection scope of the present application.
As shown in fig. 1, fig. 1 is a schematic flow chart of an embodiment of a multi-table data query method according to the present application. The multi-table data query method of the implementation method comprises the following steps:
step 101: and searching the data records meeting the query condition and the number of the data records from the plurality of data tables, and determining the data table with the maximum number of the data records as a maximum data table.
When a user inquires data through a terminal, an inquiry request comprising inquiry conditions is sent to the terminal, and correspondingly, the terminal receives the inquiry request.
The terminal comprises a smart phone, a tablet personal computer, a Personal Computer (PC) and other queryable intelligent devices, such as wearable devices with a query function, such as a smart watch.
The query condition is a condition for screening data from a data table, such as information for processing data for machines between 10:00 and 11:00 a.m.
And after receiving the query instruction, the terminal acquires the source table links of the plurality of data tables according to the query condition in order to obtain complete data, and screens the data records and the quantity which accord with the query condition from the source table links according to the query condition.
Optionally, in order to ensure the validity of the obtained data record, the sorting rules of the multiple data tables are the same, for example, all the data tables are sorted according to time, and the reverse order or the sequence is not limited.
Further, after the number of data records of each data table is obtained, in order to obtain the workload when acquiring data, in the present embodiment, one maximum data table is determined according to the number of data records of each data table. For example, there are three tables, i.e., table a, table B and table C, after screening, the number of data records of table C meeting the query condition is 1000, i.e., count (C) is 1000, the number of data records of table B meeting the query condition is 1, i.e., count (B) is 1, and the number of data records of table a meeting the query condition is 2, i.e., count (a) is 2, and the table a is determined to be the largest data table by comparison. And taking out the data records of other data tables except the maximum data table A, and caching the data records.
Step 102: and determining the actual initial displacement and the actual number of the data records of each page of the maximum data table according to the paging condition of the maximum data table and the total amount of the data records of the other data tables which meet the query condition, and sequentially taking out the actual data records of each page of the maximum data table.
After the terminal determines the maximum data table, in order to reduce the calculation amount in the query process, the data in the maximum data table are respectively obtained in a paging query mode. After the data of each page of the maximum data table is acquired, in order to ensure that the data records of all the data tables except the maximum data table are completely returned, before the actual insertion position of the data record is not determined, the data records of other data tables are respectively inserted into the data records of each page of the maximum data table in order to ensure the completeness of the data return.
As described above, there are many cases in which the actual insertion position of the data record of the other data table is not determined. Such as all data records appearing on the same page. By continuing the example of the above a table, B table, and C table, the C table is the maximum data table, the total number of data records of which the a table and B table satisfy the query condition is 3, and taking the example that the data record of each page of the a table is 10, that is, pageSize ═ 10, as an example, when there is a high possibility that the 3 data records will be inserted into the same page of the maximum data table, in an extreme case, the 3 data records will be inserted into the first 7 data records of the same page at the same time. That is, when the data record of the current page is returned, the actually returned data records are the first 7 pieces of data of the current page of the C table and the 3 pieces of data of the B table and the a table, the last three pieces of data of the C table are not returned any more, and the three pieces of data need to be returned, which causes data omission.
For example, 10 data records of the current page in table C correspond to times of 10:00, 10:02, 10:03, 10:05, 10:06, 10:08, 10:09, 10:10, 10:11, 10:12, and data records of table a and table B correspond to times of 10:01, 10:04, 10:07, then the returned data after reordering should be 10:00, 10:01, 10:02, 10:03, 10:04, 10:05, 10:06, 10:07, 10:08, 10:09, since the next page will be retrieved from the next page, three data records corresponding to 10:10, 10:11, 10:12 will be missed.
In order to avoid the above situation, in the present embodiment, the actual start displacement and the actual number of the data records of each page of the maximum data table are obtained according to the paging condition of the maximum data table and the total number of the data records of the other data tables that meet the query condition.
Specifically, for clearly explaining the execution process of this step, further referring to fig. 2, fig. 2 is a specific flowchart of an embodiment of the step of sequentially retrieving actual data records of each page of the maximum data table, which includes the following steps:
step 201: and judging whether the currently acquired data record is the data record of the first page of the maximum data table.
If it is the data record of the first page of the maximum data table, step 202 is performed.
If not, step 203 is performed.
Step 202: and directly acquiring all data records of the current page.
If the current page is the first page of the maximum data table, because the data records of other data tables are not inserted before the first page, if the current page is determined to be the first page, all the data records of the current page are directly acquired.
Step 203: and determining the displacement of the last Nth data record before the current page as the actual starting displacement of the data record of the current page.
Wherein N is the total amount of data records of other data tables which meet the query condition.
In order to solve the problem that the data records of the maximum data table are missed, the data of each page of the maximum data table can be normally displayed. In this embodiment, starting from the second page of data of the largest data table, the start bit of the current page is moved forward by the same number of bits as the number of data records of other data tables, that is, the displacement of the last but N data record before the current page is determined as the actual start displacement of the data record of the current page.
Taking the data of the table A, the table B and the table C as an example, when the data of the table B is obtained, the data of the table B is moved by 3 bits from the second page to the previous page to be used as the actual initial position of the current page.
Step 204: and sequentially acquiring the latest N data records before the current page and all the data records of the current page from the actual initial displacement to obtain the actual data record of the current page.
In step 203 and 204, after determining the actual initial position of the current page, 3 pieces of data of the previous page and 10 pieces of data of the current page are sequentially obtained as the actual data record of the current page.
Specifically, as shown in table 1, table 1 is a comparison graph of the results of the original data table acquisition mode and the acquisition mode of the present embodiment.
TABLE 1
The page number is a label of the current page data of the currently acquired maximum data table. originStart is the original initial displacement, and in this table, the displacement of the first data record on page 1 is 0, and pageSize is 10, so the displacement of the first data record on page 2 is 10, and the displacement of the first data record on page 3 is 20. originSkip is the original skip number, and in the present embodiment, the total number of data records whose B table and C table satisfy the lookup condition is 3, and therefore, the original skip number is 3, and the actual skip number actualsskip is also 3. To ensure that all data in the B table can be acquired, the actual start displacement is shifted forward by 3 bits from page 2, so the actual start displacement of page 2 is displacement 7 of the last-but-one data record of page 1, the actual start position of page 3 is displacement 17 of the last-but-one data record of page 2, and the actual start position of page 4 is displacement 27 of the last-but-one data record of page 3. Correspondingly, the actual number of the obtained data records is 13 in total, starting from page 2, except that page 1 is 10, and the actual number actualLimit is the last 3 data records of the previous page and the actual 10 data records of the current page.
In accordance with the procedure, first, pageSize 10, count (a) 2, count (b) 1, and count (c) 1000 are set.
The original start displacement and the original number of hops are defined by:
int originSkip＝COUNT(A)+COUNT(B)；
int originStart＝size*(position-1)；
the actual start position and the actual number of jumps are defined by:
int actualStart＝originStart-originSkip>0originStart-originSkip:0；
int actualSkip＝originStart-actualStart；
int actualLimit＝actualSkip+size。
step 103: and merging the data records meeting the query conditions of other data tables into each page of data record of the maximum data table, and sequencing according to a set sequence to obtain a sequenced data table.
After each page of data of the maximum data table is acquired, in order to ensure that the data records of all the data tables except the maximum data table are completely returned, before the actual insertion position of the data record is not determined, in order to ensure the completeness of the data return, the data records of other data tables are respectively inserted into each page of data record of the maximum data table from the data record of the first page of the maximum data table. It is ensured that all data records in the other data tables can be inserted into the correct position.
And after the merging is finished, sorting the merged data according to a set sequence to obtain a sorted data table. Optionally, the set order is the same as the ordering rule of the plurality of data tables to be queried, such as time ordering. Preferably, the sorting is performed in a manner convenient for the user to view, such as sorting in an ascending order or a descending order of time, which is not limited herein.
Specifically, if the data of the a table, the B table, and the C table are continuously referred to, when the data records of the other data tables are inserted into the first page, the number of the data records of the first page is not changed, and therefore, the data table corresponding to the first page includes 13 reordered data records. Starting from the second page, the corresponding data table includes 16 reordered data records because the actual initial displacement is shifted forward by 3 bits.
104: a set number of data records are returned from the data table.
In an alternative embodiment, after sorting, a displacement is assigned to each data record in the data table. A set number of data records that meet the query criteria are intercepted and returned from the data table.
Optionally, the set number is the same as the paging condition of the largest database to ensure that data records in all databases can be returned.
For example, 10 data records of the first page of the C table correspond to times of 10:00, 10:02, 10:03, 10:05, 10:06, 10:08, 10:09, 10:10, 10:11, 10:12, while 10:01, 10:04, 10:07 of the data records of the A table and the B table respectively appear in the first 10 of the first page, so that the reordered data tables should have the order of 10:00, 10:01, 10:02, 10:03, 10:04, 10:05, 10:06, 10:07, 10:08, 10:09, 10:10, 10:11, 10:12, and since the terminal returns 10 data each time, the first page should return 10:00, 10:01, 10:02, 10:03, 10:04, 10:05, 10:06, 10:07, 10:08, 10: 09. Although not shown when the first page of data is displayed. Since the actual starting position of the second page to acquire the data record is the last 3, i.e. from the 8 th data record, the three data records corresponding to 10:10, 10:11, 10:12, which are not shown in the first page, are acquired again in the second page data record. And because 3 pieces of data of the B table and the A table are inserted into the first page, the data are not merged into the data table of the second page. Therefore, three data records corresponding to 10:10, 10:11, and 10:12, which are not shown in the first page, are returned in the second page. The integrity of the returned data is guaranteed.
Different from the prior art, in this embodiment, after finding the data records meeting the conditions and the number thereof from the plurality of data tables, the largest data table with the largest number is determined, the data records of other data tables are obtained, the actual initial displacement and the actual number of the data records of each page of the largest data table are obtained according to the total amount of the data records of other data tables and the paging condition of the largest data table, and the actual data records of each page of the largest data table are sequentially taken out. All data records of other data tables are obtained firstly, and then the data record of the largest data table is obtained in the paging mode, the data obtaining process is always in a one-dimensional space, the data volume obtained every time is not large, the actual calculation amount can be effectively reduced, and the query efficiency is improved. When the current page is not the first page, the actual initial displacement is moved forward by the same number of bits as the data records of other data tables, and each data record in the maximum data table can be acquired and displayed.
The data records of other data tables which meet the query conditions are merged into each page of data record of the maximum data table and are sorted according to the set sequence to obtain the sorted data table, so that each piece of data in the other data tables can be guaranteed to be acquired and displayed, and because the data records of the other data tables are small in number relative to the maximum data table, the calculation amount in sorting can be greatly reduced when merging and sorting are carried out, and the query efficiency is improved compared with the existing situation that all the data records are sorted together.
Referring to fig. 3, fig. 3 is a schematic structural diagram of an embodiment of the apparatus for querying multi-table data according to the present application.
As shown in fig. 3, the query device of the present embodiment includes a query module 301, a data acquisition module 302, a merge module 303, and a data return module 304.
The query module 301 is configured to search the data records and the number thereof meeting the query condition from the multiple data tables, and determine the data table with the largest number of data records as the largest data table.
Further, as shown in fig. 4, the query device further includes a receiving module 405.
When a user queries data through a terminal, a query request including query conditions is first sent to a query device, and correspondingly, the receiving module 405 receives the query request.
The query device can be a smart phone, a tablet personal computer, a Personal Computer (PC), or other queryable intelligent devices, such as wearable devices with a query function, such as a smart watch.
The query condition is a condition for screening data from a data table, such as information for processing data for machines between 10:00 and 11:00 a.m.
In order to obtain complete data, the query module 301 obtains source table links of a plurality of data tables according to the query condition, and screens data records and quantities meeting the query condition from the source table links according to the query condition.
Optionally, in order to ensure the validity of the obtained data record, the sorting rules of the multiple data tables are the same, for example, all the data tables are sorted according to time, and the reverse order or the sequence is not limited.
Further, after the number of data records of each data table is obtained, in order to obtain the workload when acquiring data, in the present embodiment, one maximum data table is determined according to the number of data records of each data table. For example, there are three tables, i.e., table a, table B and table C, after screening, the number of data records of table C meeting the query condition is 1000, i.e., count (C) is 1000, the number of data records of table B meeting the query condition is 1, i.e., count (B) is 1, and the number of data records of table a meeting the query condition is 2, i.e., count (a) is 2, and the table a is determined to be the largest data table by comparison. And taking out the data records of other data tables except the maximum data table A, and caching the data records.
The data obtaining module 302 is configured to determine an actual initial displacement and an actual number of data records of each page of the maximum data table according to the paging condition of the maximum data table and the total number of data records of the other data tables that meet the query condition, and sequentially extract actual data records of each page of the maximum data table.
After the query module 301 determines the maximum data table, in order to reduce the calculation amount in the query process, the data obtaining module 302 obtains the data in the maximum data table by way of paging query.
In order to ensure that the data records of all the data tables except the maximum data table are completely returned, the data records of other data tables are respectively inserted into each page of data record of the maximum data table before the actual insertion position of the data record is not determined, and the completeness of the data return is ensured.
As described above, there are many cases in which the actual insertion position of the data record of the other data table is not determined. Such as all data records appearing on the same page. By continuing the example of the above a table, B table, and C table, the C table is the maximum data table, the total number of data records of which the a table and B table satisfy the query condition is 3, and taking the example that the data record of each page of the a table is 10, that is, pageSize ═ 10, as an example, when there is a high possibility that the 3 data records will be inserted into the same page of the maximum data table, in an extreme case, the 3 data records will be inserted into the first 7 data records of the same page at the same time. That is, when the data record of the current page is returned, the actually returned data records are the first 7 pieces of data of the current page of the C table and the 3 pieces of data of the B table and the a table, the last three pieces of data of the C table are not returned any more, and the three pieces of data need to be returned, which causes data omission.
In order to avoid the above situation, in this embodiment, the data obtaining module 302 determines to obtain the actual start displacement and the actual number of each page of data record of the maximum data table according to the paging condition of the maximum data table and the total number of data records of the other data tables that meet the query condition.
Specifically, the data obtaining module 302 determines whether the current page is the first page of the largest data table.
If the current page is the first page of the largest data table, since the data records of other data tables are not inserted before the first page, if the current page is determined to be the first page, the data obtaining module 302 directly obtains all the data records of the current page.
If the current page is not the first page of the largest data table, the data obtaining module 302 determines the displacement of the last nth data record before the current page as the actual starting displacement of the data record for obtaining the current page.
Wherein N is the total amount of data records of other data tables which meet the query condition.
In order to solve the problem that the data records of the maximum data table are missed, the data of each page of the maximum data table can be normally displayed. In this embodiment, starting from the second page of data of the largest data table, the start bit of the current page is moved forward by the same number of bits as the number of data records of other data tables, that is, the displacement of the last but N data record before the current page is determined as the actual start displacement of the data record of the current page.
If the data of the table A, the table B and the table C are taken as examples continuously, when the data of the table B is obtained, the data of the table B is moved by 3 bits from the second page to the previous page to be used as the actual initial position of the current page.
Starting from the actual initial displacement, the data obtaining module 302 sequentially obtains the last N data records before the current page and all the data records of the current page, so as to obtain the actual data record of the current page. After the actual initial position of the current page is determined, 3 pieces of data of the previous page and 10 pieces of data of the current page are sequentially acquired as the actual data record of the current page.
The merging module 303 is configured to merge data records of other data tables that meet the query condition into each page of data record of the largest data table, and sort the data records according to a set order to obtain a sorted data table.
After acquiring each page of data of the maximum data table, in order to ensure that the data records of all the data tables except the maximum data table are completely returned, before the actual insertion position of the data record is not determined, in order to ensure the integrity of the data return, the merging module 303 inserts the data records of the other data tables into each page of data record of the maximum data table, respectively, from the data record of the first page of the maximum data table. It is ensured that all data records in the other data tables can be inserted into the correct position.
After the merging is completed, the merging module 303 sorts the merged data according to a set order, so as to obtain a sorted data table. Optionally, the set order is the same as the ordering rule of the plurality of data tables to be queried, such as time ordering. Preferably, the sorting is performed in a manner convenient for the user to view, such as sorting in an ascending order or a descending order of time, which is not limited herein.
Specifically, if the data of the a table, the B table, and the C table are continuously referred to, when the data records of the other data tables are inserted into the first page, the number of the data records of the first page is not changed, and therefore, the data table corresponding to the first page includes 13 reordered data records. Starting from the second page, the corresponding data table includes 16 reordered data records because the actual initial displacement is shifted forward by 3 bits.
The data return module 304 is used to return a set number of data records from the data table.
In an alternative embodiment, after sorting, the merge module 303 assigns a displacement to each data record in the data table. The data return module 304 intercepts and returns a set number of data records from the data table that meet the query criteria.
Optionally, the set number is the same as the paging condition of the largest database to ensure that data records in all databases can be returned.
Different from the prior art, in this embodiment, after the query module finds the data records and the number thereof meeting the conditions from the plurality of data tables, the maximum data table with the largest number is determined, the data acquisition module acquires the data records of other data tables, acquires the actual initial displacement and the actual number of the data records of each page of the maximum data table according to the total amount of the data records of other data tables and the paging condition of the maximum data table, and sequentially extracts the actual data records of each page of the maximum data table. All data records of other data tables are obtained firstly, and then the data record of the largest data table is obtained in the paging mode, the data obtaining process is always in a one-dimensional space, the data volume obtained every time is not large, the actual calculation amount can be effectively reduced, and the query efficiency is improved. When the current page is not the first page, the actual initial displacement is moved forward by the same number of bits as the data records of other data tables, and each data record in the maximum data table can be acquired and displayed.
The merging module merges the data records of other data tables meeting the query condition into each page of data record of the maximum data table, and sorts the data records according to the set sequence to obtain the sorted data table, so that each piece of data in the other data tables can be ensured to be acquired and displayed, and because the data records of the other data tables are smaller in number relative to the maximum data table, the calculation amount in sorting can be greatly reduced when merging and sorting are carried out, compared with the existing situation that all the data records are sorted together, and the query efficiency is improved.
Referring to fig. 5, fig. 5 is a schematic structural diagram of an embodiment of a query terminal for multi-table data according to the present application. The acquisition terminal 50 of the present embodiment includes a processor 501 and a human-computer interaction circuit 502 coupled to each other. The human-computer interaction circuit 502 receives user instructions and returns data records. The processor 501 is configured to execute the multi-table data query method according to any of the above embodiments.
Referring to fig. 6, the present application further provides a schematic structural diagram of an embodiment of a memory device. In this embodiment, the storage device 60 stores processor-executable computer instructions 61, and the computer instructions 61 are used for executing the method in the above-described embodiment.
The storage device 60 may be a medium that can store computer instructions, such as a usb disk, a removable hard disk, a Read-Only Memory (ROM), a Random Access Memory (RAM), a magnetic disk or an optical disk, or may be a server that stores the computer instructions, and the server may send the stored computer instructions to other devices for operation or may self-operate the stored computer instructions.
In the several embodiments provided in the present application, it should be understood that the disclosed method and apparatus may be implemented in other ways. For example, the above-described apparatus embodiments are merely illustrative, and for example, a division of a module or a unit is merely a logical division, and an actual implementation may have another division, for example, a plurality of units or components may be combined or integrated into another system, or some features may be omitted, or not executed. In addition, the shown or discussed mutual coupling or direct coupling or communication connection may be an indirect coupling or communication connection through some interfaces, devices or units, and may be in an electrical, mechanical or other form.
Units described as separate parts may or may not be physically separate, and parts displayed as units may or may not be physical units, may be located in one place, or may be distributed on a plurality of network units. Some or all of the units can be selected according to actual needs to achieve the purpose of the embodiment.
In addition, functional units in the embodiments of the present application may be integrated into one processing unit, or each unit may exist alone physically, or two or more units are integrated into one unit. The integrated unit can be realized in a form of hardware, and can also be realized in a form of a software functional unit.
The integrated unit, if implemented in the form of a software functional unit and sold or used as a stand-alone product, may be stored in a computer readable storage medium. Based on such understanding, the technical solution of the present application may be substantially implemented or contributed to by the prior art, or all or part of the technical solution may be embodied in a software product, which is stored in a storage medium and includes instructions for causing a computer device (which may be a personal computer, a server, a network device, or the like) or a processor (processor) to execute all or part of the steps of the method according to the embodiments of the present application. And the aforementioned storage medium includes: a U-disk, a removable hard disk, a Read-Only Memory (ROM), a Random Access Memory (RAM), a magnetic disk or an optical disk, and other various media capable of storing program codes.
The above embodiments are merely examples and are not intended to limit the scope of the present disclosure, and all modifications, equivalents, and flow charts using the contents of the specification and drawings of the present disclosure or those directly or indirectly applied to other related technical fields are intended to be included in the scope of the present disclosure.
Claims (10)
1. A query method for multi-table data, the query method comprising:
searching data records meeting the query conditions and the number of the data records from a plurality of data tables, and determining the data table with the maximum number of the data records as a maximum data table;
determining to obtain the actual initial displacement and the actual number of each page of data record of the maximum data table according to the paging condition of the maximum data table and the total number of data records which meet the query condition and are in other data tables except the maximum data table in the plurality of data tables, and sequentially taking out the actual data record of each page of the maximum data table;
merging the data records of the other data tables which accord with the query condition into each page of data record of the maximum data table, and sorting according to a set sequence to obtain a sorted data table;
and returning a set number of data records from the sorted data table.
2. The query method according to claim 1, wherein the step of determining to obtain the actual start displacement and the actual number of the data records of each page of the maximum data table according to the paging condition of the maximum data table and the total number of the data records of the other data tables except the maximum data table, which satisfy the query condition, and sequentially fetching the actual data records of each page of the maximum data table specifically comprises:
judging whether the currently acquired data record is the data record of the first page of the maximum data table;
if the data record is not the data record of the first page, determining the displacement of the latest Nth data record before the current page as the actual initial displacement of the data record of the current page; wherein N is the total amount of data records of other data tables meeting the query condition;
and sequentially acquiring the latest N data records before the current page and all the data records of the current page from the actual initial displacement to obtain the actual data record of the current page.
3. The query method of claim 2, further comprising:
if the currently acquired data record is the data record of the first page of the maximum data table;
and directly acquiring all data records of the current page.
4. The query method according to any one of claims 1 to 3, wherein the ordering rules of the plurality of data tables are the same.
5. The method according to claim 4, wherein the setting order is the same as the sort rule.
6. The method according to claim 4, wherein the step of merging the data records of the other data tables that meet the query condition into each page of data record of the largest data table and sorting the data records according to a set order to obtain a sorted data table further comprises:
distributing displacement for each data record in the sorted data table;
the step of returning a set number of data records from the sorted data table comprises:
and intercepting and returning the data records with the set number which accord with the query condition from the sorted data table.
7. The method according to claim 1, wherein the step of searching the data records meeting the query condition and the number thereof from the plurality of data tables, and determining the data table with the largest number of data records as the largest data table comprises:
acquiring a query instruction; wherein the query instruction includes the query condition;
based on the query instruction, acquiring source table links of the plurality of data tables;
the step of searching the data records meeting the query condition and the number thereof from the plurality of data tables and determining the data table with the maximum number of data records as the maximum data table comprises the following steps:
and searching the data records meeting the query condition and the quantity thereof from the source table link of the plurality of data tables, and determining the data table with the maximum quantity of the data records as the maximum data table.
8. An apparatus for querying multi-table data, the apparatus comprising: a query module, a data acquisition module, a merging module and a data return module,
the query module is used for searching data records meeting query conditions and the number of the data records from a plurality of data tables and determining the data table with the largest number of the data records as a maximum data table;
the data acquisition module is used for determining to acquire the actual initial displacement and the actual number of each page of data record of the maximum data table according to the paging condition of the maximum data table and the total number of data records which meet the query condition and are in other data tables except the maximum data table, and sequentially taking out the actual data records of each page of the maximum data table;
the merging module is used for merging the data records of the other data tables which meet the query condition into each page of data record of the maximum data table, and sorting the data records according to a set sequence to obtain a sorted data table;
and the data return module is used for returning data records with set quantity from the sorted data table.
9. A query terminal for multi-table data, the query terminal comprising:
a processor and a human-machine interaction control circuit coupled to each other, the processor being operative to implement the query method of any one of claims 1-7 in cooperation with the human-machine interaction control circuit.
10. A computer storage medium, characterized in that the computer storage medium has stored thereon program data which, when executed by a processor, implements the query method according to any one of claims 1-7.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201811082460.8A CN110928900B (en) | 2018-09-17 | 2018-09-17 | Multi-table data query method, device, terminal and computer storage medium |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201811082460.8A CN110928900B (en) | 2018-09-17 | 2018-09-17 | Multi-table data query method, device, terminal and computer storage medium |
Publications (2)
| Publication Number | Publication Date |
|---|---|
| CN110928900A CN110928900A (en) | 2020-03-27 |
| CN110928900B true CN110928900B (en) | 2021-02-19 |
Family
ID=69855822
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN201811082460.8A Active CN110928900B (en) | 2018-09-17 | 2018-09-17 | Multi-table data query method, device, terminal and computer storage medium |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN110928900B (en) |
Families Citing this family (3)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN111488370B (en) * | 2020-04-02 | 2023-09-12 | 杭州迪普科技股份有限公司 | List paging quick response system and method |
| CN112732761B (en) * | 2021-01-13 | 2022-08-23 | 青岛海信网络科技股份有限公司 | Data collision method and device |
| CN112749187A (en) * | 2021-02-09 | 2021-05-04 | 北京宇信科技集团股份有限公司 | Data query method and device, electronic equipment and computer storage medium |
Citations (9)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN103605750A (en) * | 2013-11-22 | 2014-02-26 | 厦门雅迅网络股份有限公司 | Rapid distributed data paging method |
| CN103778135A (en) * | 2012-10-18 | 2014-05-07 | 厦门雅迅网络股份有限公司 | Method for distribution storage and paging querying of real-time data |
| CN105468644A (en) * | 2014-09-10 | 2016-04-06 | 阿里巴巴集团控股有限公司 | Method and device for performing query in database |
| CN105653537A (en) * | 2014-11-13 | 2016-06-08 | 阿里巴巴集团控股有限公司 | Paging query method and device for database application system |
| CN107193822A (en) * | 2016-03-14 | 2017-09-22 | 阿里巴巴集团控股有限公司 | For the method for paging query, device and equipment |
| CN107315753A (en) * | 2016-04-27 | 2017-11-03 | 北京京东尚科信息技术有限公司 | Across the paging method and device of multiple database |
| CN107633097A (en) * | 2017-10-16 | 2018-01-26 | 马上消费金融股份有限公司 | The dynamic capacity-expanding method and system of a kind of database table |
| CN107798135A (en) * | 2017-11-22 | 2018-03-13 | 阿里巴巴集团控股有限公司 | Paging query method and device and electronic equipment |
| CN108090086A (en) * | 2016-11-21 | 2018-05-29 | 迈普通信技术股份有限公司 | Paging query method and device |
Family Cites Families (2)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN104268295B (en) * | 2014-10-24 | 2018-07-06 | 迈普通信技术股份有限公司 | A kind of data query method and device |
| US20180089282A1 (en) * | 2016-09-23 | 2018-03-29 | Microsoft Technology Licensing, Llc. | User-driven paging |
-
2018
- 2018-09-17 CN CN201811082460.8A patent/CN110928900B/en active Active
Patent Citations (9)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN103778135A (en) * | 2012-10-18 | 2014-05-07 | 厦门雅迅网络股份有限公司 | Method for distribution storage and paging querying of real-time data |
| CN103605750A (en) * | 2013-11-22 | 2014-02-26 | 厦门雅迅网络股份有限公司 | Rapid distributed data paging method |
| CN105468644A (en) * | 2014-09-10 | 2016-04-06 | 阿里巴巴集团控股有限公司 | Method and device for performing query in database |
| CN105653537A (en) * | 2014-11-13 | 2016-06-08 | 阿里巴巴集团控股有限公司 | Paging query method and device for database application system |
| CN107193822A (en) * | 2016-03-14 | 2017-09-22 | 阿里巴巴集团控股有限公司 | For the method for paging query, device and equipment |
| CN107315753A (en) * | 2016-04-27 | 2017-11-03 | 北京京东尚科信息技术有限公司 | Across the paging method and device of multiple database |
| CN108090086A (en) * | 2016-11-21 | 2018-05-29 | 迈普通信技术股份有限公司 | Paging query method and device |
| CN107633097A (en) * | 2017-10-16 | 2018-01-26 | 马上消费金融股份有限公司 | The dynamic capacity-expanding method and system of a kind of database table |
| CN107798135A (en) * | 2017-11-22 | 2018-03-13 | 阿里巴巴集团控股有限公司 | Paging query method and device and electronic equipment |
Non-Patent Citations (2)
| Title |
|---|
| Paging query optimization of massive data in oracle 10g database;Fengdong Sun等;《IEEE》;20110804;第2388-2391页 * |
| 基于MySql多表分页查询优化技术;韩兵等;《计算机系统应用》;20160831;第25卷(第8期);第171-175页 * |
Also Published As
| Publication number | Publication date |
|---|---|
| CN110928900A (en) | 2020-03-27 |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| CN110321344B (en) | Information query method and device for associated data, computer equipment and storage medium | |
| US8924373B2 (en) | Query plans with parameter markers in place of object identifiers | |
| US6925462B2 (en) | Database management system, and query method and query execution program in the database management system | |
| CN110928900B (en) | Multi-table data query method, device, terminal and computer storage medium | |
| WO2020087082A1 (en) | Trace and span sampling and analysis for instrumented software | |
| US11468093B2 (en) | Synopsis based advanced partition elimination | |
| CN105653537A (en) | Paging query method and device for database application system | |
| US10275486B2 (en) | Multi-system segmented search processing | |
| US20160328445A1 (en) | Data Query Method and Apparatus | |
| CN109299101B (en) | Data retrieval method, device, server and storage medium | |
| US20070067327A1 (en) | Performing recursive database operators | |
| CN113553339B (en) | Data query method, middleware, electronic device and storage medium | |
| CN110083579A (en) | Incremental data synchronous method, apparatus, computer equipment and computer storage medium | |
| CN111339171A (en) | Data query method, device and equipment | |
| US20220358178A1 (en) | Data query method, electronic device, and storage medium | |
| CN112613271A (en) | Data paging method and device, computer equipment and storage medium | |
| CN112612827A (en) | Database paging query method and device, computer equipment and storage medium | |
| CN110309122A (en) | Obtain method, apparatus, server and the storage medium of incremental data | |
| CN110471935B (en) | Data operation execution method, device, equipment and storage medium | |
| CN116783588A (en) | Column technique for large metadata management | |
| CN113568924A (en) | Data processing method and device, electronic equipment and storage medium | |
| CN110222046B (en) | List data processing method, device, server and storage medium | |
| WO2023197865A1 (en) | Information storage method and apparatus | |
| CN111046106A (en) | Cache data synchronization method, device, equipment and medium | |
| CN109063215B (en) | Data retrieval method and device |
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 |