Based on my question here, I have managed to get the answer.. but then a new problem arise that I need to display it like this :
基于我的问题,我已经设法得到了答案..但随后出现了一个新问题,我需要像这样显示它:
Firstly, here is the studyplan table
首先,这是研究计划表
So now, on the first run, it will display like this if I want to get the rows until SUM of Credit column equal to 18 :
所以现在,在第一次运行时,如果我想获取行,直到Credit of Credit列等于18,它将显示如下:
But, then on the second run, I want it to be displayed like this if I want to get the rows until the SUM of Credit column equal to 21 :
但是,然后在第二次运行时,我希望它显示如下,如果我想获取行,直到Credit of SUM列等于21:
How I want it to SUM the column at the next row? Do I have to make 2 SQL statement?
我希望它在下一行汇总列?我必须制作2个SQL语句吗?
Here is the success code from the first run :
以下是第一次运行的成功代码:
SELECT * FROM
(SELECT t.*,
SUM(t.credit) OVER (PARTITION BY t.matricsno ORDER BY t.sem, t.subjectcode)
AS credit_sum
FROM studyplan t)
WHERE credit_sum <= 17 AND matricsno = 'D031310087';
Thank you for your response and time.
感谢您的回复和时间。
Here is the link, How to limit rows to where SUM of a column equals to certain value in Oracle
以下是链接,如何将行限制为列的SUM等于Oracle中的某个值
1 个解决方案
#1
1
It's a bit of an odd requirement. But, yes, if you want 2 different result sets, you'll need separate 2 SQL statements. But for each additional statement, you would only need to tweak your condition on credit_sum.
这有点奇怪的要求。但是,是的,如果你想要2个不同的结果集,你需要单独的2个SQL语句。但是对于每个附加声明,您只需要在credit_sum上调整您的条件。
For instance, if for your 1st query you want to get the rows up to when the credit sum reaches 18, you would do:
例如,如果您的第一个查询想要获得信用额达到18的行,那么您可以:
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 <= 18
order by sem, subjectcode
For your 2nd query, you say you want the rows where the credit sum reaches 21, but ignoring the rows returned by the 1st query. Another way to express that requirement is to return the rows for which the cumulative credit sum is between 19 and 39 (inclusive). So then, it simply becomes a matter of modifying the filter on credit_sum to use a between condition:
对于第二个查询,您说您希望信用额达到21的行,但忽略第一个查询返回的行。表达该要求的另一种方式是返回累积信用额在19和39之间(包括19和39)的行。那么,只需修改credit_sum上的过滤器就可以使用条件:
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 between 19 and 39
order by sem, subjectcode
#1
1
It's a bit of an odd requirement. But, yes, if you want 2 different result sets, you'll need separate 2 SQL statements. But for each additional statement, you would only need to tweak your condition on credit_sum.
这有点奇怪的要求。但是,是的,如果你想要2个不同的结果集,你需要单独的2个SQL语句。但是对于每个附加声明,您只需要在credit_sum上调整您的条件。
For instance, if for your 1st query you want to get the rows up to when the credit sum reaches 18, you would do:
例如,如果您的第一个查询想要获得信用额达到18的行,那么您可以:
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 <= 18
order by sem, subjectcode
For your 2nd query, you say you want the rows where the credit sum reaches 21, but ignoring the rows returned by the 1st query. Another way to express that requirement is to return the rows for which the cumulative credit sum is between 19 and 39 (inclusive). So then, it simply becomes a matter of modifying the filter on credit_sum to use a between condition:
对于第二个查询,您说您希望信用额达到21的行,但忽略第一个查询返回的行。表达该要求的另一种方式是返回累积信用额在19和39之间(包括19和39)的行。那么,只需修改credit_sum上的过滤器就可以使用条件:
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 between 19 and 39
order by sem, subjectcode