Relational query method and system based on relational database

Abstract

The invention discloses an association query method and system based on a relational database. The method comprises the following steps: acquiring a query request of a requester; determining a base point in the query request; executing initial query of the root table or the service table to obtain an initial current query result; then determining an associated query base point; then executing an associated query; judging whether the query result of the associated query meets the query requirement of the requester or not: if the query result of the associated query can meet the query requirement of the requester, extracting root table target information required by the requester from the root table, or extracting service target information required by the requester from the service table; if the query result of the associated query cannot meet the query requirement of the requester, repeatedly executing the associated query until the query result can meet the query requirement of the requester; and finally returning the root table target information or the service target information required by the requester to the requester. According to the method, the data association query in the relational database can be efficiently realized.

Description

Technical Field

[0001] The present invention relates to the technical field of data processing, and in particular to an association query method and system based on a relational database. Background Art

[0002] With the development of information technology, data types are constantly enriched, and the amount of data is growing explosively. Information association query based on relational databases has become a basic requirement for data retrieval. However, the use of multi-table association and joint query to achieve association query consumes a lot of computer performance and has low query efficiency. Hardware resources must be continuously expanded to improve query efficiency. In addition, although optimization through SQL optimization, partition table technology, index technology and other methods can improve efficiency to a certain extent, the improvement is limited and is constrained by the bottleneck of database equipment resource performance, making it difficult to completely solve this problem.

[0003] In the prior art, there are mainly the following ways to deal with the challenge of database association query:

[0004] (1) Distributed search engine technology: This solution processes and stores data on multiple data nodes, and uses big data technologies such as word segmentation and distributed computing to improve query efficiency. This solution requires multiple servers, and distributed databases cannot be applied to traditional relational database scenarios. For example, the relevant patent is CN117743506A

[0005] A data association query method and system based on natural language, CN117762920A a multi-table association query method and device based on Bitmap, and CN115344612A a multi-source data association query method, device and electronic equipment.

[0006] (2) Multiple data node storage and calculation: This method is applicable to the application scenario of association query of multiple computing nodes by distributing and storing the first data table and the second data table in multiple computing nodes. For example, CN117076465A, a data association query method and related equipment, proposes such a solution.

[0007] (3) Fields associated with fact tables and dimension tables: This solution selects fields on the dimension table as fields associated with the fact table based on the basic fields, obtains the values ​​of the associated fields through the cached data retrieval formula, and finally combines these values ​​with the result set to obtain the final query result. This patent is mainly used for data drilling in the field of report analysis, such as CN102521416A data association query method and data association query device.

[0008] However, the first two methods require the use of multiple devices for distributed computing, and the third method is only suitable for detailed data drilling for report analysis. It can be seen that the efficiency of associated queries in these methods is not high. Summary of the invention

[0009] The technical problem to be solved by the present invention is to solve the above-mentioned deficiencies in the prior art and propose a method and system for associative query based on a relational database. The method can efficiently implement data associative query in a relational database.

[0010] In a first aspect, the present invention provides an association query method based on a relational database, the method comprising the following steps:

[0011] Step S1: Obtain the query request from the requesting party;

[0012] Wherein, the query request is a basic information query request or a business information query request;

[0013] Step S2: Determine the base point in the query request;

[0014] Step S3: According to the base point in the query request, perform an initial query of the root table or the business table to obtain an initial current query result;

[0015] Step S4: Determine the associated query base point according to the current query result;

[0016] Step S5: Execute the associated query according to the associated query base point;

[0017] Step S6: Determine whether the query result of the associated query meets the query requirement of the requesting party:

[0018] If the query result of the associated query can meet the query requirement of the requester, then the root table target information required by the requester is extracted from the root table, or the business target information required by the requester is extracted from the business table; if the query result of the associated query cannot meet the query requirement of the requester, then steps S4 to S6 are repeated until the query result of the associated query can meet the query requirement of the requester;

[0019] Step S7: returning the root table target information or business target information required by the requesting party to the requesting party to complete the association query based on the relational database;

[0020] Among them, the initial query and the associated query both include one or more query methods, and the query method is precise query, and / or homophonic query, and / or pinyin query, and / or fuzzy query.

[0021] Furthermore, before the step S3, the method further includes a step S0;

[0022] Step S0: constructing a root table and a service table; and maintaining an association relationship between the root table and the service table.

[0023] Furthermore, the root table is constructed, which specifically includes the following steps:

[0024] Step A1: Determine the root table type based on the classification of people, places, things, objects, and organizations;

[0025] The root table types are divided into personnel element root table, address element root table, event element root table, item element root table, and organization element root table;

[0026] Step A2: According to the determined root table type, determine the root fields and root table data types required by the root table;

[0027] Step A3: Set the basic element data content according to the root fields and root table data types required by the root table;

[0028] The basic element data content includes root data item information, root association fields, and root element data ID fields; the root element data ID field is a unique field constraint;

[0029] Step A4: According to the root table type, the root field, the root table data type, and the root element data content, write a corresponding database query statement to obtain the root table.

[0030] Furthermore, constructing a business table specifically includes the following steps:

[0031] Step B1: Determine the business table type based on the classification of people, places, things, objects, and organizations;

[0032] The business table types are divided into personnel element business table, address element business table, event element business table, item element business table, and organization element business table;

[0033] Step B2: According to the determined business table type, determine the business fields and business data types required by the business table;

[0034] Step B3: Set the business element data content according to the fields and business data types required by the business table;

[0035] The business element data content includes business data item information, business association fields, and business element data ID fields;

[0036] Step B4: Compile a corresponding database query statement according to the business table type, the business field, the business data type, and the business element data content to obtain the business table.

[0037] Furthermore, maintaining the association relationship between the root table and the business table specifically includes:

[0038] Maintain the root table; and, maintain the association relationship between the root table and the business table;

[0039] The maintenance root table specifically includes:

[0040] Step C1: Set the root table data ID and unique constraint, set the root table root associated field, and set the business table data ID;

[0041] Step C2: extract information from the business table using an ETL tool or SQL statement, and insert the extracted information into the root table;

[0042] Step C3: Maintain the root table according to whether there are records in the business table that are identical to those in the root table:

[0043] If the root table contains the same content record as the service table and no association relationship is established, the root table is updated according to the service table; otherwise, the root table is not updated.

[0044] Furthermore, the maintaining the association relationship between the root table and the business table specifically includes the following steps:

[0045] Step K1: extract dynamic parameters from the business relationship comparison table;

[0046] The dynamic parameters include table serial number, table English name, or table Chinese name; the business relationship comparison table is pre-constructed according to the business content;

[0047] Step K2: Find the associated target business table according to the dynamic parameters;

[0048] Wherein, the target business table includes unassociated data and associated data;

[0049] Step K3: Determine whether there is unassociated data in the target business table by marking:

[0050] If there is unassociated data in the target business table, extract the corresponding target data from the root table according to the dynamic parameters, and update the unassociated data to obtain associated data until there is no unassociated data in the target business table;

[0051] Among them, the unassociated data includes records with dataID "0000000000000000000", the unassociated data is marked as 0, and the associated data is marked as 1.

[0052] Further, after step S7, the method further includes step S8;

[0053] Step S8: Visually display the query request, query process, and root table target information or business target information required by the requester.

[0054] In a second aspect, the present invention provides an association query system, the system comprising:

[0055] An acquisition module, used to acquire a query request from a requesting party;

[0056] Wherein, the query request is a basic information query request or a business information query request;

[0057] A first determination module, connected to the acquisition module, for determining a base point in the query request;

[0058] A first execution module, connected to the determination module, is used to execute an initial query of a root table or a business table according to a base point in the query request to obtain an initial current query result;

[0059] A second determination module, connected to the initial execution module, for determining an associated query base point according to the current query result;

[0060] The second execution module is connected to the second determination module and the first execution module respectively, and is used to execute the associated query according to the associated query base point; and determine whether the query result of the associated query meets the query requirement of the requesting party:

[0061] If the query result of the associated query can meet the query requirement of the requester, then the root table target information required by the requester is extracted from the root table, or the business target information required by the requester is extracted from the business table; if the query result of the associated query cannot meet the query requirement of the requester, then the first execution module action is triggered until the query result of the associated query can meet the query requirement of the requester;

[0062] A return module, connected to the second execution module, for returning the root table target information or business target information required by the requesting party to the requesting party, so as to complete the association query based on the relational database;

[0063] Wherein, the initial query and the associated query both include one or more query modes, and the query modes are precise query, and / or homophonic query, and / or pinyin query, and / or fuzzy query.

[0064] Furthermore, the system further comprises: a processing module;

[0065] The processing module is used to construct a root table and a service table, and to maintain an association relationship between the root table and the service table.

[0066] Furthermore, the system further comprises:

[0067] The associated information display module is connected to the acquisition module, the first execution module, the second execution module, and the return module respectively, and is used to visualize the query request, the query process, and the root table target information or business target information required by the requester.

[0068] Beneficial effects of the present invention:

[0069] 1. The present invention gradually approaches the final result from the initial query to the associated query through a step-by-step query process, thus avoiding the complex calculations required for a one-time query and improving the query efficiency.

[0070] 2. The present invention can flexibly adjust the query mode (using one or more of precise query, homophonic query, pinyin query, and fuzzy query) according to different types of query requests to meet the needs of various business scenarios.

[0071] 3. The present invention can optimize the query strategy in real time to ensure that the final result meets the requirements of the requester. This iterative query method improves the accuracy of the query.

[0072] 4. The present invention clearly distinguishes between the root table and the business table, and stores basic information and business information separately, which helps to maintain the neatness and manageability of data and improves the rationality of data query.

[0073] 5. When the query result does not meet the request of the requesting party, the present invention can iterate the associated query steps until a result that meets the conditions is obtained. This adaptive mechanism effectively ensures the integrity of the query.

[0074] 6. The present invention reduces the complexity of the query by decomposing the query process into multiple manageable steps, making the system easier to maintain and expand.

[0075] 7. The information finally returned to the requester by the present invention is all verified valid data, which can better meet the needs of the requester, thereby improving user satisfaction. BRIEF DESCRIPTION OF THE DRAWINGS

[0076] Figure 1 Schematic diagram of an association query method based on a relational database in an embodiment of the present invention;

[0077] Figure 2 It is a schematic diagram of the data element root in the embodiment of the present invention;

[0078] Figure 3 It is intended to represent the data element service in the embodiment of the present invention;

[0079] Figure 4 It is a schematic diagram of the correlation relationship in the embodiment of the present invention;

[0080] Figure 5 Schematic diagram of association relationship in an embodiment of the present invention;

[0081] Figure 6 is a flowchart of an association query based on a relational database in an embodiment of the present invention;

[0082] Figure 7 Schematic diagram of the overall framework in an embodiment of the present invention. DETAILED DESCRIPTION

[0083] In order to enable those skilled in the art to better understand the technical solution of the present invention, the embodiments of the present invention will be further described in detail below with reference to the accompanying drawings.

[0084] It should be understood that the specific embodiments and drawings described herein are only used to explain the present invention rather than to limit the present invention.

[0085] It can be understood that, in the absence of conflict, the various embodiments of the present invention and the various features in the embodiments can be combined with each other.

[0086] It can be understood that, for the convenience of description, the drawings of the present invention only show the parts related to the present invention, while the parts irrelevant to the present invention are not shown in the drawings.

[0087] It can be understood that each unit and module involved in the embodiments of the present invention may correspond to only one physical structure, or may be composed of multiple physical structures, or multiple units and modules may be integrated into one physical structure.

[0088] It can be understood that, without conflict, the functions and steps marked in the flowcharts and block diagrams of the present invention may occur in an order different from that marked in the drawings.

[0089] It is understood that the flowcharts and block diagrams of the present invention illustrate the possible architectures, functions, and operations of the systems, devices, equipment, and methods according to the various embodiments of the present invention. Each box in the flowchart or block diagram may represent a unit, module, program segment, or code, which contains executable instructions for implementing the specified functions. Moreover, each box or combination of boxes in the block diagram and flowchart may be implemented by a hardware-based system that implements the specified functions, or may be implemented by a combination of hardware and computer instructions.

[0090] It can be understood that the units and modules involved in the embodiments of the present invention can be implemented by software or hardware. For example, the units and modules can be located in a processor.

[0091] Embodiment 1:

[0092] like Figure 1 and Figure 6As shown, this embodiment provides an association query method based on a relational database, and the method includes the following steps:

[0093] Step S0: constructing a root table and a service table; and maintaining an association relationship between the root table and the service table.

[0094] As a specific implementation method, building a root table specifically includes the following steps:

[0095] Step A1: Determine the root table type based on the classification of people, places, things, objects, and organizations;

[0096] The root table types are divided into personnel element root table, address element root table, event element root table, item element root table, and organization element root table;

[0097] Step A2: According to the determined root table type, determine the root fields and root table data types required by the root table;

[0098] Step A3: Set the basic element data content according to the root fields and root table data types required by the root table;

[0099] The basic element data content includes root data item information, root association fields, and root element data ID fields; the root element data ID field is a unique field constraint;

[0100] Step A4: According to the root table type, the root field, the root table data type, and the root element data content, write a corresponding database query statement to obtain the root table.

[0101] Specifically, this embodiment classifies relevant data elements such as people, places, things, objects, and organizations, and sets up a personnel element root table, an address element root table, an event element root table, an item element root table, and an organization element root table, which can be set according to the visual data classification. In addition to the basic data items required for data query (for example, personnel elements include name, gender, nationality, date of birth, citizen ID card, address, native place, full name in Pinyin, and abbreviated name in Pinyin) and other related fields, the root table module specifically sets the information association field and the ID field of the XX element data, where the type of the information association field is character type, and the initial length is set to 512. The initial length can be expanded as needed, and the data defaults to 512-bit characters and 512 "0". The element data ID field is a unique field constraint, the field type is character type, and the length is set to 18. For example: Taking the human element as an example, set the personnel element root table RK_GB_JBXX, including personnel ID, name, citizen ID card, information association and other fields. The length of the information association field is 512 bits of characters and defaults to 512 "0", see for details. Figure 2 Data feature root table.

[0102] As a specific implementation method, constructing a business table specifically includes the following steps:

[0103] Step B1: Determine the business table type based on the classification of people, places, things, objects, and organizations;

[0104] The business table types are divided into personnel element business table, address element business table, event element business table, item element business table, and organization element business table;

[0105] Step B2: According to the determined business table type, determine the business fields and business data types required by the business table;

[0106] Step B3: Set the business element data content according to the fields and business data types required by the business table;

[0107] The business element data content includes business data item information, business association fields, and business element data ID fields;

[0108] Step B4: Compile a corresponding database query statement according to the business table type, the business field, the business data type, and the business element data content to obtain the business table.

[0109] Specifically, this embodiment classifies relevant data elements such as people, places, things, objects, and organizations, and sets different business tables according to the data categories. In addition to the data items required for the business, the business table specifically sets the element data ID field. The field type of the element data ID field is character type, and the length is set to 18. The data defaults to 18-bit characters "000000000000000000". This embodiment classifies relevant data elements such as people, places, things, objects, and organizations, and sets the personnel data ID, address data ID, event data ID, organization data ID, etc. of the human element. For example: This implementation plan takes the human element as an example, and sets the permanent population business table, temporary population business table, airport security business table, and insured personnel business table. Each business table has a personnel ID field, and the value of this field is taken from the personnel ID of the person in the human element root table. See for details. Figure 3 Data element business table.

[0110] As a specific implementation manner, maintaining the association relationship between the root table and the service table specifically includes:

[0111] Maintain the root table; and, maintain the association relationship between the root table and the business table;

[0112] The maintenance root table specifically includes:

[0113] Step C1: set the root table data ID and unique constraint, set the root table root association field (the default value is 512 bits long "0"), and set the business table data ID;

[0114] Step C2: extract information from the business table using an ETL tool or SQL statement, and insert the extracted information into the root table;

[0115] Step C3: Maintain the root table according to whether there are records in the business table that are identical to those in the root table:

[0116] If the root table contains the same content record as the service table and no association relationship is established, the root table is updated according to the service table; otherwise, the root table is not updated.

[0117] Specifically, use ETL tools or SQL statements to obtain basic data items from each business table of the same data element classification according to the scheduled task and insert them into the root table of the same data element classification. At this time, each data automatically generates the xx element data ID under the element (the element data ID is set to a self-increasing sequence starting from 1, with a length of 18 bits, and "0" is added on the left if it is less than 18 bits. For example, the starting value of the personnel data ID is "000000000000000001", and the sequence growth is "000000000000000002",

[0118] "000000000000000003"......), xx element data ID, for example, obtains information including name, gender, nationality, date of birth, citizen ID card, address, native place, full name in Pinyin, abbreviation of name in Pinyin, etc. from the business table related to personnel elements, and inserts data into the root table. When inserting, it determines whether the element information exists (for example, personnel element information is based on name + citizen ID card). If the information does not exist, insert it, and follow the unique field constraint of the element data ID field described in step 1 to ensure that the information in the root table is unique.

[0119] As a specific implementation manner, the maintaining the association relationship between the root table and the service table specifically includes the following steps:

[0120] Step K1: extract dynamic parameters from the business relationship comparison table;

[0121] The dynamic parameters include a table serial number, a table English name, or a table Chinese name; the business relationship comparison table is pre-constructed according to the business content;

[0122] The business relationship comparison table may also be called an association relationship comparison table. Figure 4 As shown, the association relationship comparison table includes fields such as serial number, table English name, table Chinese name, and table abbreviation.

[0123] Step K2: Find the associated target business table according to the dynamic parameters;

[0124] The target business table includes unassociated data and associated data.

[0125] Step K3: Determine whether there is unassociated data in the target business table by marking:

[0126] If there is unassociated data in the target business table, extract the corresponding target data from the root table according to the dynamic parameters, and update the unassociated data to obtain associated data until there is no unassociated data in the target business table;

[0127] Among them, the unassociated data includes records with dataID "0000000000000000000", the unassociated data is marked as 0, and the associated data is marked as 1.

[0128] Specifically, Figure 5 As shown, the scheduled task of this embodiment automatically and regularly maintains the association relationship, obtains the element data ID of the data from the root table (such as the personnel data ID of the person element), updates the element data ID of each business table under the same data element one by one (such as the personnel data ID of the person element), and at the same time, when the data information exists in this type of business table data, updates the value of the corresponding flag bit of the association information field under the root table of the element to 1.

[0129] Step S1: Obtaining a query request from a requesting party, wherein the query request is a basic information query request or a business information query request.

[0130] Step S2: Determine the base point in the query request.

[0131] Step S3: According to the base point in the query request, perform an initial query of the root table or the business table to obtain an initial current query result; the root table represents the basic information query request, and the business table represents the business information query request.

[0132] Step S4: Determine the associated query base point according to the current query result.

[0133] Step S5: Execute the associated query according to the associated query base point.

[0134] Step S6: Determine whether the query result of the associated query meets the query requirement of the requesting party:

[0135] If the query result of the associated query can meet the query requirements of the requester, the root table target information required by the requester is extracted from the root table, or the business target information required by the requester is extracted from the business table; if the query result of the associated query cannot meet the query requirements of the requester, steps S4 to S6 are repeated until the query result of the associated query can meet the query requirements of the requester.

[0136] When making a specific query, the element root table (for example, the personnel element root table, address element root table, event element root table, item element root table, and organization element root table) is used as the query entry. Users can enter query conditions, supporting precise query, homophone query, pinyin query, and fuzzy query. The system will obtain the query results of the root table based on the input conditions and enter the root table details for display.

[0137] Step S7: Return the root table target information or business target information required by the requesting party to the requesting party to complete the associated query based on the relational database; the initial query and the associated query both include one or more query methods, and the query method is precise query, and / or homophonic query, and / or pinyin query, and / or fuzzy query.

[0138] Step S8: Visually display the query request, query process, and root table target information or business target information required by the requester.

[0139] Specifically, on the basic information details page of the root table, the associated information field value is translated through the association relationship comparison table and the associated information with the associated information flag as 1 is actually displayed. The displayed associated information is used as an information link. After clicking, you can jump to the information result of this type, thereby realizing the associated query of the element data (such as the person, the item, the event, the address, and the organization).

[0140] The specific steps of querying in this embodiment are as follows:

[0141] (1) If you need to query a person's basic information but are not sure which type of element the information belongs to, you can use the element basic information query portal to query. The element basic information in this embodiment includes the ID number and name. The ID number and name can determine unique information;

[0142] (2) Perform an initial query to obtain basic personnel information.

[0143] (3) Perform a related query based on the basic information of the personnel and obtain the related query results.

[0144] (4) Based on the results of the related query, multiple related queries are performed, including obtaining a list of fellow travelers. Further queries can be performed to obtain more detailed information about the person. Based on the information of the person being queried, the flight he took and other fellow travelers can be further understood. This cross-query greatly improves the efficiency of data association and makes information acquisition more comprehensive and accurate.

[0145] This type of query through basic information and business information of elements can realize integrated query from "people" to "cases" and "items" to "cases".

[0146] Embodiment 2:

[0147] like Figure 7 As shown, this embodiment provides an association query system, which can implement the association query method based on a relational database described in Example 1, and the system includes:

[0148] An acquisition module, used to acquire a query request from a requesting party;

[0149] Wherein, the query request is a basic information query request or a business information query request;

[0150] A first determination module, connected to the acquisition module, for determining a base point in the query request;

[0151] A first execution module, connected to the determination module, is used to execute an initial query of a root table or a business table according to a base point in the query request to obtain an initial current query result;

[0152] A second determination module, connected to the initial execution module, for determining an associated query base point according to the current query result;

[0153] The second execution module is connected to the second determination module and the first execution module respectively, and is used to execute the associated query according to the associated query base point; and determine whether the query result of the associated query meets the query requirement of the requesting party:

[0154] If the query result of the associated query can meet the query requirement of the requester, then the root table target information required by the requester is extracted from the root table, or the business target information required by the requester is extracted from the business table; if the query result of the associated query cannot meet the query requirement of the requester, then the first execution module action is triggered until the query result of the associated query can meet the query requirement of the requester;

[0155] A return module, connected to the second execution module, for returning the root table target information or business target information required by the requesting party to the requesting party, so as to complete the association query based on the relational database;

[0156] Wherein, the initial query and the associated query both include one or more query modes, and the query modes are precise query, and / or homophonic query, and / or pinyin query, and / or fuzzy query.

[0157] Specifically, the detailed process of implementing information association query in this system is as follows:

[0158] When performing information association query, first prepare to query different types of basic information according to the classification of data elements, including people, things, objects, places, and organizations. Set the root table unit as the base table for basic information query of each element, such as the root table of personnel elements, the root table of event elements, the root table of item elements, and the root table of organizational elements. When users perform basic information query, they can use different query conditions, including precise query, fuzzy query, and homophonic query, and enter the relevant basic information of the elements as conditions for query. The system will obtain the query results based on the input conditions and display the results, including not only basic data information but also related association information.

[0159] Next, the system will confirm the associated information based on the association relationship of the data and set the link address for mutual access so that users can view the relevant associated information. In the business information query preparation stage, under each data element classification, the corresponding business information query function will be provided. According to the characteristics of different businesses, basic query conditions will be prepared to support precise query, fuzzy query and homophonic query. The query results will display the data item information of the business table and provide the corresponding associated information display function.

[0160] When browsing the query results, the uniqueness of the data is confirmed through the element data ID unit, ensuring that the business table is used as the base table for business information query under each element, such as the personnel element-permanent population table, the personnel element-temporary population table, etc. To improve the user experience, when querying, the system translates the association relationship values ​​in the association information unit into easy-to-read information through configuration information, and sets the logical relationship of the flag bit.

[0161] In addition, the execution program responsible for calling will regularly maintain the data relationship between the associated information unit in the basic information query module and the element data ID unit in the business table query module, ensuring that the element data ID values ​​of all data in the business information query module that have a data association relationship with the basic information query module come from the basic information query module. In the algorithm processing link of the association relationship, the associated information unit is used as a root table to represent the association relationship between data. It is set to a 512-bit character length and the initial value is 512 "0". When there is corresponding information in a business table, the system will set the value of the corresponding flag bit to N = 1; if the corresponding information does not exist, the flag bit remains unchanged, that is, N = 0.

[0162] In the association configuration, the system will initialize the serial number, Chinese table name, English table name and table abbreviation in the association configuration unit, and agree on the sorting order of the business information. Finally, the association algorithm unit will sort in ascending order according to the serial number, extract the serial number and table English name from the association configuration unit, and perform a loop operation.

[0163] When a user initiates a query through the basic information query module or the business information query module as the query entry, the user will enter the basic information query result details or the business information query result details. In the result display interface, in addition to displaying the set basic data items, the system will also display the values ​​in the associated information unit, and display the specific associated information after being translated by the associated relationship configuration unit. The user can click on the displayed associated information to jump and view different information, thereby effectively realizing the associated query of information.

[0164] As a specific implementation, the system further includes a processing module;

[0165] The processing module is used to construct a root table and a service table, and to maintain an association relationship between the root table and the service table.

[0166] Specifically, the processing module can be subdivided into a root table module and a business table module.

[0167] The root table module is classified according to the relevant data elements of people, places, things, objects, organizations, etc., and sets the personnel element root table, address element root table, event element root table, item element root table, and organization element root table, which can be set according to the visual data classification. In addition to the basic data items required for data query (for example: personnel elements include name, gender, ethnicity, date of birth, citizen ID card, address, native place, full name in Pinyin, and abbreviated name in Pinyin) and other related fields, the root table module specifically sets the information association field and the xx element data ID field. The information association field field type is character type, and the initial length is 512 (can be expanded at any time as needed). The data defaults to 512 characters and 512 "0". The element data ID field is a unique field constraint, the field type is character type, and the length is set to 18.

[0168] The business table module is classified according to the relevant data elements such as people, places, things, objects, and organizations, and different business tables are set according to the data categories. In addition to the data items required for the business, the business table specifically sets the element data ID field. The element data ID field type is character type, the length is set to 18, and the data defaults to 18-bit characters "000000000000000000". This embodiment is classified according to the relevant data elements such as people, places, things, objects, and organizations, and sets the human element personnel data ID, address data ID, event data ID, organization data ID, etc.

[0169] As a specific implementation, the system also includes an associated information display module, which is respectively connected to the acquisition module, the first execution module, the second execution module, and the return module, and is used to visualize the query request, the query process, and the root table target information or business target information required by the requester.

[0170] The system in this embodiment can execute the method in Embodiment 1.

[0171] It is to be understood that the above embodiments are merely exemplary embodiments used to illustrate the principles of the present invention, but the present invention is not limited thereto. For those of ordinary skill in the art, various modifications and improvements can be made without departing from the spirit and essence of the present invention, and these modifications and improvements are also considered to be within the scope of protection of the present invention.

Claims

1. A method for associative query based on a relational database, characterized in that: The method comprises the following steps: Step S1: Obtain the query request from the requesting party; Wherein, the query request is a basic information query request or a business information query request; Step S2: Determine the base point in the query request; Step S3: According to the base point in the query request, perform an initial query of the root table or the business table to obtain an initial current query result; Step S4: Determine the associated query base point according to the current query result; Step S5: Execute the associated query according to the associated query base point; Step S6: Determine whether the query result of the associated query meets the query requirement of the requesting party: If the query result of the associated query can meet the query requirement of the requester, then the root table target information required by the requester is extracted from the root table, or the business target information required by the requester is extracted from the business table; if the query result of the associated query cannot meet the query requirement of the requester, then steps S4 to S6 are repeated until the query result of the associated query can meet the query requirement of the requester; Step S7: returning the root table target information or business target information required by the requesting party to the requesting party to complete the association query based on the relational database; Wherein, the initial query and the associated query both include one or more query modes, and the query modes are precise query, and / or homophonic query, and / or pinyin query, and / or fuzzy query.

2. The method for associative query based on relational database according to claim 1, characterized in that: Before step S3, the method further includes step S0; Step S0: constructing a root table and a service table; and maintaining an association relationship between the root table and the service table.

3. The method for associative query based on relational database according to claim 2, characterized in that: Constructing the root table includes the following steps: Step A1: Determine the root table type based on the classification of people, places, things, objects, and organizations; The root table types are divided into personnel element root table, address element root table, event element root table, item element root table, and organization element root table; Step A2: According to the determined root table type, determine the root fields and root table data types required by the root table; Step A3: Set the basic element data content according to the root fields and root table data types required by the root table; The basic element data content includes root data item information, root association fields, and root element data ID fields; the root element data ID field is a unique field constraint; Step A4: According to the root table type, the root field, the root table data type, and the root element data content, write a corresponding database query statement to obtain the root table.

4. The method for associative query based on relational database according to claim 2, characterized in that: Constructing a business table includes the following steps: Step B1: Determine the business table type based on the classification of people, places, things, objects, and organizations; The business table types are divided into personnel element business table, address element business table, event element business table, item element business table, and organization element business table; Step B2: According to the determined business table type, determine the business fields and business data types required by the business table; Step B3: Set the business element data content according to the fields and business data types required by the business table; The business element data content includes business data item information, business association fields, and business element data ID fields; Step B4: Compile a corresponding database query statement according to the business table type, the business field, the business data type, and the business element data content to obtain the business table.

5. The method for associative query based on relational database according to claim 2, characterized in that: Maintaining the association relationship between the root table and the business table specifically includes: Maintain the root table; and, maintain the association relationship between the root table and the business table; The maintenance root table specifically includes: Step C1: Set the root table data ID and unique constraint, set the root table root associated field, and set the business table data ID; Step C2: extract information from the business table using an ETL tool or SQL statement, and insert the extracted information into the root table; Step C3: Maintain the root table according to whether there are records in the business table that are identical to those in the root table: If the root table contains the same content record as the service table and no association relationship is established, the root table is updated according to the service table; otherwise, the root table is not updated.

6. The method for associative query based on relational database according to claim 5, characterized in that: The maintaining of the association relationship between the root table and the business table specifically includes the following steps: Step K1: extract dynamic parameters from the business relationship comparison table; The dynamic parameters include table serial number, table English name, or table Chinese name; the business relationship comparison table is pre-constructed according to the business content; Step K2: Find the associated target business table according to the dynamic parameters; Wherein, the target business table includes unassociated data and associated data; Step K3: Determine whether there is unassociated data in the target business table by marking: If there is unassociated data in the target business table, extract the corresponding target data from the root table according to the dynamic parameters, and update the unassociated data to obtain associated data until there is no unassociated data in the target business table; Among them, the unassociated data includes records with dataID "0000000000000000000", the unassociated data is marked as 0, and the associated data is marked as 1.

7. The method for associative query based on a relational database according to any one of claims 1 to 6, characterized in that: After step S7, the method further includes step S8; Step S8: Visually display the query request, query process, and root table target information or business target information required by the requester.

8. A related query system, characterized in that: The system comprises: An acquisition module, used to acquire a query request from a requesting party; Wherein, the query request is a basic information query request or a business information query request; A first determination module, connected to the acquisition module, for determining a base point in the query request; A first execution module, connected to the determination module, is used to execute an initial query of a root table or a business table according to a base point in the query request to obtain an initial current query result; A second determination module, connected to the initial execution module, for determining an associated query base point according to the current query result; The second execution module is connected to the second determination module and the first execution module respectively, and is used to execute the associated query according to the associated query base point; and determine whether the query result of the associated query meets the query requirement of the requesting party: If the query result of the associated query can meet the query requirement of the requester, then the root table target information required by the requester is extracted from the root table, or the business target information required by the requester is extracted from the business table; if the query result of the associated query cannot meet the query requirement of the requester, then the first execution module action is triggered until the query result of the associated query can meet the query requirement of the requester; A return module, connected to the second execution module, for returning the root table target information or business target information required by the requesting party to the requesting party, so as to complete the association query based on the relational database; Wherein, the initial query and the associated query both include one or more query modes, and the query modes are precise query, and / or homophonic query, and / or pinyin query, and / or fuzzy query.

9. The association query system according to claim 8, characterized in that: The system also includes a processing module; The processing module is used to construct a root table and a service table, and to maintain an association relationship between the root table and the service table.

10. The association query system according to claim 8 or 9, characterized in that: The system further comprises: The associated information display module is connected to the acquisition module, the first execution module, the second execution module, and the return module respectively, and is used to visualize the query request, the query process, and the root table target information or business target information required by the requester.

Images