当前位置： 文江博客话题详情

有人可以向我解释一下本教程中的运行总计和 SQL 自连接吗？

发布于 2024-08-26 23:49:01 字数 247 浏览 17 评论 0原文

我正在阅读这里的教程： http://www.1keydata.com/ sql/sql-running-totals.html 这一切都是有道理的，直到它突然变得极其可笑的难以置信的复杂，当它涉及到排名，中位数，运行总计等时。有人可以用简单的英语解释一下该查询的结果是如何的吗？运行总计？谢谢！

原文

I was reading over the tutorial here: http://www.1keydata.com/sql/sql-running-totals.html and it all made sense until it suddenly got extremely ridiculously unbelievably complicated when it got to rank, median, running totals, etc. Can somebody explain in plain English how that query results in a running total? Thanks!

如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论， 你可以免费 注册 一个本站的账号。

评论（4）

一身软味 2024-09-02 23:49:01

在开始之前，我以前从未见过这种情况，而且它看起来并不是一种非常容易理解的完成运行总计的方法。

好的，这是教程中的查询：

SELECT a1.Name, a1.Sales, SUM(a2.Sales) Running_Total
FROM Total_Sales a1, Total_Sales a2
WHERE a1.Sales <= a2.sales or (a1.Sales=a2.Sales and a1.Name = a2.Name)
GROUP BY a1.Name, a1.Sales
ORDER BY a1.Sales DESC, a1.Name DESC;

示例输出

Name    Sales   Running_Total
Greg     50     50
Sophia    40    90
Stella    20    110
Jeff      20    130
Jennifer  15    145
John      10    155

该查询的简单部分是显示每个员工的销售数据。我们所做的就是从每个员工中选择 name 和 sales 并按销售额（降序）对它们进行排序。这给了我们我们的基本列表。

现在对于运行总计，我们需要已经显示的每一行。因此，我们在已经显示的每一行上将表与自身连接起来：

WHERE a1.Sales <= a2.sales or (a1.Sales=a2.Sales and a1.Name = a2.Name)

然后我们使用 SUM 聚合函数并进行相应的分组。理解这一点的一个好方法是看看如果不使用组功能会发生什么。 “Sophia”行看起来像这样：

Name    A1.Sales    A2.Sales
Sophia  40          50
Sophia    40         40

注意到我们如何获得 Greg 的销售行了吗？小组将对此进行总结，瞧！

希望有帮助。乔

Before I get started, I've not seen this before and it doesn't look like a terribly comprehensible way to accomplish a running total.

Okay, here's the query from the tutorial:

SELECT a1.Name, a1.Sales, SUM(a2.Sales) Running_Total
FROM Total_Sales a1, Total_Sales a2
WHERE a1.Sales <= a2.sales or (a1.Sales=a2.Sales and a1.Name = a2.Name)
GROUP BY a1.Name, a1.Sales
ORDER BY a1.Sales DESC, a1.Name DESC;

And the sample output

Name    Sales   Running_Total
Greg     50     50
Sophia    40    90
Stella    20    110
Jeff      20    130
Jennifer  15    145
John      10    155

The simple part of this query is displaying the sales data for each employee. All we're doing is selecting name and sales from each employee and ordering them by the sale amount (descending). This gives us our base list.

Now for the running total, we want every row that has already been displayed. So, we join the table against itself, on each row that would already have been displayed:

WHERE a1.Sales <= a2.sales or (a1.Sales=a2.Sales and a1.Name = a2.Name)

Then we use the SUM aggregate function and group accordingly. A good way to understand this is if you look at what would happen if you didn't use the group function. The 'Sophia' row would look like this:

Name    A1.Sales    A2.Sales
Sophia  40          50
Sophia    40         40

Notice how we got Greg's sales row? The group will sum that up, and voila!

Hope that helps. Joe

回复 原文
甜柠檬 2024-09-02 23:49:01

我也得到了与上面 Bob 相同的错误输出，其中 Stella & 的运行总数崩溃了。杰夫，他们有相同的销售数字。我正在使用 SQL Server 2014 Management Studio Express。我认为该网站的解决方案实际上并不正确。我根据名称而不是销售额进行连接，并得出了这些结果，从而产生了正确的运行总计：

select a1.name
, a1.sales
, sum(a2.sales) 'running_total'
from #total_sales a1
inner join #total_sales a2 on a1.name <= a2.name 
group by a1.name, a1.sales
order by sum(a2.sales);

产量：

name      sales  running_total
Stella    20     20
Sophia    40     60
John      10     70
Jennifer  15     85
Jeff      20     105
Greg      50     155

如果您对聚合排序感到不舒服，您也可以执行下面的变体。它改变了顺序，但运行总计仍然正确：

select a1.name
, a1.sales
, sum(a2.sales) 'running_total'
from #total_sales a1
inner join #total_sales a2 on a1.name >= a2.name 
group by a1.name, a1.sales
order by a1.name;

产量：

name     sales  running_total
Greg     50     50
Jeff     20     70
Jennifer 15     85
John     10     95
Sophia   40     135
Stella   20     155

I also get the same incorrect output as Bob above where the running total breaks down at Stella & Jeff, who have the same sales number. I'm using SQL Server 2014 Management Studio Express. I don't think the website's solution is actually correct. I did the join based on name instead of on sales and came up with these, which produce a correct running total:

select a1.name
, a1.sales
, sum(a2.sales) 'running_total'
from #total_sales a1
inner join #total_sales a2 on a1.name <= a2.name 
group by a1.name, a1.sales
order by sum(a2.sales);

Yields:

name      sales  running_total
Stella    20     20
Sophia    40     60
John      10     70
Jennifer  15     85
Jeff      20     105
Greg      50     155

You could also do the variant below if you're uncomfortable sorting on an aggregate. It changes the order, but the running total is still correct:

select a1.name
, a1.sales
, sum(a2.sales) 'running_total'
from #total_sales a1
inner join #total_sales a2 on a1.name >= a2.name 
group by a1.name, a1.sales
order by a1.name;

Yields:

name     sales  running_total
Greg     50     50
Jeff     20     70
Jennifer 15     85
John     10     95
Sophia   40     135
Stella   20     155
回复 原文
心碎无痕… 2024-09-02 23:49:01

第一个表连接到自身，连接产生 x 行数，其中 x 是总销售额低于其自身的行数，或者行中的名称相同（即我们所在行之前的所有销售额）正在查看，按销售额订购时）。

然后，它对连接左侧的字段进行分组，并对我们连接到的行进行求和，从而得出运行总计。要了解它是如何工作的，您可能希望在没有求和和分组的情况下运行它，以查看返回的原始结果。

The first table joins to itself, the join resulting in x number of rows, where x is the number of rows that have total sales lower than itself, or the name in the row is the same (i.e. all those sales previous to the row we are looking at, when ordered by sales amount).

It then groups on the fields in the left side of the join and sums the rows we join to, thus a running total. To see how it works, you might want to run it without the sum and grouping, to see the raw results returned.

回复 原文
缺⑴份安定 2024-09-02 23:49:01

上面的 SQL 在 Sybase (ASE 15) 上给出了不同的结果。我认为原因是“排序依据”直到显示时间才应用。这是 SQL 和结果：

drop table Total_Sales
go
create table Total_Sales
(
    Name char(15),
    Sales  int
)

INSERT INTO Total_Sales VALUES( 'John', 10 )
INSERT INTO Total_Sales VALUES( 'Jennifer', 15)
INSERT INTO Total_Sales VALUES('Stella', 20 )
INSERT INTO Total_Sales VALUES('Sophia', 40 )
INSERT INTO Total_Sales VALUES('Greg', 50 )
INSERT INTO Total_Sales VALUES('Jeff', 20 )

SELECT a1.Name, a1.Sales, SUM(a2.Sales) Running_Total 
FROM Total_Sales a1, Total_Sales a2 
WHERE a1.Sales <= a2.Sales or (a1.Sales=a2.Sales and a1.Name = a2.Name) 
GROUP BY a1.Name, a1.Sales 
ORDER BY a1.Sales DESC, a1.Name DESC

结果：

Name           Sales Running_Total   
Greg            50  50   
Sophia          40  90   
Stella          20  130 --note that two running totals are the same! 
Jeff            20  130  
Jennifer        15  145  
John            10  155

Bob

The SQL above gives a different result on Sybase (ASE 15). I think the reason is that the 'order by' is not applied until display time. Here is the SQL and the result:

drop table Total_Sales
go
create table Total_Sales
(
    Name char(15),
    Sales  int
)

INSERT INTO Total_Sales VALUES( 'John', 10 )
INSERT INTO Total_Sales VALUES( 'Jennifer', 15)
INSERT INTO Total_Sales VALUES('Stella', 20 )
INSERT INTO Total_Sales VALUES('Sophia', 40 )
INSERT INTO Total_Sales VALUES('Greg', 50 )
INSERT INTO Total_Sales VALUES('Jeff', 20 )

SELECT a1.Name, a1.Sales, SUM(a2.Sales) Running_Total 
FROM Total_Sales a1, Total_Sales a2 
WHERE a1.Sales <= a2.Sales or (a1.Sales=a2.Sales and a1.Name = a2.Name) 
GROUP BY a1.Name, a1.Sales 
ORDER BY a1.Sales DESC, a1.Name DESC

Result:

Name           Sales Running_Total   
Greg            50  50   
Sophia          40  90   
Stella          20  130 --note that two running totals are the same! 
Jeff            20  130  
Jennifer        15  145  
John            10  155

Bob

回复 原文
~没有更多了~

关于作者

你曾走过我的故事

暂无简介

文章
评论
912 人气
发私信

相关话题

更多

热门标签

操作系统 程序设计 IT运维 Linux系统管理 JavaScript 服务器应用 solaris C/C++ PHP Shell BSD Vue.js aix Oracle Python HTML 系统管理 HTML5 CSS 前端
更多

推荐作者

佚名

文章 0 评论 0

今天

文章 0 评论 0

゛时过境迁

文章 0 评论 0

达拉崩吧

文章 0 评论 0

呆萌少年

文章 0 评论 0

孤者何惧

文章 0 评论 0

更多

友情链接

文江博客
    我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站，即表示您同意使用 Cookies 和您的相关数据。
    原文