运行 SQL 查询时出现“ORA-00913：值太多”错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8353165/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
"ORA-00913: too many values" error when I run SQL query
提问by Coffee
I'm getting a "too many values" error and I'm not sure how to approach it. The problem starts at the first nested SELECT
我收到“值太多”错误，我不知道如何处理。问题从第一个嵌套的 SELECT 开始
SELECT p.name, p.phone_number
FROM person p WHERE
(SELECT ks.title, ks.ks_code
FROM required_skills rs JOIN knowledge_skills ks USING (ks_code)
WHERE rs.pos_code = 'CS1')
IN (SELECT ks.title, ks.ks_code
FROM courses_taken ct JOIN course_learning_units clu
USING (c_code) JOIN learning_unit_skills lus USING (lu_code)
JOIN knowledge_skills ks USING (ks_code))
I looked up info about this errorand I believe it's because the subquery in the WHERE returns too many columns. The table data is below. But how can I whittle that down? I appreciate any pointers, Thanks
我查找了有关此错误的信息，我相信这是因为 WHERE 中的子查询返回了太多列。表数据如下。但我怎样才能减少它呢？我感谢任何指点，谢谢
required_skills
required_skills
POS_CODE VARCHAR2(10 BYTE) Yes 1
KS_CODE VARCHAR2(10 BYTE) Yes 2
learning_unit_skills
learning_unit_skills
LU_CODE VARCHAR2(10 BYTE) Yes 1
KS_CODE VARCHAR2(10 BYTE) Yes 2
person
人
PER_ID NUMBER(38,0) No 1
NAME VARCHAR2(255 BYTE) Yes 2
STREET VARCHAR2(255 BYTE) Yes 3
CITY VARCHAR2(60 BYTE) Yes 4
ZIP_CODE VARCHAR2(60 BYTE) Yes 5
PHONE_NUMBER VARCHAR2(255 BYTE) Yes 6
GENDER VARCHAR2(6 BYTE) Yes 7
course learning units
课程学习单元
C_CODE VARCHAR2(10 BYTE) Yes 1
LU_CODE VARCHAR2(10 BYTE) Yes
courses taken
参加的课程
C_CODE VARCHAR2(10 BYTE) Yes 1
PER_ID NUMBER(38,0) Yes 2
回答by ypercube??
A quick rewrite:
快速重写：
SELECT p.name, p.phone_number
FROM person p WHERE EXISTS
(SELECT *
FROM required_skills rs JOIN knowledge_skills ks USING (ks_code)
WHERE rs.pos_code = 'CS1'
AND (ks.title, ks.ks_code)
IN (SELECT ks.title, ks.ks_code
FROM courses_taken ct JOIN course_learning_units clu
USING (c_code) JOIN learning_unit_skills lus USING (lu_code)
JOIN knowledge_skills ks USING (ks_code))
)
or (the joins will probably need corrections):
或（连接可能需要更正）：
SELECT p.name, p.phone_number
FROM person p WHERE EXISTS
(SELECT *
FROM required_skills rs JOIN knowledge_skills ks USING (ks_code)
JOIN ( courses_taken ct JOIN course_learning_units clu
USING (c_code) JOIN learning_unit_skills lus USING (lu_code)
) USING (ks_code)
WHERE rs.pos_code = 'CS1'
)
Problem is that in your code there is no relation to table person. I guess you should have one in the subqueries - making them correlated.
问题是在您的代码中与 table 没有关系person。我想你应该在子查询中有一个 - 使它们相关联。
回答by Rashmi Kant Shrivastwa
use "exists" at the place of "in" may help you
在“in”的地方使用“exists”可能会帮助你
回答by sandeep chouhan
CREATE TABLE EMP(EMPNO NUMBER(4) NOT NULL,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7, 2),
COMM NUMBER(7, 2),
DEPTNO NUMBER(2));
INSERT INTO EMP VALUES
(7369, 'SMITH', 'CLERK', 7902,
TO_DATE('17-DEC-1980', 'DD-MON-YYYY'), 800, NULL, 20);
while inserting values it is giving error to many values
在插入值时，它会给许多值带来错误
I've a table script of this table when I insert it it gives lot of errors while on my desktop it works fine
我有这个表的表脚本，当我插入它时它会出现很多错误，而在我的桌面上它工作正常
