SQL 如何在同一个查询中添加两个选择命令的结果
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15149775/
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
How to add results of two select commands in same query
提问by Rhys
I currently have two select commands as per below. What I would like to do is to add the results together in the SQL query rather than the variables in code.
我目前有两个选择命令，如下所示。我想做的是将结果添加到 SQL 查询中，而不是代码中的变量。
select sum(hours) from resource;
select sum(hours) from projects-time;
Is it possible to have both in the same SQL and output a sum of both results?
是否可以在同一个 SQL 中同时输出两个结果的总和？
回答by John Woo
Yes. It is possible :D
是的。有可能的:D
SELECT SUM(totalHours) totalHours
FROM
(
select sum(hours) totalHours from resource
UNION ALL
select sum(hours) totalHours from projects-time
) s
As a sidenote, the tablename projects-timemust be delimited to avoid syntax error. Delimiter symbols vary on RDBMS you are using.
作为旁注，projects-time必须对表名进行分隔以避免语法错误。分隔符因您使用的 RDBMS 而异。
回答by Gordon Linoff
Something simple like this can be done using subqueries in the selectclause:
可以使用select子句中的子查询来完成这样的简单操作：
select ((select sum(hours) from resource) +
(select sum(hours) from projects-time)
) as totalHours
For such a simple query as this, such a subselect is reasonable.
对于这样一个简单的查询，这样的子选择是合理的。
In some databases, you might have to add from dualfor the query to compile.
在某些数据库中，您可能必须添加from dual才能编译查询。
If you want to output each individually:
如果要单独输出每个：
select (select sum(hours) from resource) as ResourceHours,
(select sum(hours) from projects-time) as ProjectHours
If you want both andthe sum, a subquery is handy:
如果你想要两者和总和，子查询很方便：
select ResourceHours, ProjectHours, (ResourceHours+ProjecctHours) as TotalHours
from (select (select sum(hours) from resource) as ResourceHours,
(select sum(hours) from projects-time) as ProjectHours
) t
回答by Erwin Brandstetter
UNION ALLonce, aggregate once:
UNION ALL一次，汇总一次：
SELECT sum(hours) AS total_hours
FROM (
SELECT hours FROM resource
UNION ALL
SELECT hours FROM "projects-time" -- illegal name without quotes in most RDBMS
) x
回答by Pratik Roy
Repeat for Multiple aggregations like
重复多个聚合，如
SELECT sum(AMOUNT) AS TOTAL_AMOUNT FROM ( SELECT AMOUNT FROM table_1 UNION ALL SELECT AMOUNT FROM table_2 UNION ALL SELECT ASSURED_SUM FROM table_3 .... )
SELECT sum(AMOUNT) AS TOTAL_AMOUNT FROM ( SELECT AMOUNT FROM table_1 UNION ALL SELECT AMOUNT FROM table_2 UNION ALL SELECT ASSURED_SUM FROM table_3 .... )
回答by Saleh Ahmed
If you want to make multiple operation use
如果要进行多项操作使用
select (sel1.s1+sel2+s2)
(select sum(hours) s1 from resource) sel1
join
(select sum(hours) s2 from projects-time)sel2
on sel1.s1=sel2.s2
