mysql - 重复的列名 MySQL

Question

我不应该有这个错误。这是代码

drop database if exists myDB;
CREATE DATABASE IF NOT EXISTS myDB;
USE myDB;

CREATE TABLE teacher(
tId int auto_increment primary key,
fname varchar(50),
lname varchar(50),
sId varchar(50)
);


CREATE TABLE student(
sId varchar(50) not null primary key,
fname varchar(50),
lname varchar(50)
);


ALTER TABLE teacher
    ADD foreign key (sId) references student(sId);
 
 insert into student values ('123', 'a', 'aa');
 insert into student values ('456', 'b', 'bb');
 insert into teacher (fname, lname, sId) values ('t', 'tt', '123');
 insert into teacher (fname, lname, sId) values ('x', 'xx', '456');
 
 
 
 create or replace view showTeachersAndStudents as
    select teacher.fname, teacher.lname, student.sId, student.fname, student.lname 
                from teacher join student on teacher.sId = student.sId;

错误:错误代码:1060。列名称“fname”重复

Answer 1

您的 View 尝试返回 fname 和 lname 两次。

为您的 View 添加别名:

create or replace view showTeachersAndStudents as
    select teacher.fname fname_teacher, 
           teacher.lname lnamet_teacher, 
           student.sId, 
           student.fname fname_student, 
           student.lname lname_student
    from teacher 
    join student on teacher.sId = student.sId;