Method, system and device for displaying main table data and detail data and medium

Abstract

The invention discloses a main table data and detail data display method, system and device and a medium. The method comprises the steps that a main table and detail tables are created in advance, and one main table corresponds to a plurality of detail tables; based on a main table field of the main table and each detail table corresponding to the main table, configuring the main table list, and configuring each detail table added into the main table list; a data acquisition request is sent to a server, and the server splices SQL query statements through an aggregation function based on main table list configuration and queries main table data and detail data from a database based on the spliced complete SQL query statements; and receiving the main table data and the detailed data, and displaying the main table data and the detailed data in the same row of the main table list. Through the complete SQL query statement, the data of the main table and the detail table are queried from the database at one time, the data processing efficiency is improved, and meanwhile, the user can flexibly configure the detail data display conveniently to meet the diversified requirements of different users.

Description

Technical Field

[0001] This invention relates to the field of data processing technology, and in particular to a method, system, device, and medium for displaying master table data and detailed data. Background Technology

[0002] In data processing and display scenarios, linking and displaying data from master and detail tables is a common requirement, especially when detailed data needs to be presented in a list. Current technologies have several shortcomings in this area. Most methods involve multiple database queries: first, retrieving data from the master table; then, for each record in the master table, querying the corresponding detail table; finally, integrating and processing the data in the program before sending it to the front end for display. This approach is inefficient in displaying detailed data in a list. Multiple database queries not only increase the database load and lead to low data retrieval efficiency, but also consume significant system resources during data integration in the program, resulting in slow response times when displaying detailed data in the list and negatively impacting user experience. Furthermore, existing technologies also have limitations in configuring the display method of detailed data. Traditional solutions often fix the display format of detailed data in the program code. If users want to adjust the fields displayed in the detailed data display, the sorting method, or modify the separator between fields and data, the program needs to be rewritten and compiled. The operation process is complex and lacks flexibility, making it difficult to meet the diverse and personalized display needs of users. For example, in some enterprise management systems, when displaying the master order table and its corresponding order details, traditional methods cannot quickly present the order details in a clear list format, nor can they flexibly adjust the display style of the detail data according to user needs. When the data volume is large, the system response speed slows down significantly, further reducing user satisfaction with the data display effect. Therefore, there is an urgent need for an implementation method that can efficiently display details in a list and support flexible configuration of the detail display method. Summary of the Invention

[0003] This invention provides a method, system, device, and medium for displaying master table data and detailed data, in order to solve the problems of low efficiency and difficulty in flexibly configuring detailed display methods in the prior art when displaying detailed data in a list.

[0004] In a first aspect, embodiments of the present invention provide a method for displaying master table data and detailed data, including: Pre-create a master table and detail tables, where one master table corresponds to multiple detail tables; Based on the main table fields of the main table and the corresponding detail tables of the main table, the main table list is configured, and the detail tables added to the main table list are configured. A data retrieval request is sent to the server. The server, based on the configuration of the main table list, constructs an SQL query statement using aggregate functions. Based on the constructed complete SQL query statement, it retrieves the main table data and detailed data from the database. Receive the main table data and the detailed data, and display the main table data and the detailed data in the same row of the main table list.

[0005] Optionally, configuring the main table list based on the main table fields and the corresponding detail tables includes: Enter the main table list design page and identify the main table and the multiple detail tables corresponding to the main table; The main table fields selected by the user and the detail table are added as columns in the main table list design page.

[0006] Optionally, configuring each of the detail tables added to the main table list includes: Determine which of the detailed tables will be added to the main table list; Configure the display fields, sorting fields, separators between the display fields, and separators between the detailed data corresponding to each detailed table.

[0007] Optionally, the data retrieval request is sent to the server, and the server, based on the configuration of the main table list, constructs an SQL query statement using aggregate functions, and retrieves main table data and detailed data from the database based on the constructed complete SQL query statement, including: A data retrieval request is sent to the server, and the server constructs the SQL query statement based on the main table fields in the main table list configuration to obtain the first SQL query statement; Determine whether a detail table exists in the main table list configuration; If it exists, the first SQL query statement is concatenated using an aggregation function based on the configuration of each detail table to obtain the complete SQL query statement; By executing the complete SQL query statement, the main table data and detailed data are retrieved from the database.

[0008] Optionally, if present, the complete SQL query statement is obtained by concatenating the configuration of the first SQL query statement using aggregate functions based on the configuration of each detail table, including: If it exists, then iterate through each detail table configuration in the main table list and generate an aggregate subquery corresponding to each detail table configuration using an aggregate function; Each of the aforementioned aggregate subqueries is then concatenated into the first SQL query statement in a loop; The complete SQL query statement is obtained after all the detailed tables in the main table list have been configured and aggregated.

[0009] Optionally, receiving the main table data and the detailed data, and displaying the main table data and the detailed data in the same row of the main table list, includes: Receive the main table data and detailed data, and display the main table data and detailed data in the same row of the main table list based on the configuration of the main table list.

[0010] Secondly, embodiments of the present invention provide a system for displaying master table data and detailed data, used to execute the method for displaying master table data and detailed data as described in any embodiment of the present invention, including: The creation module is used to pre-create the master table and detail tables, where one master table corresponds to multiple detail tables; The configuration module is used to configure the main table list based on the main table fields and the corresponding detail tables of the main table, and to configure each detail table added to the main table list. The acquisition module is used to send data acquisition requests to the server. The server, based on the configuration of the main table list, constructs an SQL query statement using aggregation functions, and retrieves the main table data and detailed data from the database based on the constructed complete SQL query statement. The display module is used to receive the main table data and the detailed data, and display the main table data and the detailed data in the same row of the main table list.

[0011] Optionally, the acquisition module is specifically used for: A data retrieval request is sent to the server, and the server constructs the SQL query statement based on the main table fields in the main table list configuration to obtain the first SQL query statement; Determine whether a detail table exists in the main table list configuration; If it exists, the first SQL query statement is concatenated using an aggregation function based on the configuration of each detail table to obtain the complete SQL query statement; By executing the complete SQL query statement, the main table data and detailed data are retrieved from the database.

[0012] Thirdly, embodiments of the present invention provide an electronic device, the electronic device comprising: At least one processor; and A memory communicatively connected to the at least one processor; wherein, The memory stores a computer program that can be executed by the at least one processor, which enables the at least one processor to perform the method for displaying master table data and detail data according to any embodiment of the present invention.

[0013] Fourthly, embodiments of the present invention provide a computer-readable storage medium storing computer instructions, which are used to cause a processor to execute and implement the method for displaying master table data and detail data as described in any embodiment of the present invention.

[0014] (1) In this embodiment of the invention, by pre-creating a master table and a detail table and establishing a one-to-many relationship, and by combining the server with the configuration of the master table list to dynamically concatenate a complete SQL query statement containing aggregate functions, the master table data and detail data are queried from the database at once, avoiding the traditional multiple queries and memory data integration process, significantly reducing the number of database queries and improving data processing efficiency.

[0015] (2) In this embodiment of the invention, based on the main table fields of the main table and the corresponding detailed tables of the main table, users can customize the main table list and customize the detailed tables added to the main table list. By customizing the display fields, sorting fields, field separators and data separators of the detailed tables added to the main table list, users can flexibly configure the detailed data display to meet the diverse needs of different users and enhance user experience satisfaction.

[0016] (3) In this embodiment of the invention, data is obtained at once through a complete SQL query statement and the main table data and aggregated detailed data are displayed in the same row of the main table list. There is no need to query the database multiple times and there is no need to process the relationship between the main table data and the detailed data in memory again. This reduces the number of database queries, improves query performance, avoids putting a lot of pressure on the database, and by displaying the main table data and detailed data in the same row of the main table list, the main table data and detailed data can be seen more intuitively, reducing the need to enter the details view.

[0017] It should be understood that the description in this section is not intended to identify key or essential features of the embodiments of the present invention, nor is it intended to limit the scope of the invention. Other features of the invention will become readily apparent from the following description. Attached Figure Description

[0018] To more clearly illustrate the technical solutions in the embodiments of the present invention, the accompanying drawings used in the description of the embodiments will be briefly introduced below. Obviously, the accompanying 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.

[0019] Figure 1 A flowchart illustrating a method for displaying master table data and detail data according to Embodiment 1 of the present invention; Figure 2 A flowchart illustrating a specific method for displaying master table data and detail data, provided in Embodiment 1 of the present invention; Figure 3 A flowchart illustrating a method for displaying master table data and detail data according to Embodiment 2 of the present invention; Figure 4 This is a data query flowchart provided in Embodiment 2 of the present invention; Figure 5 This is a framework diagram of a main table data and detail data display system provided in Embodiment 3 of the present invention; Figure 6 A schematic diagram of an electronic device that can be used to implement embodiments of the present invention is shown. Detailed Implementation

[0020] To enable those skilled in the art to better understand the present invention, the technical solutions of the present invention will be clearly and completely described below with reference to the accompanying drawings of the embodiments of the present invention. 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 should fall within the scope of protection of the present invention.

[0021] It should be noted that the terms "first," "second," etc., in the specification, claims, and accompanying drawings of this invention are used to distinguish similar objects and are not necessarily used to describe a specific order or sequence. It should be understood that such data can be interchanged where appropriate so that the embodiments of the invention described herein can be implemented in orders other than those illustrated or described herein. Furthermore, the terms "comprising" and "having," and any variations thereof, are intended to cover a non-exclusive inclusion; for example, a process, method, system, product, or apparatus that comprises a series of steps or units is not necessarily limited to those steps or units explicitly listed, but may include other steps or units not explicitly listed or inherent to such processes, methods, products, or apparatus.

[0022] Example 1:

[0023] Figure 1 This is a flowchart illustrating a method for displaying master table data and detailed data according to Embodiment 1 of the present invention. This embodiment is applicable to data display scenarios. The method is applied to a system for displaying master table data and detailed data, and this system can be configured in an electronic device. Figure 1 As shown, the method includes: S110. Pre-create the master table and detail tables, where one master table corresponds to multiple detail tables.

[0024] In this context, the master table refers to a database table that stores information about core entities or business entities; it typically has a unique identifier, such as an ID, order number, or service order number. The detail table refers to a database table that stores specific details associated with records in the master table. Detail tables can establish a one-to-many relationship with the master table through unique identifiers, meaning one master table can correspond to multiple detail tables.

[0025] Specifically, master tables and detail tables can be pre-created in the database. Taking a data display scenario of a service order master table and product detail sub-table in a company's CRM system as an example, the service order master table can contain fields such as service order number, customer name, service type, and service time, where the "service order number" can serve as a unique identifier for the master table. The product detail table can contain fields such as service order number, product number, product name, product quantity, and product unit price. A one-to-many relationship is established between the "service order number" field and the service order master table, meaning that one service order may correspond to multiple product details.

[0026] In this embodiment, a master table and detail tables are created in advance, with one master table corresponding to multiple detail tables, providing a foundation for efficient subsequent relational queries.

[0027] S120. Based on the main table fields of the main table and the corresponding detail tables of the main table, configure the main table list and configure each detail table added to the main table list.

[0028] The main table fields can refer to each column of data stored in the main table that is available for selection and displayed in the final list; for example, in the service order main table, the service order number, customer name, service time, etc., are the main table fields, which are the raw data that can be directly read from the main table. The main table list can refer to the final visualized data list based on the main table that will be presented to the user; it includes user-defined main table fields and multiple detail tables that are related to the main table.

[0029] Specifically, the user selects a master table (e.g., service order master table, order master table, etc.) and enters the configuration interface of that master table list. The configuration interface automatically displays the master table fields. Users can drag and drop the master table fields they want to display into the "Master Table List Design Area." Each dragged master table field creates a column in the master table list, corresponding to the dragged master table field. After all the master table fields selected by the user are configured in the master table list, the corresponding detailed tables are automatically displayed in the configuration interface based on the unique identifier of the master table. Users can also drag and drop the detailed tables they want to display into the "Master Table List Design Area," adding each selected detailed table as a column to the master table list. The detailed tables added to the master table list are then configured according to configuration rules. These configuration rules are user-defined detailed table configuration rules and may include: display fields, sorting fields, separators between display fields, and separators between detailed data in each detailed table. Once the configurations for each detailed table added to the main table list are complete, save the main table list configuration.

[0030] In this embodiment, the main table list is configured based on the main table fields and the corresponding detail tables, defining the main framework and core information of the list, allowing users to customize the width and content density of the list; and supporting the configuration of multiple detail tables, which facilitates users to flexibly configure the display of detail data to meet the diverse needs of different users. S130. A data acquisition request is sent to the server. The server, based on the configuration of the main table list, constructs an SQL query statement using aggregate functions. Based on the constructed complete SQL query statement, it queries the main table data and detailed data from the database.

[0031] Here, "data retrieval request" refers to a network call request initiated by the front-end to the server to retrieve data from the main table list. "Aggregate function" refers to a special function that calculates and combines multiple rows of data into a single value, such as the `STRING_AGG` function. "SQL query statement" refers to a string command that conforms to SQL syntax and is dynamically constructed by the server. "Database" refers to a data storage and management system that supports relational models and aggregate functions, such as the PostgreSQL database. "Main table data" refers to the data corresponding to each field in the main table list. "Detailed data" refers to the detailed data corresponding to each detailed table added to the main table list.

[0032] Specifically, after saving the main table list configuration, the frontend sends a data retrieval request to the server when loading the main table list. Based on the main table list configuration, the server first concatenates the columns corresponding to each field in the main table list using an SQL query statement to obtain the first concatenated SQL query statement. Then, using the STRING_AGG function, it aggregates the detailed data corresponding to each detailed table into a single string according to the configuration of each detailed table, and concatenates this string into the first concatenated SQL query statement, dynamically constructing a complex, complete SQL query statement containing aggregate functions. This complete SQL query statement is then sent to the database for execution. The database returns a result set, where each row contains the main table data and the aggregated detailed data (strings). By concatenating the SQL, the server retrieves the data from both the main table and detailed tables from the database in one go, eliminating the need for multiple database queries and in-memory processing of the relationship between the main table data and the detailed data. The server then returns the retrieved main table data and detailed data to the frontend.

[0033] In this embodiment, a data retrieval request is sent to the server. Based on the configuration of the main table list, the server constructs an SQL query statement using aggregate functions. Based on the constructed complete SQL query statement, it retrieves both the main table data and the detailed data from the database. By constructing the SQL query, data from both the main table and the detailed table is retrieved from the database in one step, eliminating the need for multiple database queries and reprocessing the relationship between the main table data and the detailed data in memory. This reduces the number of database queries, improves query performance, and avoids placing excessive pressure on the database.

[0034] S140. Receive the main table data and the detailed data, and display the main table data and the detailed data in the same row of the main table list.

[0035] Specifically, after receiving the main table data and detailed data, the front-end displays the main table data and the detailed data in the same row of the main table list according to the configuration of the main table list. For example, Table 1 shows the front-end display effect with the service order main table, product detail sub-table, and payment detail sub-table as an example. The product detail sub-table is configured as "product name - product quantity - product unit price", and the payment detail sub-table is configured as "payment method - amount".

[0036] Table 1

[0037] This allows users to clearly and intuitively view the main table data and detailed data when viewing the service list, meeting their needs for quick viewing and analysis of detailed table data and reducing the need to navigate to the details page. In this embodiment, a master table and detail tables are pre-created, with one master table corresponding to multiple detail tables. Based on the master table's fields and the corresponding detail tables, the master table list is configured, and each detail table added to the master table list is also configured. A data retrieval request is sent to the server. The server, based on the master table list configuration, constructs an SQL query using aggregation functions. Based on the constructed complete SQL query, it retrieves master table data and detail data from the database. The master table data and detail data are received, and they are displayed in the same row of the master table list. This solves the problems of low efficiency and inflexible configuration of detail display methods in existing technologies. By constructing SQL, the data from both the master and detail tables is retrieved from the database in one step, eliminating the need for multiple database queries and reprocessing the relationship between master table data and detail data in memory. This reduces the number of database queries, improves query performance, avoids excessive pressure on the database, provides a more intuitive view of master and detail table data, and reduces the need to navigate to details.

[0038] Optionally, configuring the main table list based on the main table fields and the corresponding detail tables includes: Enter the main table list design page and identify the main table and the multiple detail tables corresponding to the main table; The main table fields selected by the user and the detail table are added as columns in the main table list design page.

[0039] The main table list design page can refer to the "list design" or "list configuration" function page displayed on the front-end interface.

[0040] Specifically, users can select a specific master table (e.g., service order master table, order master table, etc.) from the front-end interface and enter the master table list design page. The system will automatically recognize the selected master table, such as the service order master table or the order master table. The automatically recognized master table fields will be displayed to the user in a draggable component panel, for example, the master table field area: including all fields of the master table, such as service order number, customer name, service time, etc. Users can drag and drop the required fields from the "master table field area" to the "design preview area" in the master table list design page. Each successful drag and drop adds a column to the master table list. After all the master table fields selected by the user are configured in the master table list, the system will automatically query multiple detail tables that have a one-to-many relationship with the master table based on the unique identifier of the master table (service order number), and display them to the user in a draggable component panel, for example, the detail table resource area: including all recognized related detail tables, such as product detail table and service log detail table, and each detail table can be an expandable or configurable whole object. You can drag and drop detail tables from the "Detail Table Resource Area" to the "Design Preview Area" on the main table list design page, and add each dragged detail table as a separate column to the main table list.

[0041] In this embodiment, the main table list design page is accessed, the main table and multiple detail tables corresponding to the main table are identified, and the main table fields selected by the user and the detail tables are added as columns in the main table list to the main table list design page. This achieves a visual, no-code configuration process, simplifies configuration steps, improves operational efficiency, increases flexibility, and enhances user satisfaction.

[0042] Optionally, configuring each of the detail tables added to the main table list includes: Determine which of the detailed tables will be added to the main table list; Configure the display fields, sorting fields, separators between the display fields, and separators between the detailed data corresponding to each detailed table.

[0043] The display fields refer to the detailed fields selected from the detail table, which are the detailed information to be displayed in the main table list; for example, in a product detail table, fields such as "Product Name," "Product Quantity," and "Product Unit Price" can be selected. The sorting field refers to the sorting field determined from the display fields in the detail table, and the sorting method can be selected; for example, the sorting field can be set to "Product Quantity," and the sorting method can be selected as ascending or descending. The separator between the display fields refers to the separator setting between each display field, for example, it can be "-". The separator between the corresponding detailed data in the detail table can be set to ";".

[0044] Specifically, after determining the multiple detail tables to be added to the main table list, select fields from the detail tables as the product detail information to be displayed in the service order list. The granularity of the information to be displayed can be determined according to the business scenario. For example, for the product detail table, the user can choose to display the three fields "Product Name", "Product Quantity", and "Product Unit Price" as display fields. Specify one of the display fields as the sorting criterion, which can be ascending or descending. Define a character to separate different display fields within a detail table; define another character to separate different detail data within a detail table. For example, taking the product detail table as an example, the final displayed product detail data format is "Product Name A - Product Quantity A - Product Unit Price A; Product Name B - Product Quantity B - Product Unit Price B".

[0045] In this embodiment, multiple detail tables are determined to be added to the main table list; the display fields, sorting fields, separators between the display fields, and separators between the detail data corresponding to each detail table are configured; based on the configuration, database aggregation functions are used to obtain integrated data in one go, without multiple database queries and without reprocessing the relationship between the main table data and the detail data in memory, reducing the number of database queries, improving query performance, avoiding excessive pressure on the database, and allowing users to flexibly configure the display of detail data to meet the diverse needs of different users.

[0046] For example, Figure 2 A flowchart illustrating a specific method for displaying master table data and detail data according to Embodiment 1 of the present invention is shown below. Figure 2 As shown, the specific process for displaying main table data and detailed data in the main table list can be as follows: 1. Create a master table and a detail table, and establish a one-to-many relationship.

[0047] 2. Go to the list configuration of the main table, drag in the columns to be displayed and configure the display fields / sorting / separator of the detail table.

[0048] 3. Save the list configuration of the main table.

[0049] 4. After the front-end renders the list, initiate a data retrieval request.

[0050] 5. The server retrieves the required main table data and detailed data from the database in one go according to the configuration (that is, the server uses aggregate functions to construct the SQL query statement based on the main table list configuration, and then retrieves the main table data and detailed data from the database based on the constructed complete SQL query statement).

[0051] 6. Return the data to the front end for display.

[0052] In this embodiment, a master table and detail tables are pre-created, with one master table corresponding to multiple detail tables. Based on the master table's fields and the corresponding detail tables, the master table list is configured, and each detail table added to the master table list is also configured. The master table list configuration is saved, and a data retrieval request is sent to the server. The server, based on the master table list configuration, constructs an SQL query statement using aggregation functions. Based on the constructed complete SQL query statement, it retrieves master table data and detail data from the database. The server receives the master table data and detail data and displays them in the same row of the master table list. This solves the problems of low efficiency and inflexible configuration of detail display methods in existing technologies. By retrieving and processing data from the database at once, it avoids processing the relationship between the master table and details in memory, improving data processing efficiency. Simultaneously, it allows users to flexibly configure the display of detail data to meet the diverse needs of different users.

[0053] Example 2:

[0054] Figure 3 This is a flowchart illustrating a method for displaying master table data and detail data according to Embodiment 2 of the present invention. The technical solution of this embodiment is further refined based on the above embodiments. Figure 3 As shown, the method includes: S210. Pre-create the master table and detail tables, where one master table corresponds to multiple detail tables; S220. Based on the main table fields of the main table and the corresponding detail tables of the main table, configure the main table list and configure each detail table added to the main table list; S230. A data acquisition request is sent to the server. The server constructs the SQL query statement based on the main table fields in the main table list configuration to obtain the first SQL query statement.

[0055] The first SQL query statement can refer to an SQL statement that only includes the logic of querying the main table fields in the main table list, without adding the detail table aggregation query.

[0056] Specifically, after saving the main table list configuration, when the front-end loads the main table list, it sends a data retrieval request to the server. Based on the main table list configuration, the server parses out all the main table fields selected by the user that need to be displayed in the main table list, and dynamically generates a first concatenated SQL query statement that only contains the logic of querying the main table fields in the main table list through string manipulation or by using an SQL template engine.

[0057] In this embodiment, by sending a data acquisition request to the server, the server constructs the SQL query statement based on the main table fields in the main table list configuration to obtain the first SQL query statement; that is, the logic for processing the main table and the logic for processing each detailed table can be separated, and the SQL construction process is decomposed into clear steps (main table first, detailed table later), making the code logic clearer and easier to maintain and extend.

[0058] S240. Determine whether a detail table exists in the main table list configuration; Specifically, the server parses the configuration information of the main table list to determine whether it contains the configuration items of the detail table. If it does not contain them, the server directly uses the first SQL query statement (i.e., the SQL that only queries the fields of the main table) as the complete SQL query statement and executes the first SQL query statement to perform the query.

[0059] In this embodiment, by determining whether a detail table exists in the main table list configuration, unnecessary resource consumption is avoided, and the response efficiency in simple query scenarios is greatly improved.

[0060] S250. If it exists, then based on the configuration of each detail table, the first SQL query statement is concatenated using an aggregation function to obtain the complete SQL query statement.

[0061] A complete SQL query statement refers to an SQL statement that contains all query logic, can be directly executed by the database engine, and returns the final result. It consists of the first SQL query statement and one or more aggregate query clauses dynamically generated based on the configuration of each detail table.

[0062] Specifically, if the configuration information in the main table list contains detailed table configuration items, then each detailed table configuration in the main table list is looped through, and an aggregate query SQL fragment is generated for each detailed table configuration using an aggregate function. The SQL fragments corresponding to each detailed table configuration are then appended to the first SQL query statement to obtain the complete SQL query statement.

[0063] In this embodiment, if a detail table exists in the main table list, the first SQL query statement is concatenated using an aggregation function based on the configuration of each detail table to obtain the complete SQL query statement. The complete SQL query statement retrieves the data from the main table and detail table from the database at once, without needing to query the database multiple times, and without needing to process the relationship between the main table data and the detail data again in memory.

[0064] S260. By executing the complete SQL query statement, retrieve the main table data and detailed data from the database.

[0065] Specifically, the server sends a complete SQL query to the database and requests its execution. After the database completes the execution, it returns a result set containing all the data required for the front-end list display, namely the main table data corresponding to the main table fields and the detailed data corresponding to all the detailed tables.

[0066] In this embodiment, data from the main table and detail table can be retrieved from the database in one go by concatenating a complete SQL query statement, without the need for multiple database queries and without having to process the relationship between the main table data and detail data in memory again.

[0067] S270. Receive the main table data and detailed data, and based on the configuration of the main table list, display the main table data and the detailed data in the same row of the main table list.

[0068] Specifically, the result dataset returned by the server is typically a tabular data structure. Each row in the result dataset represents a record, including the main table data and the aggregated detail data strings. A single row may contain multiple detail data strings corresponding to different detail tables. After receiving the result dataset, the front-end iterates through each row. For each row, it directly populates the corresponding main table list with the main table data. Then, based on the configuration of each detail table in the main table list (i.e., sorting fields, separators between display fields, and separators between the detail data in each detail table), it renders the detail data onto the same row corresponding to the main table data for display.

[0069] In this embodiment, a data acquisition request is sent to the server. The server constructs the SQL query statement based on the main table fields in the main table list configuration to obtain a first SQL query statement. It then determines whether a detail table exists in the main table list configuration. If it does, the server constructs the first SQL query statement using aggregation functions based on the configuration of each detail table to obtain the complete SQL query statement. The complete SQL query statement is executed to retrieve main table data and detail data from the database. This method retrieves data from both the main table and detail tables in a single query, eliminating the need for multiple database queries and reprocessing the relationship between main table data and detail data in memory. This reduces the number of database queries, improves query performance, and avoids putting excessive pressure on the database.

[0070] Optionally, if present, the complete SQL query statement is obtained by concatenating the configuration of the first SQL query statement using aggregate functions based on the configuration of each detail table, including: If it exists, then iterate through each detail table configuration in the main table list and generate an aggregate subquery corresponding to each detail table configuration using an aggregate function; Each of the aforementioned aggregate subqueries is then concatenated into the first SQL query statement in a loop; The complete SQL query statement is obtained after all the detailed tables in the main table list have been configured and aggregated.

[0071] Among them, aggregate subqueries can refer to Specifically, if the main table list configuration information includes detailed table configuration items, then each detailed table configuration in the main table list is iterated through. Aggregate functions are used to read each detailed table configuration, extracting the display fields, sorting fields, separators between display fields, and separators between the detailed data corresponding to each detailed table from the configuration object. Based on the content of each detailed table configuration, a string corresponding to each detailed table is dynamically generated; this string is a complete SQL subquery fragment. Within the loop, each time a string of an aggregate subquery is generated, it is immediately appended to the end of the first SQL query statement. When appending, a comma "," needs to be added before the subquery fragment to separate it from the preceding fields. The loop ends after processing the last detailed table configuration in the main table list and appending the last aggregate subquery fragment to the SQL, resulting in the complete SQL query statement.

[0072] In this embodiment, by integrating all association, sorting, and aggregation calculations into a single SQL statement and pushing it down to the database for execution, the optimization and computing capabilities of the database engine are maximized. This significantly reduces the number of database queries, improves query performance, avoids putting excessive pressure on the database, and uses a loop-based processing approach, enabling the system to easily support the configuration of multiple detail tables and meet the diverse needs of different users.

[0073] For example, Figure 4 This is a data query flowchart provided in Embodiment 2 of the present invention, such as... Figure 4 As shown, the server receives a data retrieval request, constructs an SQL query statement based on the main table list configuration, and then retrieves the main table data and detailed data from the database based on the constructed complete SQL query statement. The specific process can be as follows: 1. Receive front-end data query requests (i.e., data retrieval requests).

[0074] 2. Query the main table list configuration.

[0075] 3. Concatenate the columns of the main table in the SQL configuration (i.e., concatenate the main table fields in the main table list configuration to obtain the first SQL query statement).

[0076] 4. Determine if there is a detailed list.

[0077] 5. If so, loop through the configured detail tables and concatenate the query SQL according to the configuration rules (fields, sorting, delimiters) and the string_agg function (that is, based on the configuration of each detail table, concatenate the first SQL query statement through the aggregation function to obtain the complete SQL query statement).

[0078] 6. Execute a database query.

[0079] 7. The data is returned to the front end and rendered into a list.

[0080] In this embodiment, the server receives a data retrieval request, constructs the SQL query statement based on the main table fields in the main table list configuration, and obtains a first SQL query statement; it then determines whether a detail table exists in the main table list configuration; if it does, it constructs the first SQL query statement based on the configuration of each detail table using an aggregation function, and obtains the complete SQL query statement; by executing the complete SQL query statement, it retrieves the main table data and detail data from the database; by executing the complete SQL query statement, it retrieves the data from the main table and detail table from the database in one go, without needing to query the database multiple times, and without needing to process the relationship between the main table data and detail data again in memory; this reduces the number of database queries, improves query performance, and avoids putting a lot of pressure on the database.

[0081] In a specific embodiment, taking the data display scenario of the service order master table and product detail sub-table in a certain enterprise CRM system as an example, the specific implementation process of the display method of master table data and detail data is described in detail: 1. System Data Structure Preparation: The CRM system's database has pre-established a service order master table and a product detail table. The service order master table includes fields such as service order number, customer name, service type, and service time, with the "service order number" serving as the unique identifier. The product detail table includes fields such as service order number, product number, product name, product quantity, and product unit price. A one-to-many relationship is established between the service order master table and the "service order number" field, meaning one service order may correspond to multiple product details. 2. View Design Page Operation: System administrators or business personnel access the CRM system's view design page to configure the list display of service order data. First, the system automatically identifies the one-to-many relationship between the service order master table and the product detail table. Then, the product detail table is dragged as a column into the view design area of ​​the service order master table, where detailed settings are made. Display field settings: Select the "Product Name", "Product Quantity", and "Product Unit Price" fields from the product details table as the product details information to be displayed in the service order list; at the same time, select the "Service Order Number", "Customer Name", and "Service Time" fields from the main service order table for display, so that users can quickly locate and identify the service order. Sorting field settings: Set the sorting field of the product details table to "Product Quantity" and select ascending order as the sorting method so that the product details can be displayed in the service order list in order of product quantity from least to most. Delimiter settings: Set the delimiter between fields to "-" and the delimiter between data to ";", that is, the final product detail data format will be "Product Name-Product Quantity-Product Unit Price;Product Name-Product Quantity-Product Unit Price". 3. Database Data Acquisition and Processing: When a user triggers the loading of service order list data on the CRM system front-end page, the system executes a query operation in the database based on the configuration information on the view design page. Using the `string_agg` function of PostgreSQL, data aggregation is performed based on the one-to-many relationship between the service order master table and the product details table. A specific SQL query example is as follows: The SQL statement is defined as follows: `SELECT Service Item Main Table.Service Item Number, Service Item Main Table.Customer Name, Service Item Main Table.Service Time, (SELECT STRING_AGG(CONCAT(Product Details Table.Product Name, '-', Product Details Table.Product Quantity, '-', Product Details Table.Product Unit Price), ';' ORDER BY Product Details Table.Product Quantity DESC) FROM Product Details Table WHERE Service Item Main Table.Service Item Number = Product Details Table.Service Item Number) AS Product Details Aggregated Data FROM Service Item Main Table;` This statement retrieves related data through table joins and uses the STRING_AGG function to aggregate product detail data into a single string according to the set fields, sorting fields, and delimiters, retrieving the integrated service order and corresponding product detail data from the database in one go. 4. Front-end Data Display: The database returns the integrated query results to the CRM system's front-end page. The front-end page parses and displays the service order master table data and the aggregated product detail data. For example, the display effect of a service order can be seen in Table 1. When users view the service order list, they can clearly and intuitively obtain the basic information of the service order and the associated product details, meeting the needs of business personnel for quick viewing and analysis of service order data. This embodiment, through a specific implementation process in a CRM system using service orders and product details display as examples, fully demonstrates how to pre-create a master table and detail tables, configure the master table list based on the master table fields and the corresponding detail tables, and configure each detail table added to the master table list; send a data retrieval request to the server, which, based on the master table list configuration, constructs an SQL query statement using aggregation functions, and retrieves master table data and detail data from the database based on the constructed complete SQL query statement; receives the master table data and detail data, and displays them in the same row of the master table list. This effectively solves the shortcomings of traditional methods in terms of data display efficiency and flexibility. By retrieving and processing data from the database at once, it avoids processing the relationship between the master table and details in memory, improving data processing efficiency. It also allows users to flexibly configure the display of detail data to meet the diverse needs of different users, and has significant practicality and promotional value.

[0082] Example 3:

[0083] Figure 5 This is a framework diagram of a system for displaying master table data and detail data according to Embodiment 3 of the present invention. Figure 5 As shown, the system includes: a creation module 310, a configuration module 320, an acquisition module 330, and a display module 340. The creation module 310 is connected to the configuration module 320, the configuration module 320 is connected to the acquisition module 330, and the acquisition module 330 is connected to the display module 340. The system is used to execute the method for displaying master table data and detailed data as described in any embodiment of the present invention, specifically including: Create module 310 to pre-create the master table and detail tables, where one master table corresponds to multiple detail tables; Configuration module 320 is used to configure the main table list based on the main table fields of the main table and the corresponding detail tables of the main table, and to configure each detail table added to the main table list; The acquisition module 330 is used to send a data acquisition request to the server. The server, based on the configuration of the main table list, constructs an SQL query statement using an aggregation function, and retrieves the main table data and detailed data from the database based on the constructed complete SQL query statement. Display module 340 is used to receive the main table data and the detailed data, and display the main table data and the detailed data in the same row of the main table list. Optional, configuration module 320, specifically used for: Enter the main table list design page and identify the main table and the multiple detail tables corresponding to the main table; The main table fields selected by the user and the detail table are added as columns in the main table list design page.

[0084] Optional, configuration module 320, specifically used for: Determine which of the detailed tables will be added to the main table list; Configure the display fields, sorting fields, separators between the display fields, and separators between the detailed data corresponding to each detailed table.

[0085] Optionally, module 330 is used for: A data retrieval request is sent to the server, and the server constructs the SQL query statement based on the main table fields in the main table list configuration to obtain the first SQL query statement; Determine whether a detail table exists in the main table list configuration; If it exists, the first SQL query statement is concatenated using an aggregation function based on the configuration of each detail table to obtain the complete SQL query statement; By executing the complete SQL query statement, the main table data and detailed data are retrieved from the database.

[0086] Optionally, module 330 is used for: If it exists, then iterate through each detail table configuration in the main table list and generate an aggregate subquery corresponding to each detail table configuration using an aggregate function; Each of the aforementioned aggregate subqueries is then concatenated into the first SQL query statement in a loop; The complete SQL query statement is obtained after all the detailed tables in the main table list have been configured and aggregated.

[0087] The main table data and detailed data display system provided in this embodiment of the invention can execute the main table data and detailed data display method provided in any embodiment of the invention, and has the corresponding functional modules and beneficial effects of the execution method.

[0088] Example 4:

[0089] Figure 6A schematic diagram of an electronic device 10, which can be used to implement embodiments of the present invention, is shown. The electronic device is intended to represent various forms of digital computers, such as laptop computers, desktop computers, workstations, personal digital assistants, servers, blade servers, mainframe computers, and other suitable computers. The electronic device can also represent various forms of mobile devices, such as personal digital processors, cellular phones, smartphones, wearable devices (e.g., helmets, glasses, watches, etc.), and other similar computing devices. The components shown herein, their connections and relationships, and their functions are merely illustrative and are not intended to limit the implementation of the invention described and / or claimed herein.

[0090] like Figure 6 As shown, the electronic device 10 includes at least one processor 11 and a memory, such as a read-only memory (ROM) 12, a random access memory (RAM) 13, etc., which is communicatively connected to the at least one processor 11. The memory stores a computer program that can be executed by the at least one processor 11, and the computer program is executed by the at least one processor 11 to enable the at least one processor 11 to perform the method provided by the present invention.

[0091] The processor 11 can perform various appropriate actions and processes based on a computer program stored in the read-only memory (ROM) 12 or a computer program loaded from the storage unit 18 into the random access memory (RAM) 13. The RAM 13 can also store various programs and data required for the operation of the electronic device 10. The processor 11, ROM 12, and RAM 13 are interconnected via a bus 14. An input / output (I / O) interface 15 is also connected to the bus 14.

[0092] Multiple components in electronic device 10 are connected to I / O interface 15, including: input unit 16, such as keyboard, mouse, etc.; output unit 17, such as various types of displays, speakers, etc.; storage unit 18, such as disk, optical disk, etc.; and communication unit 19, such as network card, modem, wireless transceiver, etc. Communication unit 19 allows electronic device 10 to exchange information / data with other devices through computer networks such as the Internet and / or various telecommunications networks.

[0093] Processor 11 can be a variety of general-purpose and / or special-purpose processing components with processing and computing capabilities. Some examples of processor 11 include, but are not limited to, a central processing unit (CPU), a graphics processing unit (GPU), various special-purpose artificial intelligence (AI) computing chips, various processors running machine learning model algorithms, digital signal processors (DSPs), and any suitable processor, controller, microcontroller, etc. Processor 11 performs the various methods and processes described above, such as methods for displaying master table data and detailed data.

[0094] In some embodiments, the method for displaying master table data and detail data can be implemented as a computer program tangibly contained in a computer-readable storage medium, such as storage unit 18. In some embodiments, part or all of the computer program can be loaded and / or installed on electronic device 10 via ROM 12 and / or communication unit 19. When the computer program is loaded into RAM 13 and executed by processor 11, one or more steps of the method for displaying master table data and detail data described above can be performed. Alternatively, in other embodiments, processor 11 can be configured to perform a method for displaying master table data and detail data by any other suitable means (e.g., by means of firmware).

[0095] Various embodiments of the systems and techniques described above herein can be implemented in digital electronic circuit systems, integrated circuit systems, field programmable gate arrays (FPGAs), application-specific integrated circuits (ASICs), application-specific standard parts (ASSPs), systems-on-chip (SoCs), complex programmable logic devices (CPLDs), computer hardware, firmware, software, and / or combinations thereof. These various embodiments may include implementations in one or more computer programs that can be executed and / or interpreted on a programmable system including at least one programmable processor, which may be a dedicated or general-purpose programmable processor, capable of receiving data and instructions from a storage system, at least one input device, and at least one output device, and transmitting data and instructions to the storage system, the at least one input device, and the at least one output device.

[0096] Computer programs used to implement the methods of the present invention may be written in any combination of one or more programming languages. These computer programs may be provided to a processor of a general-purpose computer, a special-purpose computer, or other programmable data processing device, such that when executed by the processor, the computer programs cause the functions / operations specified in the flowcharts and / or block diagrams to be performed. The computer programs may be executed entirely on a machine, partially on a machine, or as a standalone software package, partially on a machine and partially on a remote machine, or entirely on a remote machine or server.

[0097] In the context of this invention, a computer-readable storage medium stores computer instructions that, when executed by a processor, implement the method for displaying master table data and detail data provided by this invention. The computer-readable storage medium may be a tangible medium that may contain or store a computer program for use by or in conjunction with an instruction execution system, apparatus, or device. The computer-readable storage medium may include, but is not limited to, electronic, magnetic, optical, electromagnetic, infrared, or semiconductor systems, apparatus, or devices, or any suitable combination thereof. Alternatively, the computer-readable storage medium may be a machine-readable signal medium. More specific examples of machine-readable storage media include electrical connections based on one or more wires, portable computer disks, hard disks, random access memory (RAM), read-only memory (ROM), erasable programmable read-only memory (EPROM or flash memory), optical fibers, portable compact disk read-only memory (CD-ROM), optical storage devices, magnetic storage devices, or any suitable combination thereof.

[0098] To provide interaction with a user, the systems and techniques described herein can be implemented on an electronic device having: a display device (e.g., a cathode ray tube (CRT) or a liquid crystal display (LCD)) for displaying information to the user; and a keyboard and pointing device (e.g., a mouse or trackball) through which the user provides input to the electronic device. Other types of devices can also be used to provide interaction with the user; for example, feedback provided to the user can be any form of sensory feedback (e.g., visual feedback, auditory feedback, or tactile feedback); and input from the user can be received in any form (including sound input, voice input, or tactile input).

[0099] The systems and technologies described herein can be implemented in computing systems that include backend components (e.g., as data servers), or middleware components (e.g., application servers), or frontend components (e.g., user computers with graphical user interfaces or web browsers through which users can interact with implementations of the systems and technologies described herein), or any combination of such backend, middleware, or frontend components. The components of the system can be interconnected via digital data communication of any form or medium (e.g., communication networks). Examples of communication networks include local area networks (LANs), wide area networks (WANs), blockchain networks, and the Internet.

[0100] A computing system can include clients and servers. Clients and servers are generally geographically separated and typically interact via communication networks. The client-server relationship is established by computer programs running on the respective computers and having a client-server relationship with each other. The server can be a cloud server, also known as a cloud computing server or cloud host, which is a hosting product within the cloud computing service system. It addresses the shortcomings of traditional physical hosts and Virtual Private Server (VPS) services, such as high management difficulty and weak business scalability.

[0101] It should be understood that the various forms of processes shown above can be used, with steps reordered, added, or deleted. For example, the steps described in this invention can be executed in parallel, sequentially, or in different orders, as long as the desired result of the technical solution of this invention can be achieved, and this is not limited herein.

[0102] The specific embodiments described above do not constitute a limitation on the scope of protection of this invention. Those skilled in the art should understand that various modifications, combinations, sub-combinations, and substitutions can be made according to design requirements and other factors. Any modifications, equivalent substitutions, and improvements made within the spirit and principles of this invention should be included within the scope of protection of this invention.

Claims

1. A method for displaying master table data and detailed data, characterized in that, include: Pre-create a master table and detail tables, where one master table corresponds to multiple detail tables; Based on the main table fields of the main table and the corresponding detail tables of the main table, the main table list is configured, and the detail tables added to the main table list are configured. A data retrieval request is sent to the server. The server, based on the configuration of the main table list, constructs an SQL query statement using aggregate functions. Based on the constructed complete SQL query statement, it retrieves the main table data and detailed data from the database. Receive the main table data and the detailed data, and display the main table data and the detailed data in the same row of the main table list.

2. The method for displaying master table data and detailed data according to claim 1, characterized in that, The configuration of the main table list based on the main table fields and the corresponding detail tables includes: Enter the main table list design page and identify the main table and the multiple detail tables corresponding to the main table; The main table fields selected by the user and the detail table are added as columns in the main table list design page.

3. The method for displaying master table data and detailed data according to claim 1, characterized in that, The configuration of each detail table added to the main table list includes: Determine which of the detailed tables will be added to the main table list; Configure the display fields, sorting fields, separators between the display fields, and separators between the detailed data corresponding to each detailed table.

4. The method for displaying master table data and detailed data according to claim 1, characterized in that, The data retrieval request is sent to the server. Based on the configuration of the main table list, the server constructs an SQL query statement using aggregate functions. Based on the constructed complete SQL query statement, it retrieves main table data and detailed data from the database, including: A data retrieval request is sent to the server, and the server constructs the SQL query statement based on the main table fields in the main table list configuration to obtain the first SQL query statement; Determine whether a detail table exists in the main table list configuration; If it exists, the first SQL query statement is concatenated using an aggregation function based on the configuration of each detail table to obtain the complete SQL query statement; By executing the complete SQL query statement, the main table data and detailed data are retrieved from the database.

5. The method for displaying master table data and detailed data according to claim 4, characterized in that, If it exists, then based on the configuration of each detail table, the first SQL query statement is concatenated using aggregate functions to obtain the complete SQL query statement, including: If it exists, then iterate through each detail table configuration in the main table list and generate an aggregate subquery corresponding to each detail table configuration using an aggregate function; Each of the aforementioned aggregate subqueries is then concatenated into the first SQL query statement in a loop; The complete SQL query statement is obtained after all the detailed tables in the main table list have been configured and aggregated.

6. The method for displaying master table data and detailed data according to claim 1, characterized in that, The step of receiving the main table data and the detailed data, and displaying the main table data and the detailed data in the same row of the main table list, includes: Receive the main table data and detailed data, and display the main table data and detailed data in the same row of the main table list based on the configuration of the main table list.

7. A system for displaying master table data and detailed data, characterized in that, The system is used to execute the method for displaying master table data and detail data according to any one of claims 1-6, including: The creation module is used to pre-create the master table and detail tables, where one master table corresponds to multiple detail tables; The configuration module is used to configure the main table list based on the main table fields and the corresponding detail tables of the main table, and to configure each detail table added to the main table list. The acquisition module is used to send data acquisition requests to the server. The server, based on the configuration of the main table list, constructs an SQL query statement using aggregation functions, and retrieves the main table data and detailed data from the database based on the constructed complete SQL query statement. The display module is used to receive the main table data and the detailed data, and display the main table data and the detailed data in the same row of the main table list.

8. The display system for master table data and detailed data according to claim 7, characterized in that, The acquisition module is specifically used for: A data retrieval request is sent to the server, and the server constructs the SQL query statement based on the main table fields in the main table list configuration to obtain the first SQL query statement; Determine whether a detail table exists in the main table list configuration; If it exists, the first SQL query statement is concatenated using an aggregation function based on the configuration of each detail table to obtain the complete SQL query statement; By executing the complete SQL query statement, the main table data and detailed data are retrieved from the database.

9. An electronic device, characterized in that, include: At least one processor; as well as A memory communicatively connected to the at least one processor; wherein, The memory stores a computer program that can be executed by the at least one processor, the computer program being executed by the at least one processor to enable the at least one processor to perform the method for displaying master table data and detail data as described in any one of claims 1-6.

10. A computer-readable storage medium, characterized in that, The computer-readable storage medium stores computer instructions that, when executed by a processor, implement the method for displaying master table data and detail data as described in any one of claims 1-6.

Images