mysql - 如何对父 ID 值求和并显示以代替 null

Question

您好，我有一个查询返回数据

SELECT
     s.StandardID,
     s.StandardName as Standard,
     TableA.Score,
     Dropdown1.Name as dq,
     Standard.ParentStandardID,         
     Standard.Weight     
FROM TableA
      JOIN Standard ON Standard.StandardID = TableA.StandardID

结果

+------------+----------+-------+------------------+---------+
| StandardID | Standard | Score | parentstandardid |  weight |
+------------+----------+-------+------------------+---------+
|    1       |     A    |       |      3           |    o    |
+------------+----------+-------+------------------+---------+
|   107      |     B    |       |     NULL         |    1    |
+------------+----------+-------+------------------+---------+
|   111      |     C    |       |      1           |   107   |
+------------+----------+-------+------------------+---------+
|   112      |     D    |       |      2           |   107   |
+------------+----------+-------+------------------+---------+

现在，如果分数为空，我想显示对应 parent ID 的分数总和。如果 standardID 为 107 并且分数为空，则分数应为父 ID 107 的总和。不使用分组依据，如果不为空，则分数保持不变。

如有任何帮助，我们将不胜感激谢谢!

Answer 1

选择

S.StandardID， (CASE WHEN A.Score IS NULL THEN C.Score ELSE A.Score END ) AS New_Score

来自表A

INNER JOIN 标准 S ON S.StandardID = TableA.StandardID左外连接( SELECT S1.ParentStandardID, SUM(B.ScoreValue ) 分数 来自表 B，标准 S1，其中 S1.StandardID = B.StandardID 按父标准 ID 分组) CON S.StandardID = C.ParentStandardID