sql - 自连接求和

Question

我有一个表 table_one，其中的记录可以是另一条记录的子项或父项。

我试图为 TABLE_ONE 中的每条记录获取附加记录(用户)中包含的列(点)的总和。

我在 Postgres 中尝试了多种方法，例如自连接或什至使用 RECURSIVE CTE，但没有任何成功。

在下表中，记录 3 是记录 1 的子项。

TABLE_ONE

+------------------+
|ID|NAME |PARENT_ID|
+------------------+
|1 |name1|         |
+------------------+
|2 |name2|         |
+------------------+
|3 |name3|1        |
+------------------+

表_二

+-----------------------+
|ID|TABLE_ONE_ID|USER_ID|
+-----------------------+
|1 |1           |1      |
+-----------------------+
|2 |1           |2      |
+-----------------------+
|3 |3           |3      |
+-----------------------+
|4 |2           |4      |
+-----------------------+

表用户

+---------+
|ID|POINTS|
+---------+
|1 |100   |
+---------+
|2 |200   |
+---------+
|3 |100   |
+---------+
|4 |50    |
+---------+

异常结果:

+---------------+
|ID|NAME |POINTS|
+---------------+
|1 |name1|400   |
+---------------+
|2 |name2|50    |
+---------------+

Answer 1

首先获取父总计:

SELECT ID, NAME, SUM(POINTS) as POINTS
FROM TABLE_ONE T1
JOIN TABLE_TWO T2 on T1.ID = T2.TABLE_ONE_ID
JOIN TABLE_USERS U on T2.USER_ID = U.ID
WHERE PARENT_ID IS NULL --assuming those are nulls in table_one, not ''
GROUP BY ID, NAME

然后获取每个父项的子项总数:

SELECT SUM(POINTS) as POINTS, PARENT_ID
FROM TABLE_ONE T1
JOIN TABLE_TWO T2 on T1.ID = T2.TABLE_ONE_ID
JOIN TABLE_USERS U on T2.USER_ID = U.ID
WHERE PARENT_ID IS NOT NULL
GROUP BY PARENT_ID

然后合并查询并将POINTS相加:

SELECT P.ID, P.NAME, PARENT_POINTS + COALESCE(CHILD_POINTS,0) as POINTS
FROM (SELECT T1.ID, NAME, SUM(POINTS) as PARENT_POINTS
      FROM TABLE_ONE T1
      JOIN TABLE_TWO T2 on T1.ID = T2.TABLE_ONE_ID
      JOIN TABLE_USERS U on T2.USER_ID = U.ID
      WHERE PARENT_ID IS NULL --assuming those are nulls in table_one, not ''
      GROUP BY ID, NAME) P
LEFT JOIN (SELECT SUM(POINTS) as CHILD_POINTS, PARENT_ID
           FROM TABLE_ONE T1
           JOIN TABLE_TWO T2 on T1.ID = T2.TABLE_ONE_ID
           JOIN TABLE_USERS U on T2.USER_ID = U.ID
           WHERE PARENT_ID IS NOT NULL
           GROUP BY PARENT_ID) C on P.ID = C.PARENT_ID

一些注意事项:

• 派生表对于确保父子连接是 1 对 1 很重要，否则你会得到重复的
• 您需要在两者之间进行LEFT JOIN，这样您就不会失去没有 child 的 parent ，并且您需要对POINTS 进行NULL 处理，因为其中(即 COALESCE)