从三个不同的表创建一个表 [英] Creating one Table from three different Tables

问题描述

我在SQL中有3个表，我需要将它们全部组合成一个表。我需要一个表中所有表的所有字段。所有表都包含来自三个不同年份的相同字段。我写的代码是：

I have three tables in SQL and I need them to all be combined into one. I need all the fields from all the tables in the one table. All the tables contain the same fields just from three different years. I wrote a code that is:

CREATE TABLE COL_TBL_TRAINING_ALL_YEARS AS(
SELECT 
COL_TBL_2010_TRN_RESULTS_new.*,
COL_TBL_TRN_RESULTS_GEMS_2011.*, 
COL_TBL_TRN_RESULTS_GEMS_2012.*
FROM COL_TBL_2010_TRN_RESULTS_new,
COL_TBL_TRN_RESULTS_GEMS_2011, 
COL_TBL_TRN_RESULTS_GEMS_2012
WHERE COL_TBL_2010_TRN_RESULTS_new.SYS_EMP_ID_NR = COL_TBL_TRN_RESULTS_GEMS_2011.SYS_EMP_ID_NR = COL_TBL_TRN_RESULTS_GEMS_2012.SYS_EMP_ID_NR)

我在单词'AS'错误附近得到了错误的语法，在'='附近得到了错误的语法

And I get an incorrect syntax near the word 'AS' error and incorrect syntax near '='

我已经阅读了我的SQL书籍，似乎无法找到执行此操作的方法的说明，非常感谢您。

I have read my SQL books and cannot seem to find an explanation on the method to do this, any help would be greatly appreciated.

推荐答案

您需要执行两步操作：

1. 使用 UNION ALL ，就像前面提到的那样。但是，如果缺少一列，则应对该列使用 NULL 。

1. Use the UNION ALL, like it was mentioned. But if a column is missing you should use NULL for that column.

您应插入结果在新表中。

You should insert the result in a new table.

所以它应该看起来像：

SELECT * 
INTO   yournewtablename 
FROM   (SELECT col1, 
               col2, 
               col3 
        FROM   col_tbl_2010_trn_results_new 
        UNION ALL 
        SELECT col1, 
               col2, 
               col3 
        FROM   col_tbl_trn_results_gems_2011 
        UNION ALL 
        SELECT col1, 
               col2, 
               NULL AS Col3 
        FROM   col_tbl_trn_results_gems_2012) n 

以下是演示示例： SQL字段

这篇关于从三个不同的表创建一个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！