将多个表连接在一起并逐行显示结果 [英] Join multiple tables together and show result row by row

问题描述

I have 3 tables in the same database, with couple of columns as common and rest non-matching columns as well. I need to show them together in such a fashion that the user should be able to distinguish between the source tables (Refer below diagrams). I want to know if I can achieve this in database itself, before passing its result on to my report UI or code behind?

常用列数据，表1列数据， NULL，NULL

NULL，NULL，表2列数据，NULL

NULL，NULL，NULL，表3列数据，



我尝试了什么：



我尝试使用OUTER JOIN，FULL OUTER JOIN实现此目的。

common columns data, table 1 columns data, NULL, NULL
NULL, NULL, table 2 columns data, NULL
NULL, NULL, NULL, table 3 columns data,

What I have tried:

I have tried achieving this using OUTER JOIN, FULL OUTER JOIN.

推荐答案

从您描述问题的方式我创建了这个示例数据

From the way you described your problem I created this sample data

create table table1
(
	com1 int,
	com2 int,
	com3 int,
	tab1col1 varchar(125),
	tab1col2 varchar(125),
	tab1col3 varchar(125)
)

create table table2
(
	com1 int,
	com2 int,
	com3 int,
	tab2col1 varchar(125),
	tab2col2 varchar(125),
	tab2col3 varchar(125),
	tab2col4 varchar(125)

)

create table table3
(
	com1 int,
	com2 int,
	com3 int,
	tab3col1 varchar(125),
	tab3col2 varchar(125)
)
insert into table1 values(1,1,1,'c1','c2','c3')
insert into table2 values(1,1,1,'c1','c2','c3','c4')
insert into table3 values(1,1,1,'c1','c2')

提示：提供样本数据和预期结果有助于我们为您提供帮助。

然后我能够生成此查询

HINT: Providing sample data and expected results helps us to help you.
I was then able to produce this query

select com1, com2, com3 
, 'Table2' as TableName, tab2col1 as Col1, tab2col2 as Col2, tab2col3 as Col3, tab2col4  as Col4
from Table2
UNION ALL
select com1, com2, com3
, 'Table1', tab1col1, tab1col2, tab1col3, NULL
from Table1
UNION ALL
select com1, com2, com3 
, 'Table3', tab3col1, tab3col2, null, null
from table3

产生这些结果：

Which produces these results:

com1    com2    com3    TableName  Col1 Col2 Col3 Col4
1	1	1	Table2	   c1	c2   c3	   c4
1	1	1	Table1	   c1	c2   c3	   NULL
1	1	1	Table3	   c1	c2   NULL  NULL

注意事项：

- 我在联合列表中首先使用了列数最多的表，这样我就不必将空值转换为适当的类型。换句话说，我需要这样的东西

Things to note:
- I used the table that had the most columns first in the list of unions so that I didn't have to cast the null values to the appropriate type. In other words I would have needed something like this

select com1, com2, com3
, 'Table1' as TableName, tab1col1 as "Col1", tab1col2 as "Col2", tab1col3 as "Col3", cast(null as varchar(125)) as "Col4"
from Table1
UNION ALL
...

- 我给结果列任意名称（Col1，Col2，Col3，Col4） ） - 他们很可能甚至不会代表类似的东西（从你的问题中不清楚）

- 我使用了硬编码的文本作为[TableName]，所以逐行你可以找出数据来自哪个表

- UNION中使用的结果必须具有相同的列数，因此对于具有较少列的表，我返回NULL。

- 我使用了UNION ALL，它不会删除重复项（而不是UNION）。在这种情况下并不是绝对必要的，因为硬编码[TableName]会阻止重复，但这应该（在理论上）稍微更高效，因为我明确地说不要寻找重复

- I've given the results columns arbitrary names (Col1, Col2, Col3, Col4) - they quite possibly won't even represent similar things (it is not clear from your question)
- I've used hard-coded text as [TableName] so that row-by-row you can work out which table the data came from
- The results used in a UNION must have the same number of columns, so for the tables with fewer columns I've returned NULL.
- I used UNION ALL which will not remove duplicates (rather than UNION which would). Not strictly necessary in this case as the hard-coded [TableName] would prevent duplicates, but this should (in theory) be slightly more performant because I've explicitly said "don't look for duplicates"

Quote：

我想知道我是否可以在数据库本身实现这一点，然后将结果传递给我的报告UI或代码？

I want to know if I can achieve this in database itself, before passing its result on to my report UI or code behind?

我不完全确定你的意见是什么意思，但你用于报告或将信息返回到后面的代码的查询就是你这样做的地方。



如果这不是你想要的，那么提供一些样本数据和预期结果。

I'm not entirely sure what you meant by this comment, but the query that you would use for your report or to return information to the code behind is where you do this.

If this wasn't what you were looking for then provide some sample data and expected results.

这篇关于将多个表连接在一起并逐行显示结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！