sql - sql通过将树挂在叶子上来构造树

Question

我有 2 个表（仅给出相关列）作为

表 j

id int
account int
amount numeric;

表e

id int
account int
amount numeric
code_id foreign key to j.id

数据样本

表 j

  id   account_id   amount     
 ---- ------------ -------- -- 
   1            5      500     
   2            6     -700     
   3                           
   4            6      200     
   5                           

表e

  id   account_id   amount   code_id  
 ---- ------------ -------- --------- 
   1           10     -200         1  
   2            6     -100         1  
   3            8      150         1  
   4            7     -350         1  
   5           12      200         2  
   6           13      500         2  
   5            6      200         3  
   6            4     -100         3  
   7            6     -125         3  
   8            5       25         3  
   9            6     -200         4  
  10            6      300         5  
  11           12     -300         5  

我想要以下表 j 或表 e 中的每个 account_id。我给出了帐户6的结果

编辑：需要取 6 并将其倒挂列出所有针对它的交易，如下所示。

  ledger_head_id   account_id   amount   bal   t_id  
 ---------------- ------------ -------- ----- ------ 
               6           10     -200            1  
               6            8      150            1  
               6            7     -350            1  
               6            5      500   100      1  
               6           12      200            2  
               6           13      500   700      2  
               6            4     -100            3  
               6            5       25    75      3  
               6            6     -200   200      4  
               6           12     -300   300      5  

我所做的是

WITH tb1 AS (
    SELECT *
    FROM (
        SELECT code_id AS j_id, account_id, amount, 'E' AS entry_type, e.id AS entry_id
        FROM t, e
        WHERE code_id = t.id
        UNION ALL
        SELECT id, account_id, amount, NULL, NULL
        FROM t
    ) foo
    WHERE account_id NOTNULL
)
    SELECT
        DISTINCT ON (l2.account_id, j_id)
        j_id,
        l2.account_id AS ledger_head_id,
        jsonb_agg(
            jsonb_build_object(
                'account_id', l1.account_id,
                'amt', l1.amount,
            )
        ) OVER ( PARTITION BY l2.account_id, l1.j_id ORDER BY l2.account_id, l1.j_id ) AS details,
        l2.amount
    FROM tb1 l1
    LEFT JOIN tb1 l2 USING (j_id)
    WHERE
          ((l1.entry_type = 'E' AND l1.entry_id IS DISTINCT FROM l2.entry_id) OR ((l1.entry_type != 'E' OR l1.entry_type ISNULL ) AND l1.account_id != l2.account_id))
    ORDER BY ledger_head_id, j_id

我觉得，这不是最佳的做法。仅供参考，我没有必要加入整个表格......我可以为所需的帐户做它（ledger_head_id），如果这样可以更有效，那么我必须使用Postgres 中的函数。

基本上是在寻找更好的方法来做到这一点。

使用：Postgres 12

谢谢你

标签： sqlpostgresql