我正在从事一个我们应该评估供应商的项目,在这个数据库中,我有这个表格EvaluationGrade
:
+------+---------------------+------------+-----------+
| Id | EvaluationMethodId | FromScore | ToScore |
+------+---------------------+------------+-----------+
| 1 | 2 | 1 | 20 |
| 2 | 2 | 21 | 50 |
| 3 | 2 | 51 | 70 |
| 4 | 2 | 71 | 100 |
| 5 | 3 | 1 | 20 |
| 6 | 3 | 31 | 40 |
+------+---------------------+------------+-----------+
此表对分数进行分类,我将确保EvaluationMethodId=2
范围值填充 1 到 100(就像上面的示例一样(。
我正在寻找这样的东西:
+---------------------+------------+
| EvaluationMethodId | Sum |
+---------------------+------------+
| 2 | 100 |
| 3 | 30 |
+---------------------+------------+
这是我尝试的方式:
WITH myUpdate
AS (SELECT emg.Id,emg.EvaluationMethodId,
SUM(emg.ToGrade - emg.FromGrade) + 1 AS SumScope
FROM generalsup.EvaluationMethodGrading emg
GROUP BY emg.Id,emg.EvaluationMethodId)
SELECT myUpdate.EvaluationMethodId, SUM(myUpdate.SumScope) AS SumScopeAll
FROM myUpdate
GROUP BY myUpdate.EvaluationMethodId;
但是我使用窗口功能,可以减少服务器上的开销。
由于分数中没有重叠的情况,因此您可以使用group by EvaluationMethodId
和sum()
来做到这一点:
select EvaluationMethodId, sum(ToScore - FromScore + 1) [Sum]
from EvaluationMethodGrading
group by EvaluationMethodId
请参阅演示。
结果:
> EvaluationMethodId | Sum
> -----------------: | --:
> 2 | 100
> 3 | 30