我有一个空表DatabaseStudents2,由三列组成:StudentName、StudentEmail和ClassName。我想插入到这个表中的数据都存储在三个单独的单列表中。我使用以下插入语句:
insert into DatabaseStudents2(StudentName, StudentEmail, ClassName)
select names from StudentNames
union
select email from studentEmail
union
select clname from ClassName;
然而,当我尝试执行此操作时,我会收到"列计数与第1行的值计数不匹配"错误。我不确定为什么,因为我有三个列名和三个select语句,所以我看不出我的列计数与为什么值计数不匹配。请告知。非常感谢。
我需要更多信息来回答这个问题。您可以分享StudentNames、studentmail和ClassName表中的定义(DDL(吗?
不管怎样,我做了一个样品给你看:
create table DatabaseStudents2 (StudentName varchar(25),
StudentEmail varchar(50), ClassName varchar(50));
create table StudentNames (id int, names varchar(25));
create table StudentEmail (id int, id_student int, email varchar(50));
create table ClassName (id int, id_student int, clname varchar(50));
表StudentEmail和ClassName有一个列"id_student"来标识学生,它用于加入。
让我们插入几行。学生:
insert into StudentNames values (1, "John");
insert into StudentNames values (2, "Maria");
现在电子邮件:
insert into StudentEmail values (1, 1, 'john@email.com');
insert into StudentEmail values (2, 1, 'john.secondmail@email.com');
insert into StudentEmail values (3, 2, 'maria@email.com');
学生"约翰"有两封电子邮件。第一列标识电子邮件,第二列标识学生。
现在的类别:
insert into ClassName values (1, 1, 'First Class');
insert into ClassName values (2, 2, 'Second Class');
选择这3个具有联接的表:
select s.names, e.email, c.clname from StudentNames s
inner join StudentEmail e on s.id = e.id_student
inner join ClassName c on s.id = c.id_student;
+-------+---------------------------+--------------+
| names | email | clname |
+-------+---------------------------+--------------+
| John | john@email.com | First Class |
| John | john.secondmail@email.com | First Class |
| Maria | maria@email.com | Second Class |
+-------+---------------------------+--------------+
最后,插入你想要的:
insert into DatabaseStudents2 (StudentName, StudentEmail, ClassName)
select s.names, e.email, c.clname from StudentNames s
inner join StudentEmail e on s.id = e.id_student
inner join ClassName c on s.id = c.id_student;
如果我帮助过你,请告诉我。