mysql - 创建 MySQL View 问题

Question

作为大学项目的一部分，我正在设计一款星球大战游戏。

我需要做的一部分是根据某些属性来削减角色。我最初使用大量 MySQL 查询来获取我需要的内容，但我建议通过使用 View ，我可以让自己变得更容易。但是，我遇到了一个问题。

例如，下面是原始 MySQL 查询，用于显示所有不使用光剑的角色:

-- Query where person does not use a lightsaber --
select * from person, weapon, person_weapon
WHERE person.id = person_weapon.person_id 
AND person_weapon.weapon_id = weapon.id and weapon.name != "Lightsaber"
AND person.name NOT IN (select person.name from person, weapon, person_weapon 
WHERE person.id = person_weapon.person_id 
AND person_weapon.weapon_id = weapon.id and weapon.name = "Lightsaber");

这将返回相关字符并消除其余字符。我想把它放到一个可以再次查询的 View 中。所以我创建了这个:

CREATE VIEW noLightsaber AS 
SELECT alias, class, force_wielder, gender, image, name AS p_name, pilot, quote, species,
colour_id, how_many, name AS w_name, person_id, weapon_id from person, weapon, person_weapon
WHERE person.id = person_weapon.person_id 
AND person_weapon.weapon_id = weapon.id and weapon.name != "Lightsaber"
AND person.name NOT IN (select person.name from person, weapon, person_weapon 
WHERE person.id = person_weapon.person_id 
AND person_weapon.weapon_id = weapon.id and weapon.name = "Lightsaber");

建议使用 name AS Something_name，因为人和武器都有名称。然而，它出错了。我收到 #1052 错误 - 字段列表中的列“名称”不明确。显然这是由于名称出现在两个表中，但我不确定如何进行此操作。

Answer 1

列名的歧义必须使用该列相关的表名来解决；在您的情况下，您应该使用 person.name AS p_name。

CREATE VIEW noLightsaber AS 
SELECT alias
  , class
  , force_wielder
  , gender
  , image
  , person.name AS p_name
  , pilot
  , quote
  , species
  , colour_id
  , how_many
  , person_weapon.name AS w_name
  , person_id
  , weapon_id 
from person
INNER JOIN person_weapon ON person.id = person_weapon.person_id 
INNER JOIN weapon ON person_weapon.weapon_id = weapon.id and weapon.name != "Lightsaber"
WHERE person.name NOT IN (
  select person.name from person, weapon, person_weapon 
  WHERE person.id = person_weapon.person_id 
  AND person_weapon.weapon_id = weapon.id and weapon.name = "Lightsaber"
);

并且您应该使用显式连接语法。