我有一个表，一个表，其中的记录可以是另一个记录的子记录或父记录。
我试图为表中的每条记录获取一个附加记录（用户）中包含的列（点）的总和。
我尝试了一些方法，比如在Postgres中使用self-join或者甚至递归CTE，但是没有成功。
在下表中，记录3是记录1的子级。
一号桌
+------------------+
|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 |
+---------------+
最佳答案
首先获取父总计：
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介于两者之间，这样你就不会失去无子女的父母，因此你需要NULL处理POINTS（即COALESCE）关于sql - 自加入和，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/48211169/