mysql - 在 mysql 中更新查询以获取新列

Question

亲们

我有如下表格

我们假设在 countryname 列之后添加一个新列，即 parent_country，结果应如下所示

id parentid  countryname                Parent_country

39  0        Barbados                     Barbados
40  39       Barbados mobile              Barbados
41  39       Mobile Digicel               Barbados
42  0        Anguilla                     Anguilla 
43  42       Anguilla Mobile              Anguilla 
44  42       hello Mobile Digicel         Anguilla 

谢谢

Answer 1

我认为您不应该添加一列。你正在打破规范化的基本规则之一。这是一个查询，可以为您提供所需的结果。

SELECT tbl.id as id
  , tbl.parentid as parentid
  , tbl.countryname as countryname
  , COALESCE(parent.countryname, tbl.countryname) as Parent_country
FROM tbl
LEFT JOIN tbl AS parent
ON tbl.parentid = parent.id

而且很容易变成一个 View (虽然不可更新)可以模仿表格以供阅读

CREATE VIEW tbl_trick_business_team AS
SELECT tbl.id as id
  , tbl.parentid as parentid
  , tbl.countryname as countryname
  , COALESCE(parent.countryname, tbl.countryname) as Parent_country
FROM tbl
LEFT JOIN tbl AS parent
ON tbl.parentid = parent.id