A data query method, system and device for multi-table association

Abstract

The application discloses a kind of multi-table association data query method, system and equipment, it is related to data query field.The method includes: constructing index field, and the index field is stored to multiple tables;Filter the data in multiple tables according to the order screening by the same index field;According to the primary key of multiple tables, the filtered data is filtered.The application can improve the query efficiency when multiple tables are associated.

Description

Technical Field

[0001] This invention relates to the field of data query technology, and in particular to a data query method, system and device for multi-table association. Background Technology

[0002] Currently, the internet has become an indispensable part of people's lives. With the rapid growth in user numbers and usage frequency, the volume of data related to business operations and user behavior is also constantly increasing, posing a significant challenge to big data statistics. To better support business development, there is a strong demand for high-performance, real-time big data statistics and analysis.

[0003] In the field of big data, query efficiency is usually improved by upgrading server configuration, CDN acceleration, data pre-storage, slicing new and old data, and expanding index fields. However, these methods have problems such as high cost and poor user experience. Summary of the Invention

[0004] The purpose of this invention is to provide a data query method, system, and device for multi-table association, which improves the efficiency of multi-table association query scenarios without affecting user experience or incurring additional costs.

[0005] To achieve the above objectives, the present invention provides the following solution:

[0006] A method for querying data involving multiple table joins, including:

[0007] Construct index fields and store them in multiple tables;

[0008] Filter data from multiple tables sequentially using the same index field;

[0009] Filter the selected data based on the primary and foreign keys of multiple tables.

[0010] Optionally, an index field is constructed and the index field is stored in multiple tables, specifically including:

[0011] Identify the multiple tables to be queried; the tables must be queried in a specific order.

[0012] Retrieve the index field from the first table;

[0013] Based on the index relationship between the first table and the remaining tables, the indexed fields are stored in the remaining tables.

[0014] Optionally, multiple tables are determined by specific business requirements.

[0015] This invention also provides a data query system with multi-table association, comprising:

[0016] The building module is used to build index fields and store the index fields in multiple tables;

[0017] A filtering module is used to filter data from multiple tables sequentially using the same index field;

[0018] The filtering module is used to filter the selected data based on the primary and foreign keys of multiple tables.

[0019] Optionally, the building module specifically includes:

[0020] The "determine unit" is used to identify the multiple tables to be queried; these tables have a specific order.

[0021] The retrieval unit is used to retrieve the index field from the first table;

[0022] A storage unit is used to store the index field in the remaining tables according to the index relationship between the first table and the remaining tables.

[0023] Optionally, multiple tables are determined by specific business requirements.

[0024] The present invention also provides an electronic device, including a memory and a processor, wherein the memory is used to store a computer program, and the processor runs the computer program to enable the electronic device to perform the above-described data query method for multi-table association.

[0025] The present invention also provides a computer-readable storage medium storing a computer program that, when executed by a processor, implements the above-described data query method for multi-table association.

[0026] According to specific embodiments provided by the present invention, the present invention discloses the following technical effects:

[0027] This invention, when performing join queries, first uses an index field to retrieve preliminary results in the second or Nth table, obtaining a small amount of initial data. This preliminary result data is then joined and filtered with the preliminary results data from the first table to obtain the final result data. This avoids directly using the full data from the second table and the preliminary results from the first table for joining and filtering, thus improving the query efficiency when performing join queries on multiple tables. Attached Figure Description

[0028] To more clearly illustrate the technical solutions in the embodiments of the present invention or the prior art, the drawings used in the embodiments will be briefly introduced below. Obviously, the drawings described below are only some embodiments of the present invention. For those skilled in the art, other drawings can be obtained based on these drawings without creative effort.

[0029] Figure 1A flowchart of a multi-table join data query method provided in Embodiment 1 of the present invention;

[0030] Figure 2 This is a schematic diagram illustrating a data query using the multi-table association data query method provided by this invention. Detailed Implementation

[0031] The technical solutions of the embodiments of the present invention will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only some embodiments of the present invention, and not all embodiments. Based on the embodiments of the present invention, all other embodiments obtained by those skilled in the art without creative effort are within the scope of protection of the present invention.

[0032] The purpose of this invention is to provide a data query method, system, and device for multi-table association, thereby improving the query efficiency when performing association queries on multiple tables.

[0033] To make the above-mentioned objects, features and advantages of the present invention more apparent and understandable, the present invention will be further described in detail below with reference to the accompanying drawings and specific embodiments.

[0034] Example 1

[0035] Embodiment 1 of the present invention provides a data query method involving multiple table joins. For example... Figure 1 As shown, the method includes the following steps:

[0036] S1: Construct an index field and store the index field in multiple tables.

[0037] Step S1 specifically includes:

[0038] (1) Determine the number of tables N to be queried (N is greater than or equal to 2).

[0039] (2) Retrieve the index fields in Table 1, where Table 1 is determined by the specific business logic. The index fields are not necessarily unique and there can be multiple fields.

[0040] Tables 1 and 2 are determined by specific business requirements. For example, Table 1 is the student basic information table, and Table 2 is the student grade table. To query Zhang San's grade, one typically first finds the corresponding person in the basic information table, and then uses that person's ID to find their grade value in the grade table. Therefore, Tables 1 and 2 have a specific order.

[0041] (3) Store the index fields in Table 1 into the remaining Tables 2 to n.

[0042] Store the indexed fields in the remaining tables based on the index relationship between Table 1 and the other tables.

[0043] When writing data to an index field (such as index field A) in Table 1, the same value is also written to the corresponding index field A in the other tables 2 through n. The fields are of the same type. This is equivalent to field A existing in both Table 1 and Table n. Furthermore, field A is both a database index for Table 1 and a database index for Table n.

[0044] S2: Filter data from multiple tables in sequence using the same index field.

[0045] like Figure 2 As shown, when querying data, the data in Table 1 is filtered using the index field A as a condition, resulting in preliminary result data 1. Field A is only one query condition; if there are multiple conditions, the same logic applies. This invention simply uses field A as an example. Similarly, the data in Table n is filtered using the index field A, resulting in preliminary result data n.

[0046] S3: Filter the selected data based on the primary and foreign keys of multiple tables.

[0047] The final required result data is obtained by filtering the result data from Table 1 and Table n.

[0048] The filtering process is as follows:

[0049] After obtaining the result data, the next step is to filter it using the primary and foreign keys (or mapping fields between the two tables) of Table 1 and Table 2. For example, querying "Zhang San" in Table 1 yields the first set of preliminary result data, and querying "Zhang San" in Table 2 yields the second set of preliminary result data. However, this is based on a name condition, and there are many people named "Zhang San," so it is necessary to filter the data using the primary and foreign keys of Table 1 and Table 2 to obtain the corresponding result data. Below is an example demonstration:

[0050] Table 1: (Basic Information Table)

[0051] ID ID number Name gender 1 362229198011116666 Zhang San male 2 362229198011117777 Li Si female 3 362229198011118888 Zhang San female 4 ～～～ ～～～ ～～～

[0052] Table 2: Student Grade Sheet

[0053]

[0054]

[0055] The "Name" field in Table 2 is the same as field A described above. If Table 2 did not have a "Name" field, we would first have to search for "Zhang San" in Table 1 to obtain his ID number, and then use that ID number to iterate through Table 2 to find the corresponding scores for each subject. Now, by adding the "Name" field to Table 2, we can perform searches in both Table 1 and Table 2 simultaneously, and then use the data from Table 1 and the filtered data from Table 2 for the next step of the search.

[0056] The method provided by this invention is applicable to scenarios involving join queries across multiple tables (two or more). When writing values ​​to the index fields of the first table, these values ​​are also repeatedly written to the second and Nth tables. During a join query, the index fields are first used to retrieve preliminary results in the second or Nth table, yielding a small amount of initial data. This preliminary result is then compared with the initial results from the first table to obtain the final result. This avoids directly using the full data from the second table with the preliminary results from the first table for filtering, thus improving query efficiency when performing join queries across multiple tables.

[0057] Example 2

[0058] In order to implement the method corresponding to Embodiment 1 above and achieve the corresponding functions and technical effects, a data query system with multi-table association is provided below.

[0059] The system includes:

[0060] A construction module is used to construct index fields and store the index fields in multiple tables. Specifically, it includes: a determination unit for determining the multiple tables to be queried, wherein the tables are in a specific order; a retrieval unit for retrieving the index fields from the first table; and a storage unit for storing the index fields in the remaining tables according to the index relationships between the first table and the other tables.

[0061] A filtering module is used to filter data from multiple tables sequentially using the same index field.

[0062] The filtering module is used to filter the selected data based on the primary and foreign keys of multiple tables.

[0063] Example 3

[0064] Embodiment 3 of the present invention provides an electronic device, including a memory and a processor. The memory is used to store a computer program, and the processor runs the computer program to enable the electronic device to perform the data query method of multi-table association provided in Embodiment 1.

[0065] In practical applications, the aforementioned electronic devices can be servers.

[0066] In practical applications, electronic devices include: at least one processor, memory, bus, and communication interface.

[0067] The processor, communication interface, and memory communicate with each other via a communication bus.

[0068] A communication interface is used to communicate with other devices.

[0069] The processor is used to execute programs, specifically the methods described in the above embodiments.

[0070] Specifically, the program may include program code, which includes computer operation instructions.

[0071] The processor may be a central processing unit (CPU), an application-specific integrated circuit (ASIC), or one or more integrated circuits configured to implement embodiments of the present invention. The electronic device includes one or more processors, which may be processors of the same type, such as one or more CPUs; or they may be processors of different types, such as one or more CPUs and one or more ASICs.

[0072] Memory is used to store programs. Memory may include high-speed RAM, and may also include non-volatile memory, such as at least one disk drive.

[0073] Example 4

[0074] Based on the description of Embodiment 3, Embodiment 4 of the present invention provides a storage medium on which a computer program is stored. The computer program can be executed by a processor to implement the data query method of multi-table association in Embodiment 1.

[0075] The multi-table join data query system provided in Embodiment 2 of this invention exists in various forms, including but not limited to:

[0076] (1) Mobile communication devices: These devices are characterized by their mobile communication capabilities and primarily aim to provide voice and data communication. These terminals include: smartphones (e.g., iPhones), multimedia phones, feature phones, and low-end phones, etc.

[0077] (2) Ultra-mobile personal computer devices: These devices fall under the category of personal computers, possessing computing and processing capabilities, and generally also have mobile internet access capabilities. These terminals include PDAs, MIDs, and UMPCs, such as the iPad.

[0078] (3) Portable entertainment devices: These devices can display and play multimedia content. This category includes: audio and video players (such as iPods), handheld game consoles, e-books, as well as smart toys and portable car navigation devices.

[0079] (4) Other electronic devices with data interaction functions.

[0080] Specific embodiments of the subject matter have now been described. Other embodiments are within the scope of the appended claims. In some cases, the actions described in the claims can be performed in a different order and still achieve the desired result. Furthermore, the processes depicted in the drawings do not necessarily require a specific or sequential order to achieve the desired result. In some embodiments, multitasking and parallel processing can be advantageous.

[0081] The systems, devices, modules, or units described in the above embodiments can be implemented by computer chips or entities, or by products with certain functions. A typical implementation device is a computer. Specifically, a computer can be, for example, a personal computer, laptop computer, cellular phone, camera phone, smartphone, personal digital assistant, media player, navigation device, email device, game console, tablet computer, wearable device, or any combination of these devices.

[0082] For ease of description, the above apparatus is described by dividing it into various functional units. Of course, in implementing this invention, the functions of each unit can be implemented in one or more software and / or hardware components. Those skilled in the art will understand that embodiments of the present invention can be provided as methods, systems, or computer program products. Therefore, the present invention can take the form of a completely hardware embodiment, a completely software embodiment, or an embodiment combining software and hardware aspects. Furthermore, the present invention can take the form of a computer program product implemented on one or more computer-usable storage media (including but not limited to disk storage, CD-ROM, optical storage, etc.) containing computer-usable program code.

[0083] This invention is described with reference to flowchart illustrations and / or block diagrams of methods, apparatus (systems), and computer program products according to embodiments of the invention. It will be understood that each block of the flowchart illustrations and / or block diagrams, and combinations of blocks in the flowchart illustrations and / or block diagrams, can be implemented by computer program instructions. These computer program instructions can be provided to a processor of a general-purpose computer, special-purpose computer, embedded processor, or other programmable data processing apparatus to produce a machine, such that the instructions, which execute via the processor of the computer or other programmable data processing apparatus, generate instructions for implementing the flowchart illustrations and / or block diagrams. Figure 1 One or more processes and / or boxes Figure 1 A device that provides the functions specified in one or more boxes.

[0084] These computer program instructions may also be stored in a computer-readable storage medium that can direct a computer or other programmable data processing device to function in a particular manner, such that the instructions stored in the computer-readable storage medium produce an article of manufacture including instruction means, which are implemented in a process Figure 1 One or more processes and / or boxes Figure 1 The function specified in one or more boxes.

[0085] These computer program instructions may also be loaded onto a computer or other programmable data processing equipment to cause a series of operational steps to be performed on the computer or other programmable equipment to produce a computer-implemented process, thereby providing instructions that execute on the computer or other programmable equipment for implementing the process. Figure 1 One or more processes and / or boxes Figure 1 The steps of the function specified in one or more boxes.

[0086] In a typical configuration, a computing device includes one or more processors (CPU), input / output interfaces, network interfaces, and memory.

[0087] Memory may include non-persistent storage in computer-readable media, such as random access memory (RAM) and / or non-volatile memory, such as read-only memory (ROM) or flash RAM. Memory is an example of computer-readable media.

[0088] Computer-readable media include both permanent and non-permanent, removable and non-removable media that can store information by any method or technology. Information can be computer-readable instructions, data structures, modules of programs, or other data. Examples of computer storage media include, but are not limited to, phase-change memory (PRAM), static random access memory (SRAM), dynamic random access memory (DRAM), other types of random access memory (RAM), read-only memory (ROM), electrically erasable programmable read-only memory (EEPROM), flash memory or other memory technologies, CD-ROM, digital versatile optical disc (DVD) or other optical storage, magnetic tape, magnetic disk storage or other magnetic storage devices, or any other non-transferable medium that can be used to store information accessible by a computing device. As defined herein, computer-readable media does not include transient computer-readable media, such as modulated data signals and carrier waves.

[0089] It should also be noted that the terms "comprising," "including," or any other variations thereof are intended to cover non-exclusive inclusion, such that a process, method, article, or apparatus that comprises a list of elements includes not only those elements but also other elements not expressly listed, or elements inherent to such a process, method, article, or apparatus. Without further limitation, an element defined by the phrase "comprising one..." does not exclude the presence of other identical elements in the process, method, article, or apparatus that includes said element.

[0090] This invention can be described in the general context of computer-executable instructions that are executed by a computer, such as program modules.

[0091] Generally, program modules include routines, programs, objects, components, data structures, etc., that perform specific transactions or implement specific abstract data types. This invention can also be practiced in distributed computing environments where transactions are performed by remote processing devices connected via a communication network. In distributed computing environments, program modules can reside in local and remote computer storage media, including storage devices.

[0092] The various embodiments in this specification are described in a progressive manner, with each embodiment focusing on its differences from other embodiments. Similar or identical parts between embodiments can be referred to interchangeably. For the systems disclosed in the embodiments, since they correspond to the methods disclosed in the embodiments, the descriptions are relatively simple; relevant parts can be referred to the method section.

[0093] This document uses specific examples to illustrate the principles and implementation methods of the present invention. The descriptions of the above embodiments are only for the purpose of helping to understand the method and core ideas of the present invention. Furthermore, those skilled in the art will recognize that, based on the ideas of the present invention, there will be changes in the specific implementation methods and application scope. Therefore, the content of this specification should not be construed as a limitation of the present invention.

Claims

1. A data query method involving multi-table joins, characterized in that, include: Constructing index fields and storing the index fields in multiple tables; specifically including: determining the multiple tables to be queried; the multiple tables have a specific order; obtaining the index fields from the first table; and storing the index fields in the remaining tables according to the index relationship between the first table and the remaining tables. Filter data from multiple tables sequentially using the same index field; Filter the selected data based on the primary and foreign keys of multiple tables.

2. The data query method for multi-table association according to claim 1, characterized in that, Multiple tables are determined by specific business requirements.

3. A data query system with multi-table joins, characterized in that, include: A construction module is used to construct index fields and store the index fields in multiple tables; specifically, it includes: a determination unit, used to determine the multiple tables to be queried; the multiple tables have a specific order; an acquisition unit, used to acquire the index fields from the first table; and a storage unit, used to store the index fields in the remaining tables according to the index relationship between the first table and the remaining tables. A filtering module is used to filter data from multiple tables sequentially using the same index field; The filtering module is used to filter the selected data based on the primary and foreign keys of multiple tables.

4. The data query system with multi-table association according to claim 3, characterized in that, Multiple tables are determined by specific business requirements.

5. An electronic device, characterized in that, The device includes a memory and a processor, the memory being used to store a computer program, and the processor running the computer program to cause the electronic device to perform the data query method for multi-table association as described in any one of claims 1-2.

6. A computer-readable storage medium, characterized in that, It stores a computer program that, when executed by a processor, implements the data query method for multi-table association as described in any one of claims 1-2.

Images