A certain database has a ticket table and a work hour table. The ticket table stores the relationship between each ticket and its parent ticket, forming a self-association structure:

The work hour table stores the direct working hours corresponding to each ticket:

Now we need to recursively calculate the total working hours, which is the sum of the direct working hours of each ticket and the working hours of all subordinate sub tickets.

SQL does not support references, makes it inconvenient to express self-associated relationships, lacks recursive functions, and makes code difficult to write. SPL provides reference functions and can establish self-associations; Provides recursive functions that can take all subordinate nodes: https://try.esproc.com/splx?4l2

A1： Join two tables and load data.

A2： Use the switch function to modify the field values of the parent ticket to the record references of the parent ticket, and establish a self-associated relationship. Record references can intuitively express parent-child relationships. The following figure shows the parent tickets of all levels of ticket 3, [8,20,26,30].

A3=A1.new(A1.nodes(parentID,~)) Create a new two-dimensional table and first calculate all the subordinate records of the current record. The function nodes can recursively calculate all the subordinate records of a certain record, where ~ represents the current record. The following figure shows all subordinate records of ticket No. 26, [3,8,14,20]:

A3=A1.new(ticketid,A1.nodes(parentID,~).sum(hours)+hours:Total_hours) Then calculate the total working hours of the current ticket, which is the sum of the working hours of the subordinate sub tickets plus the direct working hours.