SQL多表记录合并：如何查询各年度表中匹配条件的记录？

兄弟，你当前的SQL写法踩了个大坑：直接把多个表用逗号拼接会触发交叉连接（笛卡尔积），这会让所有表的记录无规则地交叉匹配，不仅数据量爆炸，结果也完全不符合你的需求。

要从每个年度表中分别取出符合条件的记录并合并结果，正确的做法是用UNION ALL（如果需要去重复记录就用UNION，但年度数据一般不会重复，UNION ALL性能更高）来逐个合并每个表的查询结果。

直接写法：逐个查询后合并 #

你可以给每个年度表单独写一条带条件的SELECT语句，然后用UNION ALL把它们连起来，注意每个SELECT的字段数量、顺序、数据类型必须完全一致：

SELECT LastName, FirstName, BirthYear, LocalExp, TotalExp, TotSalary, TotFringe, WorkLocationName, SchoolName
FROM DPISTAFF2008_2009
WHERE FirstName = 'George'
UNION ALL
SELECT LastName, FirstName, BirthYear, LocalExp, TotalExp, TotSalary, TotFringe, WorkLocationName, SchoolName
FROM DPISTAFF2009_2010
WHERE FirstName = 'George'
UNION ALL
SELECT LastName, FirstName, BirthYear, LocalExp, TotalExp, TotSalary, TotFringe, WorkLocationName, SchoolName
FROM DPISTAFF2010_2011
WHERE FirstName = 'George'
UNION ALL
SELECT LastName, FirstName, BirthYear, LocalExp, TotalExp, TotSalary, TotFringe, WorkLocationName, SchoolName
FROM DPISTAFF2011_2012
WHERE FirstName = 'George'
UNION ALL
SELECT LastName, FirstName, BirthYear, LocalExp, TotalExp, TotSalary, TotFringe, WorkLocationName, SchoolName
FROM DPISTAFF2012_2013
WHERE FirstName = 'George'
UNION ALL
SELECT LastName, FirstName, BirthYear, LocalExp, TotalExp, TotSalary, TotFringe, WorkLocationName, SchoolName
FROM DPISTAFF2013_2014
WHERE FirstName = 'George'
UNION ALL
SELECT LastName, FirstName, BirthYear, LocalExp, TotalExp, TotSalary, TotFringe, WorkLocationName, SchoolName
FROM DPISTAFF2014_2015
WHERE FirstName = 'George'
UNION ALL
SELECT LastName, FirstName, BirthYear, LocalExp, TotalExp, TotSalary, TotFringe, WorkLocationName, SchoolName
FROM DPISTAFF2015_2016
WHERE FirstName = 'George';

更优雅的方案：创建合并视图 #

如果以后还要频繁查询这些年度表，建议先创建一个视图把所有年度表合并起来，还能额外加一个字段标记记录所属的年度区间，方便后续分析：

CREATE VIEW AllDPIStaff AS
SELECT '2008_2009' AS YearRange, LastName, FirstName, BirthYear, LocalExp, TotalExp, TotSalary, TotFringe, WorkLocationName, SchoolName
FROM DPISTAFF2008_2009
UNION ALL
SELECT '2009_2010' AS YearRange, LastName, FirstName, BirthYear, LocalExp, TotalExp, TotSalary, TotFringe, WorkLocationName, SchoolName
FROM DPISTAFF2009_2010
UNION ALL
SELECT '2010_2011' AS YearRange, LastName, FirstName, BirthYear, LocalExp, TotalExp, TotSalary, TotFringe, WorkLocationName, SchoolName
FROM DPISTAFF2010_2011
UNION ALL
SELECT '2011_2012' AS YearRange, LastName, FirstName, BirthYear, LocalExp, TotalExp, TotSalary, TotFringe, WorkLocationName, SchoolName
FROM DPISTAFF2011_2012
UNION ALL
SELECT '2012_2013' AS YearRange, LastName, FirstName, BirthYear, LocalExp, TotalExp, TotSalary, TotFringe, WorkLocationName, SchoolName
FROM DPISTAFF2012_2013
UNION ALL
SELECT '2013_2014' AS YearRange, LastName, FirstName, BirthYear, LocalExp, TotalExp, TotSalary, TotFringe, WorkLocationName, SchoolName
FROM DPISTAFF2013_2014
UNION ALL
SELECT '2014_2015' AS YearRange, LastName, FirstName, BirthYear, LocalExp, TotalExp, TotSalary, TotFringe, WorkLocationName, SchoolName
FROM DPISTAFF2014_2015
UNION ALL
SELECT '2015_2016' AS YearRange, LastName, FirstName, BirthYear, LocalExp, TotalExp, TotSalary, TotFringe, WorkLocationName, SchoolName
FROM DPISTAFF2015_2016;

之后查询就变得非常简单，直接查视图就行：

SELECT * FROM AllDPIStaff WHERE FirstName = 'George';

以后新增年度表时，只需要更新这个视图，不用每次都写一大串UNION ALL。

注意事项 #

• 必须保证每个SELECT语句的字段完全匹配：数量相同、顺序一致、数据类型兼容，否则会报错。
• 如果不确定表结构是否一致，可以用数据库自带的命令检查，比如MySQL用DESCRIBE DPISTAFF2008_2009;，SQL Server用sp_columns DPISTAFF2008_2009。

内容的提问来源于stack exchange，提问作者InterLinked