Question

It s difficult to tell what is being asked here. This question is ambiguous, vague, incomplete, overly broad, or rhetorical and cannot be reasonably answered in its current form. For help clarifying this question so that it can be reopened, visit the help center.

Closed 11 years ago.

我坐在一张空洞中。

我的表薪结构如下:

Name Amount
a     100
b     300
c     250

我必须写一个提问,结果如下:

 Name Amount RelevantAmount
    a     100    100
    b     300    400
    c     250    650

我没有发现任何想法,即如何在不使用任何 cur子和触发器的情况下书写这种询问。

Answer 1

假设你正在将清单中高于一切的数额汇总起来,并包括目前线,那么最简单地理解(但不一定是最有效的)答案就将使用一个相关的问题:

select
    name
,   amount
,   (select sum(amount) from MyTable t2 where t1.name <= t2.name) RelevantAmount
from MyTable t1
order by t1.Name

Answer 2

为此:

SELECT a.Name, a.Amount, SUM(b.Amount) as RelevantAmount
FROM myTable a
    JOIN myTable b
        ON b.Name <= a.Name
GROUP BY a.Name, a.Amount
ORDER BY a.Name ASC

Answer 3

根据大家的理解,你可以:


SELECT name,
SUM(amount) AS month_amount,
SUM(SUM(amount)) OVER
(ORDER BY amount ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
AS cumulative_amount
FROM table_name
GROUP BY name
ORDER BY name;

友情链接