SQL 如何合并多个SQL查询？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4441590/
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 Do I Combine Multiple SQL Queries?
提问by Jazzepi
I'm having some trouble figuring out any way to combine two SQL queries into a single one that expresses some greater idea.
我在想办法将两个 SQL 查询组合成一个表达一些更大想法的单个查询时遇到了一些麻烦。
For example, let's say that I have query A, and query B. Query A returns the total number of hours worked. Query B returns the total number of hours that were available for workers to work. Each one of these queries returns a single column with a single row.
例如，假设我有查询 A 和查询 B。查询 A 返回工作总小时数。查询 B 返回可供工作人员工作的总小时数。这些查询中的每一个都返回具有单行的单列。
What I really want, though, is essentially query A over query B. I want to know the percentage of capacity that was worked.
不过，我真正想要的是查询 A 而非查询 B。我想知道已工作的容量百分比。
I know how to write query A and B independently, but my problem comes when I try to figure out how to use those prewritten queries to come up with a new SQL query that uses them together. I know that, on a higher level, like say in a report, I could just call both queries and then divide them, but I'd rather encompass it all into a single SQL query.
我知道如何独立编写查询 A 和 B，但是当我试图弄清楚如何使用这些预先编写的查询来提出将它们一起使用的新 SQL 查询时，我的问题就出现了。我知道，在更高的层次上，就像在报告中说的那样，我可以只调用两个查询然后将它们分开，但我宁愿将它们全部包含在一个 SQL 查询中。
What I'm looking for is a general idea on how to combine these queries using SQL.
我正在寻找的是关于如何使用 SQL 组合这些查询的一般想法。
Thanks!
谢谢！
回答by kevpie
Unconstrained JOIN, Cartesian Product of 1 row by 1 row
无约束 JOIN，1 行 x 1 行的笛卡尔积
SELECT worked/available AS PercentageCapacity
FROM ( SELECT worked FROM A ),
( SELECT available FROM B )
回答by Oded
You can declare variables to store the results of each query and return the difference:
您可以声明变量来存储每个查询的结果并返回差异：
DECLARE @first INT
DECLARE @second INT
SET @first = SELECT val FROM Table...
SET @second = SELECT val FROM Table...
SELECT @first - @second
回答by Justin Niessner
The answer depends on where the data is coming from.
答案取决于数据的来源。
If it's coming from a single table, it could be something as easy as:
如果它来自单个表，则可能很简单：
select totalHours, availableHours, (totalHours - availableHours) as difference
from hoursTable
But if the data is coming from separate tables, you need to add some identifying column so that the rows can be joined together to provide some useful view of the data.
但是如果数据来自不同的表，则需要添加一些标识列，以便可以将行连接在一起以提供一些有用的数据视图。
You may want to post examples of your queries so we know better how to answer your question.
您可能希望发布您的查询示例，以便我们更好地了解如何回答您的问题。
回答by Derek Flenniken
You can query the queries:
您可以查询查询：
SELECT
a.ID
a.HoursWorked/b.HoursAvailable AS UsedWork
FROM
( SELECT ID, HoursWorked FROM Somewhere ) a
INNER JOIN
( SELECT ID, HoursAvailable FROM SomewhereElse ) b
ON
a.ID = b.ID
