带有子查询的 SQL Sum？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9985674/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Sum with Sub Query?
提问by gbusman
Thanks for any help in advance, I can't wrap my SQL skills around this one...
I have two tables like this:
提前感谢您的任何帮助，我无法将我的 SQL 技能包装在这个……我有两个这样的表：
Settings
设置
Customerid ViewerLimit
1 50
2 50
Customerid 查看者限制
1 50
2 50
Distribution
分配
Customerid ServerIP
1 stream3
1 stream4
2 stream3
CUSTOMERID SERVERIP
1 STREAM3
1 STREAM4
2 STREAM3
I want to calculate the load on each server. A customer divides the load if they have more than one server, so here customer 1 puts a load of 25 on each server. The result I'm trying to get is this:
我想计算每台服务器上的负载。如果客户拥有多台服务器，则客户会分配负载，因此这里客户 1 在每台服务器上放置 25 个负载。我想要得到的结果是这样的：
ServerIP Load
stream3 75
stream4 25
SERVERIP 负载
STREAM3 75
STREAM4 25
I tried to do a sum function similar to this:
我试图做一个类似于这样的 sum 函数：
sum(viewerlimit/(count # of servers)) as load group by serverip
But I can't do a sub query within a sum function. There are many customers and possibly many servers per customer so it will become too complex to do manually. I appreciate any input.
但是我不能在 sum 函数中进行子查询。有很多客户，每个客户可能有很多服务器，因此手动操作会变得太复杂。我感谢任何输入。
采纳答案by Nikola Markovinovi?
Here is uninspired version with count in derived table:
这是派生表中有计数的无灵感版本：
select serverip, sum (viewerlimit/cast (ServerCount as float)) Load
from
(
select customerid, count(*) ServerCount from distribution group by customerid
) a
inner join settings
on a.customerid = settings.customerid
inner join distribution
on settings.customerid = distribution.customerid
group by serverip
UPDATE - an attempt at explanation
更新 - 尝试解释
Derived tables are used to produce ad-hoc result sets that can be joined to main body of a query. It is placed in from clause and enclosed in parenthesis. You can use anything an ordinary select can use, top, order by, aggregate functions etc. The only thing you cannot use is correlation to a table in main body. Oh, and CTE. Derived table must be aliased.
派生表用于生成可以连接到查询主体的临时结果集。它放在 from 子句中并括在括号中。您可以使用普通选择可以使用的任何东西，top、order by、聚合函数等。唯一不能使用的是与主体中的表的关联。哦，还有 CTE。派生表必须具有别名。
In previous sample derived table 'a' selects counts of servers by customerid. Main body sees it as a table with CustomerId and ServerCount columns, ready to be used as any column from all listed tables. A join on customerid is performed between settings and 'a'. As this is 1:1 relation (both tables produce single row given a customerid), no duplication occurs.
在前面的示例中，派生表“a”按客户 ID 选择服务器计数。主体将其视为包含 CustomerId 和 ServerCount 列的表，准备用作所有列出的表中的任何列。在设置和“a”之间执行对 customerid 的连接。由于这是 1:1 关系（两个表都生成给定 customerid 的单行），因此不会发生重复。
回答by quzary
select d.serverip, sum(s.viewerlimit/d.devider)
from (
select customerid, serverip, COUNT(serverip) over (partition by customerid) servercount
from distribution
) d
join settings s
on s.customerid=d.customerid
group by d.serverip
回答by Anand Nandakumar
How about doing a count of servers in a sub query and assign that to a query variable and then use that query variable inside the sum function?
如何在子查询中计算服务器数量并将其分配给查询变量，然后在 sum 函数中使用该查询变量？
