sql - 在没有任何加入代码的情况下获得“列不明确定义”
问题描述
尝试执行以下脚本时出现“列不明确定义”错误:
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
请参阅演示。
推荐阅读
- javascript - 将 if/else 逻辑添加到 JavaScript 过滤器
- vue.js - 使用 vue 中的元素 UI 具有一个选择和一个提交按钮的内联表单?
- java - 测试 Spring Boot 应用程序的最佳方法是什么?有服务器参与还是通过创建模拟环境?
- c++ - 哪个问题阻止 ShowWindow 工作?
- android - Android Studio 3.5 无法编译数据绑定
- solr - Solr查询返回部分归档内容
- angular - 来自浏览器的延迟引导 Angular 8 模块
- excel - 列值 2 x 2
- javascript - React - 删除请求后组件不会重新渲染
- java - jOOQ & PostgreSQL:将从复杂 jsonb 中提取的嵌套 json 对象映射到自定义类型