时间戳

首页 > 解决方案 > 如何对父 ID 值求和并代替 null 显示

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

问题描述

嗨，我有查询返回数据

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   |
+------------+----------+-------+------------------+---------+

现在分数为空，我想显示相应父母 ID 的分数总和。如果standardID为107并且得分为null，则得分应该是父母ID的总和为107。不使用分组，如果不为null，则得分保持不变。

任何帮助将不胜感激谢谢！

标签： mysql

解决方案

选择

S.StandardID，（当 A.Score 为 NULL THEN C.Score ELSE A.Score END 时的情况）作为 New_Score

从表 A

INNER JOIN Standard S ON S.StandardID = TableA.StandardID LEFT OUTER JOIN (SELECT S1.ParentStandardID, SUM(B.ScoreValue) Score FROM Table B, Standard S1 WHERE S1.StandardID = B.StandardID GROUP BY ParentStandardID) C ON S. StandardID = C.ParentStandardID

推荐阅读