Oracle SQL Developer创建表遇重复列load_Tstamp，求重命名或替代方案

问题原因 #

你的SQL语句中，内层子查询同时使用a.*和b.*，而mdmstggeo.T_USPS_DETAIL_RECORD与DSSCORP.T_STATE两张表都包含LOAD_TSTMP列，导致子查询结果集出现重复列名，Oracle不允许创建列名重复的表，因此触发报错。

可行解决方案 #

方案一：显式指定列并给重复列重命名 #

放弃使用SELECT *，显式列出所有需要的列，对两张表的LOAD_TSTMP分别赋予不同别名，确保结果集列名唯一：

Create table New_County_code as (
   Select t.*
   from (
      Select 
         CONCAT(b.STATE_FIPS_CODE,a.ZIP_CNTY_CDE) AS "Final_County_Code",
         -- 列出a表需要的列，重命名LOAD_TSTMP
         a.LOAD_TSTMP as usps_load_tstmp,
         a.ZIP_CNTY_CDE,
         a.ST_CPTL_BLDG_ZIP_CDE,
         a.ds_batch_gid,
         -- 补充a表其他需要的列
         -- 列出b表需要的列，重命名LOAD_TSTMP
         b.LOAD_TSTMP as state_load_tstmp,
         b.STATE_FIPS_CODE,
         -- 补充b表其他需要的列
      from  mdmstggeo.T_USPS_DETAIL_RECORD@ODSDEV a
      left join  (
         select * 
         from DSSCORP.T_STATE@ds31 
         where STATE_FIPS_CODE is not  null) b  
      on ST_CPTL_BLDG_ZIP_CDE = DTL_ZIP_CDE
      WHERE ds_batch_gid=1661 
      order by STATE_FIPS_CODE 
    ) t
);

方案二：排除其中一张表的重复列 #

如果只需要保留其中一张表的LOAD_TSTMP，在子查询中排除另一张表的该列即可：

Create table New_County_code as (
   Select t.*, b.LOAD_TSTMP as Load_time
   from (
      Select 
         CONCAT(b.STATE_FIPS_CODE,a.ZIP_CNTY_CDE) AS "Final_County_Code",
         a.*,
         -- 列出b表所有列，排除LOAD_TSTMP
         b.STATE_FIPS_CODE,
         b.ST_CPTL_BLDG_ZIP_CDE,
         -- 补充b表其他需要的列（除LOAD_TSTMP）
      from  mdmstggeo.T_USPS_DETAIL_RECORD@ODSDEV a
      left join  (
         select * 
         from DSSCORP.T_STATE@ds31 
         where STATE_FIPS_CODE is not  null) b  
      on ST_CPTL_BLDG_ZIP_CDE = DTL_ZIP_CDE
      WHERE ds_batch_gid=1661 
      order by STATE_FIPS_CODE 
    ) t
);

方案三：外层查询明确选择唯一列名 #

如果需要保留两个LOAD_TSTMP，在内层子查询给它们不同别名后，外层只选择需要的列：

Create table New_County_code as (
   Select 
      t."Final_County_Code",
      t.usps_load_tstmp,
      t.state_load_tstmp as Load_time,
      -- 补充其他需要的列
   from (
      Select 
         CONCAT(b.STATE_FIPS_CODE,a.ZIP_CNTY_CDE) AS "Final_County_Code",
         a.*,
         b.LOAD_TSTMP as state_load_tstmp,
         -- 列出b表其他列（排除原LOAD_TSTMP）
         b.STATE_FIPS_CODE,
         -- 补充b表其他需要的列
      from  mdmstggeo.T_USPS_DETAIL_RECORD@ODSDEV a
      left join  (
         select * 
         from DSSCORP.T_STATE@ds31 
         where STATE_FIPS_CODE is not  null) b  
      on ST_CPTL_BLDG_ZIP_CDE = DTL_ZIP_CDE
      WHERE ds_batch_gid=1661 
      order by STATE_FIPS_CODE 
    ) t
);

关键注意事项 #

• 生产环境禁止使用SELECT *，多表关联时极易引发重复列、冗余列问题，且后续表结构变更会导致SQL逻辑异常。
• 可通过以下语句查看表结构，明确需要的列：

DESC mdmstggeo.T_USPS_DETAIL_RECORD@ODSDEV;
DESC DSSCORP.T_STATE@ds31;

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