Report SQL statement generation method and device, and electronic equipment

Abstract

The application provides a report SQL statement generation method and device and electronic equipment, wherein the method comprises the following steps: obtaining a chain directed graph of multiple reports edited by a business staff; the chain directed graph comprises multiple report nodes and a connection line; the report node comprises the following attributes: a report identifier; the connection line comprises the following attributes: a report identifier in a connection line head end report node, a report identifier in a connection line tail end report node, a connection mode of the two end reports of the connection line, and a connection field of the two end reports of the connection line; obtaining a filtering condition selected by the business staff through a man-machine interaction interface and transmitted to a background; based on the report sequence in the chain directed graph, whether a filtering element for reaching the filtering condition exists in each report is sequentially queried; and a target SQL statement is spliced according to the query result and the attributes of the connection line in the chain directed graph. The scheme can conveniently and flexibly adjust the SQL statement generation mode, and can avoid missing filtering element information and is not prone to bugs.

Description

Technical Field

[0001] This application relates to the field of automation technology, and in particular to methods, apparatus and electronic devices for generating SQL statements for reports. Background Technology

[0002] Larger companies typically have a wide variety and large volume of business transactions, resulting in a large number of report creation tasks for their business personnel. Since business personnel usually lack database operation skills, they cannot write SQL statements to create reports themselves. Therefore, technical personnel usually need to write general-purpose SQL statements and interactive code for business personnel to call these SQL statements. Only then can business personnel obtain and execute the SQL statements according to their actual needs, thereby completing the report creation task.

[0003] However, creating reports using existing SQL statement generation solutions is prone to bugs and lacks flexibility. Summary of the Invention

[0004] The purpose of this application is to provide a method, apparatus, and electronic device for generating SQL statements for reports, in order to solve the problems of existing SQL statement generation solutions being prone to bugs and lacking flexibility in report creation.

[0005] To address the aforementioned technical problems, this specification provides a method for generating reports using SQL statements, comprising: obtaining a chained directed graph of multiple reports edited by business personnel; the chained directed graph includes multiple report nodes and connections, the report nodes including the following attributes: report identifier, and the connections including the following attributes: report identifier in the report node at the beginning of the connection, report identifier in the report node at the end of the connection, connection method of the reports at both ends of the connection, and connection field of the reports at both ends of the connection;

[0006] The system obtains the filtering conditions selected and transmitted to the backend by business personnel through the human-computer interaction interface; based on the report order in the chained directed graph, it sequentially queries whether there are filtering elements in each report that meet the filtering conditions; and according to the query results and the attributes of the connecting lines in the chained directed graph, it concatenates them to form the target SQL statement.

[0007] In some embodiments, the target SQL statement is formed by concatenating the query results and the attributes of the connections in the chained directed graph, including: repeatedly executing the following steps until the tail node of the chained directed graph is reached, concatenating the filter elements corresponding to each report node into the SQL statement to form the target SQL statement: querying whether there are filter elements in the current report node in the chained directed graph, and recording the filter elements corresponding to the current report node if so; determining whether there are connections after the current report node in the chained directed graph; if so, locating the next report node according to the connection and taking the next report node as the current report node; if not, determining that the tail node of the chained directed graph has been reached.

[0008] In some embodiments, the SQL filtering elements include fields selected by business personnel that need to be queried and displayed; correspondingly, based on the query results and the attributes of the connections in the chained directed graph, a target SQL statement is formed by concatenating the following steps: cyclically executing the following steps until the tail node of the chained directed graph is reached, concatenating the filtered fields corresponding to each report node to form a select statement segment; querying the fields in the report corresponding to the report identifier in the current report node of the chained directed graph, and filtering out the fields selected by the business personnel; determining whether there is a connection after the current report node in the chained directed graph; if yes, locating the next report node based on the connection, and using the next report node as the current report node; if no, determining that the tail node of the chained directed graph has been reached.

[0009] In some embodiments, the SQL filtering elements include the name of the table containing the fields that the business personnel select to be queried; correspondingly, based on the query results and the attributes of the connections in the chained directed graph, a target SQL statement is formed by concatenating the following steps: cyclically executing the following steps until the tail node of the chained directed graph is reached, concatenating the connection relationship statements corresponding to each report node into a from statement segment; determining whether there is a connection after the current report node in the chained directed graph; if there is, marking the connection relationship between the two reports associated with the connection according to the connection attributes, and locating the next report node according to the connection, and taking the next report node as the current report node; if there is no connection, determining that the tail node of the chained directed graph has been reached.

[0010] In some embodiments, the SQL filtering elements include filtering conditions, which are transmitted to the backend and used as target attributes in the corresponding report object. Accordingly, based on the query results and the attributes of the connections in the chained directed graph, a target SQL statement is formed by concatenating the following steps: cyclically executing the following steps until the tail node of the chained directed graph is reached, and then concatenating the obtained target attributes to form a WHERE clause segment; obtaining the target attribute in the report object corresponding to the report identifier in the current report node of the chained directed graph; determining whether there is a connection after the current report node in the chained directed graph; if yes, locating the next report node based on the connection and using the next report node as the current report node; if no, determining that the tail node of the chained directed graph has been reached.

[0011] In some embodiments, the chain-like directed graph is determined by a business user by dragging a graphical report component from a first area on a graphical editing interface to a second area, based on the positional relationship between the graphical report components in the second area.

[0012] A second aspect of this specification provides an apparatus for generating SQL statements for reports, comprising: a first acquisition unit for acquiring a chained directed graph of multiple reports edited by business personnel; the chained directed graph includes multiple report nodes and connections, the report nodes including the following attributes: report identifier, and the connections including the following attributes: report identifier in the report node at the beginning of the connection, report identifier in the report node at the end of the connection, connection method of the reports at both ends of the connection, and connection field of the reports at both ends of the connection; a second acquisition unit for acquiring filtering conditions selected and transmitted to the backend by business personnel through a human-computer interaction interface; a query unit for querying, based on the order of the reports in the chained directed graph, whether there are filtering elements in each report that meet the filtering conditions; and a splicing unit for splicing together, according to the query results and the attributes of the connections in the chained directed graph, to form a target SQL statement.

[0013] In some embodiments, the splicing unit includes: a first query subunit, a judgment subunit, a first determination subunit, and a second determination subunit, used to perform operations cyclically until the tail node of the chained directed graph is reached, and then splice the filter elements corresponding to each report node into the SQL statement to form the target SQL statement; wherein, the first query subunit is used to query whether there are filter elements in the current report node in the chained directed graph, and if so, record the filter elements corresponding to the current report node; the judgment subunit is used to determine whether there is a connection after the current report node in the chained directed graph; the first determination subunit is used to locate the next report node according to the connection if yes, and take the next report node as the current report node; the second determination subunit is used to determine the tail node of the chained directed graph if no.

[0014] In some embodiments, the SQL filtering elements include fields selected by business personnel that need to be queried and displayed; correspondingly, the concatenation unit includes: a second query subunit, a judgment subunit, a first determination subunit, and a second determination subunit, used to perform operations cyclically until the tail node of the chained directed graph is reached, and then concatenate the filtered fields corresponding to each report node to form a select statement segment; wherein, the second query subunit is used to query the fields in the report corresponding to the report identifier in the current report node in the chained directed graph, and filter out the fields selected by the business personnel; the judgment subunit is used to determine whether there is a connection after the current report node in the chained directed graph; the first determination subunit is used to locate the next report node according to the connection if yes, and take the next report node as the current report node; the second determination subunit is used to determine the tail node of the chained directed graph if no.

[0015] In some embodiments, the SQL filtering elements include the names of the tables containing the fields that the business personnel select to be queried; correspondingly, the concatenation unit includes: a judgment subunit, a marking subunit, and a second determination subunit, used to perform operations cyclically until the tail node of the chained directed graph is reached, and then concatenate the connection relationship statements corresponding to each report node to form a from statement segment; wherein, the judgment subunit is used to determine whether there is a connection after the current report node in the chained directed graph; the marking subunit is used to mark the connection relationship between the two reports associated with the connection according to the connection attribute if there is, and locate the next report node according to the connection, and take the next report node as the current report node; the second determination subunit is used to determine the tail node of the chained directed graph if there is no connection.

[0016] In some embodiments, the SQL filtering elements include filtering conditions, which are transmitted to the backend and used as target attributes in the corresponding report object. Correspondingly, the concatenation unit includes: an acquisition subunit, a judgment subunit, a first determination subunit, and a second determination subunit, used to perform operations cyclically until the tail node of the chained directed graph is reached, and then concatenating the acquired target attributes to form a WHERE clause segment. The acquisition subunit is used to acquire the target attribute in the report object corresponding to the report identifier in the current report node of the chained directed graph. The judgment subunit is used to determine whether there is a connection after the current report node in the chained directed graph. The first determination subunit, if yes, locates the next report node based on the connection and uses the next report node as the current report node. The second determination subunit, if no, determines that the tail node of the chained directed graph has been reached.

[0017] In some embodiments, the chain-like directed graph is determined by a business user by dragging a graphical report component from a first area on a graphical editing interface to a second area, based on the positional relationship between the graphical report components in the second area.

[0018] A third aspect of this specification provides an electronic device, comprising: a memory and a processor, wherein the processor and the memory are communicatively connected to each other, the memory stores computer instructions, and the processor executes the computer instructions to implement the steps of the method described in any of the first aspects.

[0019] A fourth aspect of this specification provides a computer storage medium storing computer program instructions that, when executed by a processor, implement the steps of the method described in any of the first aspects.

[0020] A fifth aspect of this specification provides a computer program product comprising a computer program that, when executed by a processor, implements the steps of the method described in any of the first aspects.

[0021] The report generation method, device, and electronic equipment provided in this manual use SQL statements to generate reports. SQL statements are formed by concatenating the report order and connection attributes in a chained directed graph. Since the chained directed graph is edited by business personnel, the SQL statement generation method can be adjusted conveniently and flexibly when database tables and their connections change. Querying and filtering elements based on the report order in the chained directed graph, rather than based on filtering conditions, avoids missing filtering element information. Attached Figure Description

[0022] To more clearly illustrate the technical solutions in the embodiments of this application or the prior art, the drawings used in the description of the embodiments or the prior art will be briefly introduced below. Obviously, the drawings described below are only some embodiments recorded in this application. For those skilled in the art, other drawings can be obtained from these drawings without creative effort.

[0023] Figure 1 A flowchart illustrating a method for generating reports using SQL statements provided in this specification is shown;

[0024] Figure 2 A schematic diagram of the three database tables stored in the database is shown;

[0025] Figure 3 A schematic diagram of a graphical editing interface for a chained directed graph is shown.

[0026] Figure 4A flowchart illustrating one implementation method of constructing a target SQL statement by piecing together the query results and the attributes of the connections in the chained directed graph;

[0027] Figure 5 A flowchart illustrating another implementation method for forming a target SQL statement by piecing together the query results and the attributes of the connections in the chained directed graph;

[0028] Figure 6 A flowchart illustrating another implementation method for forming a target SQL statement by piecing together the query results and the attributes of the connections in the chained directed graph;

[0029] Figure 7 A flowchart illustrating another implementation method for forming a target SQL statement by piecing together the query results and the attributes of the connections in the chained directed graph;

[0030] Figure 8 This specification shows a schematic block diagram of a report generation device using SQL statements.

[0031] Figure 9 A schematic block diagram of the electronic device provided in this specification is shown. Detailed Implementation

[0032] To enable those skilled in the art to better understand the technical solutions in this application, the technical solutions in the embodiments of this application will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only a part of the embodiments of this application, and not all of the embodiments. Based on the embodiments in this application, all other embodiments obtained by those skilled in the art without creative effort should fall within the scope of protection of this application.

[0033] The inventors discovered that in existing SQL statement generation solutions provided by technicians to business personnel, each data table is fixedly stored in a first array. When generating SQL based on the filter conditions selected by the business personnel, the array elements are traversed, and the filter elements associated with each array element are concatenated into the SQL statement. In some embodiments, the filter conditions selected by the business personnel are also stored in a second array, and the tables associated with each filter condition are searched, and the SQL statement is concatenated based on the search results. These two array-based operation methods result in poor scalability and a tendency to encounter problems in existing SQL statement generation solutions.

[0034] Based on the above findings, this specification provides a method for generating SQL statements for reports to solve the problem that existing solutions for generating SQL statements are prone to bugs when creating reports.

[0035] like Figure 1 As shown, the method for generating this report using SQL statements includes the following steps:

[0036] S10: Obtain a chained directed graph of multiple reports edited by business personnel; the chained directed graph includes multiple report nodes and connections. The report nodes include the following attributes: report identifier, and the connections include the following attributes: report identifier in the report node at the beginning of the connection, report identifier in the report node at the end of the connection, connection method of the reports at both ends of the connection, and connection field of the reports at both ends of the connection.

[0037] In the database, the database tables and the reports that business users see through the human-computer interaction interface have the same names and the same fields. For example, Figure 2 The database shows three tables: Report A (fields: a1, a2, a3), Report B (fields: b1, b2, b3), and Report C (fields: c1, c2, c3). At least some of the data in Report A, Report B, and Report C is visible to business users.

[0038] Figure 2 The directed graph connecting the three reports shown can be: A → B → C.

[0039] The chained directed graph between multiple reports can be edited by business users themselves on the human-computer interaction interface. For example, they can edit the report identifiers corresponding to the first, second, and third report nodes in the chained directed graph, and the background program can automatically determine the next report node for each report node based on the report node's sequence number.

[0040] In some embodiments, such as Figure 3 As shown, a graphical editing interface for a chain-directed graph can be provided on the human-computer interaction interface. This graphical editing interface includes a first area and a second area. The first area includes graphical report components of the graphical editing interface, which may include report node components, connection components, etc. The second area is the editing area for the chain-directed graph. Business users can drag the graphical report components in the first area to the second area and set the attributes of each graphical report component and the relationship between each graphical report component to obtain the chain-directed graph.

[0041] The attributes of the report node component can include: report identifier. The attributes of the connection component can include: the report identifier in the report node at the beginning of the connection, the report identifier in the report node at the end of the connection, the connection method of the reports at both ends of the connection (e.g., inner connection, left connection, right connection, etc.), and the connection fields of the reports at both ends of the connection.

[0042] Depend on Figure 3As shown in the edit area, the linked directed graph in this specification is not the same as the linked list storage structure used in data storage; they belong to different domains. In terms of code implementation, linked directed graphs can be implemented using either arrays or linked lists.

[0043] S20: Obtain the SQL filter elements selected and transmitted to the backend by business personnel through the human-computer interaction interface.

[0044] The SQL statement is typically: select X from Y where Z, where X represents the data field that the business personnel want to obtain, Y represents the data set used to filter the data, and Z represents the filtering conditions.

[0045] Business personnel can send the data fields they want to obtain to the program backend by selecting fields in the report on the human-computer interaction interface, thus obtaining X above; they can obtain Y above based on the information in the chained directed graph; they can edit the filter conditions on the human-computer interaction interface and send them to the program backend, thus obtaining Z above.

[0046] The filtering elements refer to the content between two adjacent SQL keywords in an SQL statement. SQL keywords include select, from, where, group, etc.

[0047] In some embodiments, after the SQL filter elements are sent to the backend, they can be used as attributes of the corresponding report object. In the backend program, an object is created for each report, with the fields in the report serving as the first type of attribute, and the SQL filter elements sent to the backend serving as the second type of attribute of the report object. Objects, attributes, etc., are common concepts in object-oriented programming and will not be discussed in detail here.

[0048] In some embodiments, after the SQL filter elements are sent to the backend, they can also be used as global variables in the code segment corresponding to the SQL statement generation method, that is, they are not used as attributes of the corresponding report.

[0049] S30: Based on the report order in the chained directed graph, sequentially query whether there are filtering elements in each report that meet the filtering conditions.

[0050] S40: Based on the query results and the attributes of the connecting lines in the chained directed graph, the target SQL statement is formed by concatenating them.

[0051] Step S30 involves querying filter elements according to the report order in the chained directed graph. This prevents the possibility of missing filter elements and causing bugs when querying filter elements according to other orders (such as by filter conditions).

[0052] The above reports are generated using SQL statements. The SQL statements are formed by piecing together the report order and connection attributes in the chained directed graph. Since the chained directed graph is edited by business personnel, the SQL statement generation method can be adjusted conveniently and flexibly when the database tables and their connections change. Querying and filtering elements based on the report order in the chained directed graph, rather than based on filtering conditions, avoids missing filtering element information.

[0053] In some embodiments, such as Figure 4 As shown, S40 includes the following steps:

[0054] S41: Query whether there are filter elements in the current report node of the chained directed graph, and record the filter elements corresponding to the current report node if they exist.

[0055] S42: Determine if there is a connection after the current report node in the chained directed graph. If yes, execute S43, then jump to S41 to continue execution; if no, execute S44 and S45.

[0056] S43: Locate the next report node based on the connection line, and use the next report node as the current report node.

[0057] S44: Determine the tail node of the linked directed graph.

[0058] S45: Concatenate the filter elements corresponding to each report node into the SQL statement to form the target SQL statement.

[0059] This embodiment demonstrates a method based on a chained directed graph to sequentially query each report for the existence of filtering elements that meet the filtering conditions. This method employs a depth-first traversal algorithm, resulting in highly efficient and accurate SQL generation.

[0060] In some embodiments, the SQL filter elements include fields selected by business personnel that need to be queried and displayed. Accordingly, such as... Figure 5 As shown, S40 includes the following steps:

[0061] S411: Query the fields in the report corresponding to the report identifier in the current report node of the chained directed graph, and filter out the fields selected by the business personnel.

[0062] S412: Determine if there is a connection after the current report node in the chained directed graph. If yes, execute S413, then jump to S411 to continue execution; if no, execute S414 and S415.

[0063] S413: Locate the next report node based on the connection line, and set the next report node as the current report node.

[0064] S414: Determine the tail node of the linked directed graph.

[0065] S415: Concatenate the selected fields corresponding to each report node to form a select statement segment.

[0066] In some embodiments, the SQL filter elements include the name of the table containing the fields that the business personnel select as the fields they wish to query. Accordingly, such as... Figure 6 As shown, S40 includes the following steps:

[0067] S421: Determine if there is a connection after the current report node in the chained directed graph. If there is, execute S422, then jump to S421 to continue execution; if not, execute S423 and S424.

[0068] S422: Mark the connection relationship between two reports associated by the connection attribute, locate the next report node based on the connection, and use the next report node as the current report node.

[0069] S423: Determine the tail node of the linked directed graph.

[0070] S424: Concatenate the connection relationship statements corresponding to each report node to form a from statement segment.

[0071] In some embodiments, the SQL filtering elements include filtering conditions, which are transmitted to the backend and used as target attributes in the corresponding report object. Accordingly, such as... Figure 7 As shown, S40 includes the following steps:

[0072] S431: Retrieve the target attribute in the report object corresponding to the report identifier in the current report node of the chained directed graph.

[0073] S432: Determine if there is a connection after the current report node in the chained directed graph. If yes, execute S433, then jump to S431 to continue execution; if no, execute S434 and S435.

[0074] S433: Locate the next report node based on the connection line, and use the next report node as the current report node.

[0075] S434: Determine the tail node of the linked directed graph.

[0076] S435: Concatenate the obtained target attributes to form a WHERE clause.

[0077] The following is based on Figure 2 The report shown is used as an example to illustrate the method of generating reports using SQL statements provided in this manual.

[0078] Business personnel pre-edit the pointing relationships among three tables as: A -> B -> C. When business personnel need to search for data from the database, 1. The fields in the report are displayed through the display interface, and the business personnel select the target fields to be searched from the display interface. For example, A-a1, B-b2, C-c3, and these fields are sent to the background program as an "array"; 2. The business personnel input filtering conditions in the dialog box displayed on the display interface. For example, A-a2 > X, C-c2 < Y, and the background will convert these filtering conditions into a filter attribute in a "class" / object corresponding to the report.

[0079] After the background program receives the above two aspects of information input by the business personnel, it splices to obtain the SQL statement: select A.a1,B.b2,C.c3 from A left join B on A.a1 = B.b2 left join on B.b2 = C.3where A.a2 > X and C.C2 < Y. The splicing method is as follows:

[0080] 1. Call the "first function", which performs a loop recursive operation to splice each field in the array passed from the front end into "A.a1,B.b2,C.c3" after select. The loop recursive method is: First, view the first report A in the chain directed graph, and take the field "A.a1" in report A; then judge whether there is a connection after report A (that is, whether there is a next report node for report A). If there is a connection pointing to report B (that is, report A has a next report node B), then take the field "B.b2" in report B; then judge whether there is a connection after report B (that is, whether there is a next report node for report B). If there is a connection pointing to table C (that is, report B has a next report node C), then take the field "C.c3" in report C; then judge whether there is a connection after report C (that is, whether there is a next report node for report C). If there is no connection, end the operation of taking fields, and splice the taken fields into a select statement segment to obtain: select A.a1,B.b2,C.c3.

[0081] 2. Call the "Second Function", which performs a loop recursive operation to obtain "A left join B on A.a1 = B.b2 left join on B.b2 = C.3" after "from". The loop recursive method is as follows: First, view the first report A in the chain directed graph, and determine whether there is a connection after report A (that is, whether there is a next report node for report A). If there is (denoted as the first connection), then extract the associated field of the first connection and mark "A left join B on A.a1 = B.b2"; then, locate the next report B according to the first connection, and determine whether there is a connection after report B (that is, whether there is a next report node for report B). If there is (denoted as the second connection), then extract the associated field of the second connection and mark "left join on B.b2 = C.3"; then, locate report C according to the second connection, and determine that there is no connection after report C (that is, there is no next report node for report C), then end the operation of extracting fields, and splice the marked content into a "from" statement segment to obtain: "A left join B on A.a1 = B.b2 left join on B.b2 = C.3".

[0082] 3. Call the "Third Function", which performs a loop recursive operation to obtain "A.a2 > X and C.C2 < Y" after "where". The loop recursive method is as follows: First, view the first report A in the chain directed graph, and determine whether there is a filter attribute value in report A. If there is "A.a2 > X", then record this attribute value; then, determine whether there is a connection after report A (that is, whether there is a next report node for report A). If there is a connection pointing to report B (that is, there is a next report node B for report A), then determine whether there is a filter attribute value in report B. If not; then, determine whether there is a connection after report B (that is, whether there is a next report node for report B). If there is a connection pointing to table C (that is, there is a next report node C for report B), then determine whether there is a filter attribute value in report C. If there is "C.C2 < Y", then record this attribute value; then, determine whether there is a connection after report C (that is, whether there is a next report node for report C). If there is no connection, then end the operation of recording filter attribute values, and splice the recorded attribute values into a "where" statement segment to obtain: "where A.a2 > X and C.C2 < Y".

[0083] This specification provides a device for generating a SQL statement for a report, which can be used to implement Figure 1 the method for generating a SQL statement for a report as shown. As Figure 8 shown, the device includes a first acquisition unit 10, a second acquisition unit 20, a query unit 30, and a splicing unit 40.

[0084] The first acquisition unit 10 is used to acquire a chain-directed graph of multiple reports edited by business personnel; the chain-directed graph includes multiple report nodes and connections. The report nodes include the following attributes: report identifier, and the connections include the following attributes: report identifier in the report node at the beginning of the connection, report identifier in the report node at the end of the connection, connection method of the reports at both ends of the connection, and connection field of the reports at both ends of the connection.

[0085] The second acquisition unit 20 is used to acquire the filtering conditions selected by business personnel through the human-computer interaction interface and transmitted to the backend.

[0086] The query unit 30 is used to query, based on the report order in the chained directed graph, whether there are filtering elements in each report that meet the filtering conditions.

[0087] The splicing unit 40 is used to splice together the query results and the attributes of the connecting lines in the chained directed graph to form a target SQL statement.

[0088] In some embodiments, the splicing unit includes: a first query subunit, a judgment subunit, a first determination subunit, and a second determination subunit, used to perform operations cyclically until the tail node of the chained directed graph is reached, and then splice the filter elements corresponding to each report node into the SQL statement to form the target SQL statement; wherein, the first query subunit is used to query whether there are filter elements in the current report node in the chained directed graph, and if so, record the filter elements corresponding to the current report node; the judgment subunit is used to determine whether there is a connection after the current report node in the chained directed graph; the first determination subunit is used to locate the next report node according to the connection if yes, and take the next report node as the current report node; the second determination subunit is used to determine the tail node of the chained directed graph if no.

[0089] In some embodiments, the SQL filtering elements include fields selected by business personnel that need to be queried and displayed; correspondingly, the concatenation unit includes: a second query subunit, a judgment subunit, a first determination subunit, and a second determination subunit, used to perform operations cyclically until the tail node of the chained directed graph is reached, and then concatenate the filtered fields corresponding to each report node to form a select statement segment; wherein, the second query subunit is used to query the fields in the report corresponding to the report identifier in the current report node in the chained directed graph, and filter out the fields selected by the business personnel; the judgment subunit is used to determine whether there is a connection after the current report node in the chained directed graph; the first determination subunit is used to locate the next report node according to the connection if yes, and take the next report node as the current report node; the second determination subunit is used to determine the tail node of the chained directed graph if no.

[0090] In some embodiments, the SQL filtering elements include the names of the tables containing the fields that the business personnel select to be queried; correspondingly, the concatenation unit includes: a judgment subunit, a marking subunit, and a second determination subunit, used to perform operations cyclically until the tail node of the chained directed graph is reached, and then concatenate the connection relationship statements corresponding to each report node to form a from statement segment; wherein, the judgment subunit is used to determine whether there is a connection after the current report node in the chained directed graph; the marking subunit is used to mark the connection relationship between the two reports associated with the connection according to the connection attribute if there is, and locate the next report node according to the connection, and take the next report node as the current report node; the second determination subunit is used to determine the tail node of the chained directed graph if there is no connection.

[0091] In some embodiments, the SQL filtering elements include filtering conditions, which are transmitted to the backend and used as target attributes in the corresponding report object. Correspondingly, the concatenation unit includes: an acquisition subunit, a judgment subunit, a first determination subunit, and a second determination subunit, used to perform operations cyclically until the tail node of the chained directed graph is reached, and then concatenating the acquired target attributes to form a WHERE clause segment. The acquisition subunit is used to acquire the target attribute in the report object corresponding to the report identifier in the current report node of the chained directed graph. The judgment subunit is used to determine whether there is a connection after the current report node in the chained directed graph. The first determination subunit, if yes, locates the next report node based on the connection and uses the next report node as the current report node. The second determination subunit, if no, determines that the tail node of the chained directed graph has been reached.

[0092] In some embodiments, the chain-like directed graph is determined by business personnel by dragging graphical report components from the first area of ​​the graphical editing interface to the second area, based on the positional relationship between the graphical report components in the second area.

[0093] The description and benefits of the SQL statement generation device for the above reports can be found in the description and benefits section of the method section, and will not be repeated here.

[0094] This invention also provides an electronic device, such as... Figure 9 As shown, the electronic device may include a processor 901 and a memory 902, wherein the processor 901 and the memory 902 may be connected via a bus or other means. Figure 9 Taking the example of a connection between China and Israel via a bus.

[0095] Processor 901 can be a Central Processing Unit (CPU). Processor 901 can also be other general-purpose processors, digital signal processors (DSPs), application-specific integrated circuits (ASICs), field-programmable gate arrays (FPGAs), or other programmable logic devices, discrete gate or transistor logic devices, discrete hardware components, or combinations of the above types of chips.

[0096] Memory 902, as a non-transitory computer-readable storage medium, can be used to store non-transitory software programs, non-transitory computer-executable programs, and modules, such as the program instructions / modules corresponding to the report generation method using SQL statements in this embodiment of the invention (e.g., Figure 8 The first acquisition unit 10, the second acquisition unit 20, the query unit 30, and the splicing unit 40 are shown. The processor 901 executes various functional applications and data classification by running non-transitory software programs, instructions, and modules stored in the memory 902, thereby implementing the report generation method using SQL statements in the above method embodiments.

[0097] The memory 902 may include a program storage area and a data storage area. The program storage area may store the operating system and applications required for at least one function; the data storage area may store data created by the processor 901, etc. Furthermore, the memory 902 may include high-speed random access memory and may also include non-transitory memory, such as at least one disk storage device, flash memory device, or other non-transitory solid-state storage device. In some embodiments, the memory 902 may optionally include memory remotely located relative to the processor 901, and these remote memories may be connected to the processor 901 via a network. Examples of such networks include, but are not limited to, the Internet, corporate intranets, local area networks, mobile communication networks, and combinations thereof.

[0098] The one or more modules are stored in the memory 902, and when executed by the processor 901, they execute the above-mentioned method for generating reports using SQL statements.

[0099] For details regarding the electronic device described above, please refer to the relevant descriptions and effects in the above embodiments for further understanding; they will not be repeated here.

[0100] This specification provides a computer storage medium storing computer program instructions that, when executed by a processor, implement the steps of any of the above-described methods for generating reports using SQL statements.

[0101] This specification provides a computer program product comprising a computer program that, when executed by a processor, implements the steps of any of the above-described methods for generating reports using SQL statements.

[0102] Those skilled in the art will understand that all or part of the processes in the methods of the above embodiments can be implemented by a computer program instructing related hardware. The program can be stored in a computer-readable storage medium, and when executed, it can include the processes of the embodiments of the above methods. The storage medium can be a magnetic disk, optical disk, read-only memory (ROM), random access memory (RAM), flash memory, hard disk drive (HDD), or solid-state drive (SSD), etc.; the storage medium can also include combinations of the above types of memory.

[0103] The various embodiments in this specification are described in a progressive manner. For the same or similar parts between the various embodiments, please refer to each other. The focus of each embodiment is to describe the differences from other embodiments.

[0104] The systems, devices, modules, or units described in the above embodiments can be implemented by computer chips or entities, or by products with certain functions.

[0105] For ease of description, the above devices are described separately by function as various units. Of course, in implementing this application, the functions of each unit can be implemented in one or more software and / or hardware.

[0106] As can be seen from the above description of the embodiments, those skilled in the art can clearly understand that this application can be implemented by means of software plus necessary general-purpose hardware platforms. Based on this understanding, the technical solution of this application, in essence, or the part that contributes to the prior art, can be embodied in the form of a software product. This computer software product can be stored in a storage medium, such as ROM / RAM, magnetic disk, optical disk, etc., and includes several instructions to cause a computer device (which may be a personal computer, server, or network device, etc.) to execute certain parts of the methods of various embodiments of this application.

[0107] This application can be used in a wide variety of general-purpose or special-purpose computer system environments or configurations. For example: personal computers, server computers, handheld or portable devices, tablet devices, multiprocessor systems, microprocessor-based systems, set-top boxes, programmable consumer electronics devices, network PCs, minicomputers, mainframe computers, distributed computing environments including any of the above systems or devices, etc.

[0108] This application can be described in the general context of computer-executable instructions, such as program modules, that are executed by a computer. Generally, program modules include routines, programs, objects, components, data structures, etc., that perform a specific task or implement a specific abstract data type. This application can also be practiced in distributed computing environments where tasks are performed by remote processing devices connected via a communication network. In distributed computing environments, program modules can reside in local and remote computer storage media, including storage devices.

[0109] Although this application has been described through embodiments, those skilled in the art will know that this application has many modifications and variations without departing from the spirit of this application, and it is intended that the appended claims cover such modifications and variations without departing from the spirit of this application.

Claims

1. A method for generating reports using SQL statements, characterized in that, include: Obtain a chained directed graph of multiple reports edited by business personnel; the chained directed graph includes multiple report nodes and connections. The report nodes include the following attributes: report identifier, and the connections include the following attributes: report identifier in the report node at the beginning of the connection, report identifier in the report node at the end of the connection, connection method of the reports at both ends of the connection, and connection field of the reports at both ends of the connection. The system obtains the filter criteria selected and transmitted to the backend by business personnel through a human-computer interaction interface; the filter criteria are included in the SQL filter elements. Based on the report order in the chained directed graph, query each report sequentially to see if there are any filtering elements that meet the filtering conditions. Based on the query results and the attributes of the connections in the chained directed graph, the target SQL statement is formed by concatenating them. Based on the query results and the attributes of the connections in the chained directed graph, a target SQL statement is constructed by concatenating the following steps: The steps are executed iteratively until the tail node of the chained directed graph is reached, then the filter elements corresponding to each report node are concatenated into the SQL statement to form the target SQL statement. Query whether there are filter elements in the current report node of the chained directed graph, and if so, record the filter elements corresponding to the current report node; Determine if there is a connection after the current report node in the chained directed graph; If yes, locate the next report node based on the connection and use the next report node as the current report node; if no, determine the tail node of the chained directed graph.

2. The method according to claim 1, characterized in that, The SQL filtering elements include the fields that business personnel select to be queried and displayed; Accordingly, based on the query results and the attributes of the connecting lines in the chained directed graph, the target SQL statement is formed by concatenating the following steps: after repeatedly executing the following steps until the tail node of the chained directed graph is reached, the selected fields corresponding to each report node are concatenated to form a select statement segment. Query the fields in the report corresponding to the report identifier in the current report node of the chained directed graph, and filter out the fields selected by the business personnel; Determine if there is a connection after the current report node in the chained directed graph; If yes, locate the next report node based on the connection and use the next report node as the current report node; if no, determine the tail node of the chained directed graph.

3. The method according to claim 1, characterized in that, The SQL filtering elements include the names of the tables containing the fields that the business personnel select to query; Accordingly, based on the query results and the attributes of the connections in the chained directed graph, the target SQL statement is formed by concatenating the following steps: after repeatedly executing the following steps until the tail node of the chained directed graph is reached, the connection relationship statements corresponding to each report node are concatenated to form a from statement segment. Determine if there is a connection after the current report node in a chained directed graph; In some cases, the connection relationship between two reports associated with a connection is marked based on the connection attribute, and the next report node is located based on the connection, and the next report node is used as the current report node: In the absence of a tail node, determine the destination node of the directed graph.

4. The method according to claim 1, characterized in that, The SQL filtering elements include filtering conditions, which are transmitted to the backend and used as target attributes in the corresponding report object. Accordingly, based on the query results and the attributes of the connecting lines in the chained directed graph, the target SQL statement is formed by concatenating the results, including: repeatedly executing the following steps until the tail node of the chained directed graph is reached, and then concatenating the obtained target attributes to form a WHERE clause segment. Retrieve the target attribute from the report object corresponding to the report identifier in the current report node of the chained directed graph; Determine if there is a connection after the current report node in the chained directed graph; If yes, locate the next report node based on the connection and use the next report node as the current report node; if no, determine the tail node of the chained directed graph.

5. The method according to claim 1, characterized in that, The chain-like directed graph is determined by business personnel when they drag graphical report components from the first area of ​​the graphical editing interface to the second area, based on the positional relationships between the graphical report components in the second area.

6. A device for generating reports using SQL statements, characterized in that, include: The first acquisition unit is used to acquire a chain-directed graph of multiple reports edited by business personnel; the chain-directed graph includes multiple report nodes and connections. The report nodes include the following attributes: report identifier, and the connections include the following attributes: report identifier in the report node at the beginning of the connection, report identifier in the report node at the end of the connection, connection method of the reports at both ends of the connection, and connection field of the reports at both ends of the connection. The second acquisition unit is used to acquire the filtering conditions selected and transmitted to the backend by business personnel through the human-computer interaction interface; the filtering conditions are included in the SQL filtering elements; The query unit is used to query, based on the report order in the chained directed graph, whether there are any filtering elements in each report that meet the filtering conditions. The splicing unit is used to splice the query results and the attributes of the connecting lines in the chained directed graph to form the target SQL statement; The concatenation unit includes: a first query subunit, a judgment subunit, a first determination subunit, and a second determination subunit, used to cyclically execute operations until the tail node of the chained directed graph is reached, then concatenating the filter elements corresponding to each report node into the SQL statement to form the target SQL statement; wherein, The first query subunit is used to query whether there are filter elements in the current report node of the chained directed graph, and if so, to record the filter elements corresponding to the current report node; The judgment subunit is used to determine whether there is a connection after the current report node in the chained directed graph; The first determining subunit is used to locate the next report node according to the connection line when the condition is met, and to take the next report node as the current report node. The second determining subunit is used to determine the tail node of the chained directed graph if no.

7. The apparatus according to claim 6, characterized in that, The SQL filtering elements include the fields that business personnel select to be queried and displayed; Accordingly, the splicing unit includes: a second query subunit, a judgment subunit, a first determination subunit, and a second determination subunit, used to perform the operation cyclically until the tail node of the chained directed graph is reached, and then splice the filtered fields corresponding to each report node to form a select statement segment; wherein, The second query subunit is used to query the fields in the report corresponding to the report identifier in the current report node of the chained directed graph, and filter out the fields selected by the business personnel. The judgment subunit is used to determine whether there is a connection after the current report node in the chained directed graph; The first determining subunit is used to locate the next report node according to the connection line when the condition is met, and to take the next report node as the current report node. The second determining subunit is used to determine the tail node of the chained directed graph if no.

8. The apparatus according to claim 6, characterized in that, The SQL filtering elements include the names of the tables containing the fields that the business personnel select to query; Accordingly, the splicing unit includes: a judgment subunit, a marking subunit, and a second determination subunit, used to perform the operation cyclically until the tail node of the chained directed graph is reached, and then splice the connection relationship statements corresponding to each report node to form a from statement segment; wherein, The judgment subunit is used to determine whether there is a connection after the current report node in the chained directed graph; The marker subunit is used, when available, to mark the connection relationship between two reports associated with a connection based on the connection attribute, and to locate the next report node based on the connection, using the next report node as the current report node: The second determining subunit is used to determine the tail node of the chained directed graph if none is found.

9. The apparatus according to claim 6, characterized in that, The SQL filtering elements include filtering conditions, which are transmitted to the backend and used as target attributes in the corresponding report object. Accordingly, the splicing unit includes: an acquisition sub-unit, a judgment sub-unit, a first determination sub-unit, and a second determination sub-unit, used to perform the operation cyclically until the tail node of the chained directed graph is reached, and then the acquired target attributes are spliced ​​to form a WHERE clause segment; wherein, The acquisition subunit is used to acquire the target attribute in the report object corresponding to the report identifier in the current report node of the chained directed graph; The judgment subunit is used to determine whether there is a connection after the current report node in the chained directed graph; The first determining subunit is used to locate the next report node according to the connection line when the condition is met, and to take the next report node as the current report node. The second determining subunit is used to determine the tail node of the chained directed graph if no.

10. The apparatus according to claim 6, characterized in that, The chain-like directed graph is determined by business personnel when they drag graphical report components from the first area of ​​the graphical editing interface to the second area, based on the positional relationships between the graphical report components in the second area.

11. An electronic device, characterized in that, include: A memory and a processor, the processor and the memory being communicatively connected to each other, the memory storing computer instructions, the processor executing the computer instructions to implement the steps of the method according to any one of claims 1 to 5.

12. A computer storage medium, characterized in that, The computer storage medium stores computer program instructions, which, when executed by a processor, implement the steps of the method according to any one of claims 1 to 5.

13. A computer program product, characterized in that, It includes a computer program that, when executed by a processor, implements the steps of the method according to any one of claims 1 to 5.

Images