如何将行限制为列的SUM等于Oracle中的某些值 [英] How to limit rows to where SUM of a column equals to certain value in Oracle

问题描述

根据我引用的链接，我试图做同样的事情，但是出错了

Based on the link I refer, I have tried to do the same but got an error

将行限制为在MySQL中某一列的总和等于某个值的地方

首先，这是学习计划表:

如果我要获得的行直到CREDIT列的SUM等于18:

If I want to get the rows until the SUM of CREDIT column equal to 18 :

如果要在CREDIT列的SUM等于20之前获取行:

If I want to get the rows until the SUM of CREDIT column equals to 20 :

这是我尝试的SQL语句:

This is the SQL statement I try:

SELECT t.matricsno,t.sem,t.credit,
(SELECT SUM(credit) FROM studyplan
WHERE matricsno = 'D031310087')
FROM studyplan t
HAVING SUM(credit) = 18
ORDER BY t.sem,t.subjectcode;

我得到的错误是: ORA-00937:不是单组分组功能

感谢您的回复和帮助.

推荐答案

这是使用累积总和的好用例.

This is a good use case for using a cumulative sum.

假设行的顺序由列sem和subjectcode明确定义，正如您的查询所暗示的，您可以这样编写查询:

Assuming the order of the rows is unambiguously defined by the columns sem and subjectcode, as your query implies, you can write the query like this:

select *
  from (select t.*,
               sum(t.credit) over (order by t.sem, t.subjectcode) as credit_sum
          from studyplan t
         where t.matricsno = 'D031310087')
 where credit_sum <= 20 -- adjust to desired number of credits
 order by sem, subjectcode

这篇关于如何将行限制为列的SUM等于Oracle中的某些值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！