无需在代码中进行任何连接即可获得“列的定义不明确”

debugcn 发表于 Dev

亚历山大·高利斯

尝试执行以下脚本时出现“列定义不明确”错误：

INSERT INTO experience (id_, company, start_, end_, usr, position_) 
WITH exp_ AS (
    SELECT 0, 0, DATE'2013-04-30', NULL, 11, 'CEO' FROM DUAL UNION ALL
    SELECT 1, 0, DATE'2011-12-11', DATE'2013-04-30', 9, 'Administrator' FROM DUAL UNION ALL
    SELECT 2, 0, DATE'2013-04-30', DATE'2015-10-27', 6, 'Manager' FROM DUAL UNION ALL
    SELECT 3, 1, DATE'2014-11-04', NULL, 10, 'CEO' FROM DUAL UNION ALL
    SELECT 4, 1, DATE'2017-06-13', NULL, 13, 'CTO' FROM DUAL UNION ALL
    SELECT 5, 1, DATE'2010-04-22', DATE'2018-10-22', 1, 'Manager' FROM DUAL UNION ALL
    SELECT 6, 1, DATE'2019-08-26', NULL, 2, 'Trainer' FROM DUAL UNION ALL
    SELECT 7, 1, DATE'2006-02-27', DATE'2016-11-30', 6, 'Video Creator' FROM DUAL UNION ALL
    SELECT 8, 2, DATE'2012-04-30', DATE'2013-11-24', 5, 'CEO' FROM DUAL UNION ALL
    SELECT 9, 2, DATE'2013-01-10', NULL, 0, 'CEO' FROM DUAL UNION ALL
    SELECT 10, 2, DATE'2010-11-01', DATE'2017-01-01', 7, 'Video Creator' FROM DUAL UNION ALL
    SELECT 11, 2, DATE'2010-10-03', DATE'2018-03-01', 8, 'Commentator' FROM DUAL
)
SELECT * FROM exp_;

我在这里不使用任何JOIN，并且错误描述指出，这是由于试图将具有重叠列名的两个表联接在一起引起的。

那么，为什么要在这里买它以及如何解决？

福帕斯

仅当您删除CTE并直接使用插入行时，此语法才有效UNION ALL：

INSERT INTO experience (id_, company, start_, end_, usr, position_)
    SELECT 0, 0, DATE'2013-04-30', NULL, 11, 'CEO' FROM DUAL UNION ALL
    SELECT 1, 0, DATE'2011-12-11', DATE'2013-04-30', 9, 'Administrator' FROM DUAL UNION ALL
    SELECT 2, 0, DATE'2013-04-30', DATE'2015-10-27', 6, 'Manager' FROM DUAL UNION ALL
    SELECT 3, 1, DATE'2014-11-04', NULL, 10, 'CEO' FROM DUAL UNION ALL
    SELECT 4, 1, DATE'2017-06-13', NULL, 13, 'CTO' FROM DUAL UNION ALL
    SELECT 5, 1, DATE'2010-04-22', DATE'2018-10-22', 1, 'Manager' FROM DUAL UNION ALL
    SELECT 6, 1, DATE'2019-08-26', NULL, 2, 'Trainer' FROM DUAL UNION ALL
    SELECT 7, 1, DATE'2006-02-27', DATE'2016-11-30', 6, 'Video Creator' FROM DUAL UNION ALL
    SELECT 8, 2, DATE'2012-04-30', DATE'2013-11-24', 5, 'CEO' FROM DUAL UNION ALL
    SELECT 9, 2, DATE'2013-01-10', NULL, 0, 'CEO' FROM DUAL UNION ALL
    SELECT 10, 2, DATE'2010-11-01', DATE'2017-01-01', 7, 'Video Creator' FROM DUAL UNION ALL
    SELECT 11, 2, DATE'2010-10-03', DATE'2018-03-01', 8, 'Commentator' FROM DUAL

参见演示。

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。