SQL 如何将两个查询的结果合并为一个数据集
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15230350/
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 combine results of two queries into a single dataset
提问by CodeNinja
I have two queries : Queries Simplified excluding Joins
我有两个查询：查询简化，不包括连接
Query 1 : select ProductName,NumberofProducts (in inventory) from Table1.....;
Query 2 : select ProductName, NumberofProductssold from Table2......;
I would like to know how I can get an output as :
我想知道如何获得输出：
ProductName NumberofProducts(in inventory) ProductName NumberofProductsSold
The relationships used for getting the outputs for each query are different. I need the output this way for my SSRS report .
用于获取每个查询的输出的关系是不同的。我的 SSRS 报告需要这种方式的输出。
(I tried the union statement but it doesnt work for the output I want to see. )
（我尝试了 union 语句，但它不适用于我想看到的输出。）
回答by GojiraDeMonstah
Here is an example that does a union between two completely unrelated tables: the Student and the Products table. It generates an output that is 4 columns:
这是一个在两个完全不相关的表之间进行联合的示例：Student 和 Products 表。它生成一个 4 列的输出：
select
FirstName as Column1,
LastName as Column2,
email as Column3,
null as Column4
from
Student
union
select
ProductName as Column1,
QuantityPerUnit as Column2,
null as Column3,
UnitsInStock as Column4
from
Products
Obviously you'll tweak this for your own environment...
显然，您会针对自己的环境进行调整...
回答by Kaf
I think you are after something like this; (Using row_number()with CTEand performing a FULL OUTER JOIN)
我认为你在追求这样的事情；（使用row_number()带有CTE和执行FULL OUTER JOIN）
;with t1 as (
select col1,col2, row_number() over (order by col1) rn
from table1
),
t2 as (
select col3,col4, row_number() over (order by col3) rn
from table2
)
select col1,col2,col3,col4
from t1 full outer join t2 on t1.rn = t2.rn
Tables and data :
表格和数据：
create table table1 (col1 int, col2 int)
create table table2 (col3 int, col4 int)
insert into table1 values
(1,2),(3,4)
insert into table2 values
(10,11),(30,40),(50,60)
Results :
结果 ：
| COL1 | COL2 | COL3 | COL4 |
---------------------------------
| 1 | 2 | 10 | 11 |
| 3 | 4 | 30 | 40 |
| (null) | (null) | 50 | 60 |
回答by Jodrell
How about,
怎么样，
select
col1,
col2,
null col3,
null col4
from Table1
union all
select
null col1,
null col2,
col4 col3,
col5 col4
from Table2;
回答by Roney Michael
If you mean that both ProductNamefields are to have the same value, then:
如果您的意思是两个ProductName字段都具有相同的值，则：
SELECT a.ProductName,a.NumberofProducts,b.ProductName,b.NumberofProductsSold FROM Table1 a, Table2 b WHERE a.ProductName=b.ProductName;
Or, if you want the ProductNamecolumn to be displayed only once,
或者，如果您希望该ProductName列只显示一次，
SELECT a.ProductName,a.NumberofProducts,b.NumberofProductsSold FROM Table1 a, Table2 b WHERE a.ProductName=b.ProductName;
Otherwise,if any row of Table1 can be associated with any row from Table2 (even though I really wonder why anyone'd want to do that), you could give thisa look.
否则，如果 Table1 的任何行可以与 Table2 中的任何行相关联（尽管我真的很想知道为什么有人想要这样做），您可以看看这个。
回答by David T. Macknet
The problem is that unless your tables are related you can't determine how to join them, so you'd have to arbitrarily join them, resulting in a cartesian product:
问题是，除非您的表是相关的，否则您无法确定如何加入它们，因此您必须任意加入它们，从而产生笛卡尔积：
select Table1.col1, Table1.col2, Table2.col3, Table2.col4
from Table1
cross join Table2
If you had, for example, the following data:
例如，如果您有以下数据：
col1 col2
a 1
b 2
col3 col4
y 98
z 99
You would end up with the following:
你最终会得到以下结果：
col1 col2 col3 col4
a 1 y 98
a 1 z 99
b 2 y 98
b 2 z 99
Is this what you're looking for? If not, and you have some means of relating the tables, then you'd need to include that in joining the two tables together, e.g.:
这是你要找的吗？如果没有，并且您有一些关联表格的方法，那么您需要在将两个表格连接在一起时包括它，例如：
select Table1.col1, Table1.col2, Table2.col3, Table2.col4
from Table1
inner join Table2
on Table1.JoiningField = Table2.JoiningField
That would pull things together for you into however the data is related, giving you your result.
无论数据是相关的，这都会为您汇总所有内容，从而为您提供结果。
回答by www
Try this:
尝试这个：
SELECT ProductName,NumberofProducts ,NumberofProductssold
FROM table1
JOIN table2
ON table1.ProductName = table2.ProductName
回答by RandomUs1r
Load each query into a datatable:
将每个查询加载到数据表中：
http://www.dotnetcurry.com/ShowArticle.aspx?ID=143
http://www.dotnetcurry.com/ShowArticle.aspx?ID=143
load both datatables into the dataset:
将两个数据表加载到数据集中：
http://msdn.microsoft.com/en-us/library/aeskbwf7%28v=vs.80%29.aspx
http://msdn.microsoft.com/en-us/library/aeskbwf7%28v=vs.80%29.aspx
回答by Praveen Nambiar
This is what you can do. Assuming that your
ProductNamecolumn have common values.
这是你可以做的。假设您的
ProductName列具有共同的值。
SELECT
Table1.ProductName,
Table1.NumberofProducts,
Table2.ProductName,
Table2.NumberofProductssold
FROM Table1
INNER JOIN Table2
ON Table1.ProductName= Table2.ProductName
回答by Roland
Old question, but where others use JOIN to combine unrelated queries to rows in one table, this is my solution to combine unrelated queries to one row, e.g:
老问题，但其他人使用 JOIN 将无关查询组合到一个表中的行，这是我将无关查询组合到一行的解决方案，例如：
select
(select count(*) c from v$session where program = 'w3wp.exe') w3wp,
(select count(*) c from v$session) total,
sysdate
from dual;
which gives the following one-row output:
这给出了以下一行输出：
W3WP TOTAL SYSDATE
----- ----- -------------------
14 290 2020/02/18 10:45:07
(which tells me that our web server currently uses 14 Oracle sessions out of the total of 290 sessions; I log this output without headers in an sqlplus script that runs every so many minutes)
（这告诉我，我们的 Web 服务器当前使用 290 个会话中的 14 个 Oracle 会话；我在每隔几分钟运行一次的 sqlplus 脚本中记录此输出而没有标头）
回答by scorpionsrk7
Try this:
尝试这个：
GET THE RECORD FOR CURRENT_MONTH, LAST_MONTH AND ALL_TIME AND MERGE THEM INTO SINGLE ARRAY
获取 CURRENT_MONTH、LAST_MONTH 和 ALL_TIME 的记录并将它们合并到单个数组中
$analyticsData = $this->user->getMemberInfoCurrentMonth($userId);
$analyticsData1 = $this->user->getMemberInfoLastMonth($userId);
$analyticsData2 = $this->user->getMemberInfAllTime($userId);
foreach ($analyticsData2 as $arr) {
foreach ($analyticsData1 as $arr1) {
if ($arr->fullname == $arr1->fullname) {
$arr->last_send_count = $arr1->last_send_count;
break;
}else{
$arr->last_send_count = 0;
}
}
foreach ($analyticsData as $arr2) {
if ($arr->fullname == $arr2->fullname) {
$arr->current_send_count = $arr2->current_send_count;
break;
}else{
$arr->current_send_count = 0;
}
}
}
echo "<pre>";
print_r($analyticsData2);die;
