好的 - 我不是那个投票给你问题的人，但我能理解为什么有人这么做。



回答你的问题



1）否 - 对于你的目标，这不是***的查询。我不相信你的MainTable设计是***的，但至少我认为它是可扩展的。不需要所有临时表和多个选择......这些在SQL2005上甚至不能按原样运行。这个简化版本也可以正常工作

 选择 * 从 MainTable 
 LEFT   OUTER   JOIN  table1   MainTable.FID = table1.ID 和 MainTable.TypeID =  1  
 LEFT   OUTER  > JOIN  table2   MainTable.FID = table2.ID 和 MainTable .TypeID =  2  
 LEFT   OUTER   JOIN  table3   MainTable.FID = table3.ID 和 MainTable.TypeID =  3  
 LEFT   OUTER   JOIN  table4  on  MainTable.FID = table4.ID 和 MainTable.TypeID =  4  
 LEFT   OUTER   JOIN  table5   MainTable.FID = table5.ID 和 MainTable.TypeID =  5  

2）要合并列，请使用COALESCE表达式合并的有用链接 [ ^ ]例如如果你的每个tablex上有一列数据

 选择 MainTable.ID，MainTable.FID，MainTable.TypeID，
 COALESCE （table1.data，table2.data，table3.data，table4 .data，table5.data）
来自 MainTable 
 LEFT   OUTER   JOIN  table1  on  MainTable.FID = table1.ID 和 MainTable.TypeID =  1  
 LEFT   OUTER   JOIN  table2   MainTable。 FID = table2.ID 和 MainTable.TypeID =  2  
 LEFT   OUTER   JOIN  table3   MainTable.FID = table3.ID 和 MainTable.TypeID =  3  
 LEFT   OUTER   JOIN  table4   MainTable.FID = table4.ID 和 MainTable.TypeID =  4  
 LEFT   OUTER   JOIN  table5   MainTable.FID = table5.ID 和 MainTable.TypeID =  5  

返回类似的内容...

 ID FID TypeID（无列名）
 1 1 1 table1-1 
 2 2 1 table1-2 
 3 3 1 table1-3 
 4 1 2 table2-1 
 5 2 2 table2-2 
 6 3 2 table2-3 
 7 1 3 table3-1 
 8 2 3 table3-2 
 9 3 3 table3-3 
 10 1 4 table4-1 
 11 2 4 table4-2 
 12 3 4 table4-3 
 13 1 5 table5-1 
 14 2 5 table5-2 
 15 3 5 table5-3 

I have a table(MainTable) in my sql database which has 5 field.One of these fields(FID) is foreign key of 5 other tables in the same database.The other field(TypeID) is containing an ID which shows that the key in FID is belong to which table of that 5 tables.For example if FID=1000 and TypeID=1 for a record in MainTable it means that 1000 is a primary key in table number one.
Now i want to join these tables to a new table so i have made a view and used LEFT OUTER JOIN command between each of those 5 tables and MainTable :

Select * From
      (Select * From
        (Select * From
           (Select * From
             (Select * From MainTable LEFT OUTER JOIN Table1 ON MainTable.FID=Table1.ID AND  MainTable.TypeID=1)AS Temp1
LEFT OUTER JOIN Table2 ON Temp1.FID=Table2.ID AND Temp1.TypeID=2)AS Temp2
  LEFT OUTER JOIN Table3 ON Temp2.FID=Table3.ID AND Temp2.TypeID=3)AS Temp3
    LEFT OUTER JOIN Table4 ON Temp3.FID=Table4.ID AND Temp3.TypeID=4)AS Temp4
       LEFT OUTER JOIN Table5 ON Temp4.FID=Table5.ID AND Temp4.TypeID=5

1)Is the above query the best one for this aim?

2)Is there any way to merge some columns of the result table in a special column?I mean that if the result is something like this :

field1 field2 field3 field4
-------------------------------------------------
value1 NULL NULL NULL
NULL value2 NULL NULL
NULL NULL value3 NULL
NULL NULL NULL value4

convert it to something like below :

new_field
-------------
value1
value2
value3
value4

Ok - I wasn''t the one that voted your question down but I can understand why someone did.

To answer your questions

1) No - this is not the best query for whatever your aim is. I''m not convinced that your design for MainTable is the best either but at least it''s extensible I suppose. There is no need for all of the temporary tables and multiple selects ... which wouldn''t even run as-is on SQL2005. This simplified version would work just as well

select * from MainTable
LEFT OUTER JOIN table1 on MainTable.FID=table1.ID and MainTable.TypeID = 1
LEFT OUTER JOIN table2 on MainTable.FID=table2.ID and MainTable.TypeID = 2
LEFT OUTER JOIN table3 on MainTable.FID=table3.ID and MainTable.TypeID = 3
LEFT OUTER JOIN table4 on MainTable.FID=table4.ID and MainTable.TypeID = 4
LEFT OUTER JOIN table5 on MainTable.FID=table5.ID and MainTable.TypeID = 5

2) To merge columns use the COALESCE expression Useful link on coalesce[^] e.g. if you have a column data on each of your tablex

select MainTable.ID, MainTable.FID, MainTable.TypeID,
COALESCE(table1.data, table2.data, table3.data, table4.data, table5.data)
from MainTable
LEFT OUTER JOIN table1 on MainTable.FID=table1.ID and MainTable.TypeID = 1
LEFT OUTER JOIN table2 on MainTable.FID=table2.ID and MainTable.TypeID = 2
LEFT OUTER JOIN table3 on MainTable.FID=table3.ID and MainTable.TypeID = 3
LEFT OUTER JOIN table4 on MainTable.FID=table4.ID and MainTable.TypeID = 4
LEFT OUTER JOIN table5 on MainTable.FID=table5.ID and MainTable.TypeID = 5

returns something like ...

ID	FID	TypeID	(No column name)
1	1	1	table1-1
2	2	1	table1-2
3	3	1	table1-3
4	1	2	table2-1
5	2	2	table2-2
6	3	2	table2-3
7	1	3	table3-1
8	2	3	table3-2
9	3	3	table3-3
10	1	4	table4-1
11	2	4	table4-2
12	3	4	table4-3
13	1	5	table5-1
14	2	5	table5-2
15	3	5	table5-3