如何解决错误1060:使用“视图"重复列名->建立检视 [英] How to solve ERROR 1060: Duplicate column name using Views -> Create View

问题描述

我正在尝试通过联接Sakila数据库中的某些表(在MySQL中)来创建视图( https://dev.mysql.com/doc/sakila/en/)，即我想加入付款，员工和客户，并显示客户的姓名，员工的姓名，付款ID和金额. 我右键单击视图"，然后单击创建表". 我认为存在此错误是因为在职员表和客户表中都有名为first_name和last_name的列.我该如何解决这个问题?

I am trying to create a view from joining some tables (in MySQL) from Sakila database (https://dev.mysql.com/doc/sakila/en/), namely I want to join payment, staff and customer and display the customer's name, the staff's name, the payment id and amount. I right clicked on Views and then Create Table. I think this error exists because of the fact that in the staff table and in the customer table there are both columns named first_name and last_name. How could I solve this problem?

我的代码:

CREATE VIEW `payment` AS
SELECT payment.payment_id, customer.first_name, customer.last_name, 
staff.first_name, staff.last_name, payment.amount
FROM payment INNER JOIN customer ON payment.customer_ID = customer.customer_ID
INNER JOIN staff ON payment.staff_ID = staff.staff_ID

错误消息: 错误1060:重复的列名"first_name"

The error message: ERROR 1060: Duplicate column name 'first_name'

如果我像一些回答者所建议的那样使用别名进行尝试，则会收到错误ERROR 1347:'sakila.payment'不是VIEW.

If I try it with aliases, as some of the answerers suggested, I get the error ERROR 1347: 'sakila.payment' is not VIEW.

完全相同的代码就像SQL文件中的超级按钮一样工作，当我运行它时，它将创建我需要的表.这两种方法有什么区别?我应该使用哪一个?为什么它不能与视图"->创建视图"选项一起使用?

The exact same code works like a charm in SQL File, when I run it, it creates the table I need. What is the difference between these two methods? Which one should I use? And why didn't it work with the Views -> Create View option?

谢谢.

推荐答案

使用别名分配名称:

CREATE VIEW v_payment AS
    SELECT p.payment_id, c.first_name as customer_first_name, c.last_name as customer_last_name,
           s.first_name as staff_first_name, s.last_name as staff_last_name,
           p.amount
    FROM payment p INNER JOIN
         customer c
         ON p.customer_ID = c.customer_ID INNER JOIN
         staff s
         ON p.staff_ID = s.staff_ID;

first_name和last_name在select列表中出现两次.以上说明了该名称是用于客户还是用于员工.

first_name and last_name appear twice in your select list. The above clarifies whether the name is for a customer or staff.

这篇关于如何解决错误1060:使用“视图"重复列名->建立检视的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！