某数据库有票据表和工时表，票据表存储了每张票据和父票据的关系，形成了自关联结构：

工时表存储了每张票据对应的多个工时：

现在要计算出每张票据的工时，并递归地计算出该票据和所有下级子票据的工时之和，即总工时。

SQL：

WITH    CTE_TREE AS (
        SELECT  parentid AS parentid, ticketid AS children
        FROM    tickets t
        WHERE   parentID <> 0
        UNION
        SELECT  parentid, NULL
        FROM    tickets
        WHERE   parentID <> 0
        UNION
        SELECT  ticketid, NULL
        FROM    tickets
    )
    ,   CTE_TRAVERSE AS (
        SELECT  parentid AS mainId, children AS nextParent
        FROM    CTE_TREE
        UNION ALL
        SELECT  t.mainId, tree.children
        FROM    CTE_TREE tree
        INNER JOIN CTE_TRAVERSE t
            ON  t.nextParent = tree.parentid
        WHERE   tree.children <> ''
    )
    SELECT  t.MainID
    ,   SUM(CASE WHEN t.nextparent IS NULL THEN h.Hours END) AS Direct_hours
    ,   SUM(h.Hours) AS Total_hours
    FROM    CTE_TRAVERSE t
    INNER JOIN Hours h
        ON  h.ticketid = t.nextparent
        OR  (h.ticketid = t.mainID AND t.nextparent IS NULL)
    GROUP BY t.mainId

SQL 要用多个子查询实现自关联和递归关系，代码复杂难懂。SPL 直接提供了引用函数可建立自关联，提供了递归函数可取所有下级节点：https://try.esproc.com/splx?2SJ

	A
1	$select t.ticketid ticketid,t.parentID parentID,h.hours hours from tickets.txt t left join (select ticketid,sum(hours) hours from hours.txt group by ticketid) h on t.ticketid=h.ticketid
2	>A1.switch(parentID,A1:ticketid)
3	=A1.new(ticketid:MainID,hours:Direct_hours,hours+A1.nodes(parentID,~).sum(hours):Total_hours)

A1：加载数据，在票据表的基础上，计算出每张票据的直接工时。

A2：让每条记录的父票据字段指向父票据记录，建立自关联关系。函数 switch 可以将字段值修改成记录。

A3：新建二维表，当前票据的总工时 = 当前票据的直接工时 + 当前票据所有的下级子票据的工时之和。函数 nodes 可以递归地计算出某条记录所有的下级记录。

问题来源：https://stackoverflow.com/questions/78089375/how-to-add-total-hours-for-a-ticket-including-all-child-tickets