理解SQL Server中具有函数的自联接

Question

有人能帮我理解我的查询吗？我知道这是正确的，但不知怎么的，我不知道我是怎么得出这个结果的。

查询：

Select 
    A.Name, A.Age, SUM(B.Age) Running_Total
from 
    infoTbl  A, infoTbl B 
Where 
    A.Age <= B.Age 
Group By 
    A.Name, A.Age
Order By 
    A.Age Desc 

结果：

Name    Age   Running Total
---------------------------
A        25        25
B        20        45
C        18        63
D        15        78
E        1         79
F        5         84

的含义是什么

Where A.Age <= B.Age

你能给我一幅插图吗？非常感谢。

Answer 1

让我们将查询分解为步骤

1.

Select 
    A.Name, A.Age, b.Name as bName, B.Age as bAge
from 
    infoTbl  A, infoTbl B 
Where 
    A.Age <= B.Age 
Order By 
    A.Age desc, B.Age

它相当于

Select 
    A.Name, A.Age, b.Name as bName, B.Age as bAge
from 
    infoTbl  A
    inner join infoTbl B on A.Age <= B.Age 
Order By 
    A.Age desc, B.Age

这将给你：对于表A中的每一条记录，B中都有一条年龄相同或更高的记录。

Name    Age   bName   bAge
---------------------------
A        25    A       25
B        20    B       20
B        20    A       25
C        18    C       18
C        18    B       20
C        18    A       25
...
etc...

当你现在把总和和分组加上你得到的是一个总年龄，比如说年龄等于一岁或更大的人

Select 
    A.Name, A.Age, SUM(B.Age) AS Running_Total
from 
    infoTbl  A
    inner join infoTbl B on A.Age <= B.Age
group by
    A.Name, A.Age 
Order By 
    A.Age desc

Name    Age   Running Total
---------------------------
A        25        25          (25)
B        20        45          (20 + 25 which is age of B+A)
C        18        63          (18 + 20 + 25 which is age of C+B+A)

Answer 2

Where A.Age>=B.Age为您获取要添加的下一行。

你可以用这个代替自加入

select *,sum(Age)over(order by Name) as Running_Total from table