MySQL视图:在另一个计算字段中引用一个计算字段(按名称)

由于视图中不允许使用子查询,因此需要通过创建多个视图来模拟子查询。

例如,如果直接执行,此查询将解决您的问题:

SELECT 
    TotalCircles + TotalSquares AS TotalShapes
FROM
    (SELECT 
        BlueCirles + RedCircles AS TotalCircles,
        BlueSquares + RedSquares AS TotalSquares
    FROM
        (SELECT
            2 AS BlueCirles,
            3 AS RedCircles,
            4 AS BlueSquares,
            5 AS RedSquares
        ) AS shapes
    ) as totals;

根据 MySQL documentation 视图对不能在FROM子句中包含子查询有限制。要解决此限制并将此查询转换为视图,请将其分为3个视图(每个子查询一个),最后一个视图提供所需的字段组合:

CREATE VIEW shapes AS
SELECT
    2 AS BlueCirles,
    3 AS RedCircles,
    4 AS BlueSquares,
    5 AS RedSquares;

CREATE VIEW totals AS
SELECT 
    BlueCirles + RedCircles AS TotalCircles,
    BlueSquares + RedSquares AS TotalSquares
FROM
    shapes;

CREATE VIEW result AS
SELECT 
    TotalCircles + TotalSquares AS TotalShapes
FROM
    totals;

SELECT * FROM result;

…创建基于前两个计算字段的第三个计算字段…

正如您所发现的,不能在同一个select中引用计算列。您的选择是:

复制逻辑

MySQL不支持视图中的子查询,原因很奇怪:

CREATE OR REPLACE VIEW your_view AS
SELECT t.BlueSquares + t.RedSquares AS TotalSquares, 
       t.BlueCirles + t.RedCircles AS TotalCircles,
       t.BlueSquares + t.RedSquares + t.BlueCirles + t.RedCircles AS TotalShapes
  FROM TABLE t

将视图分层

最不推荐的选项…

 CREATE OR REPLACE VIEW initial_view AS
    SELECT t.BlueSquares + t.RedSquares AS TotalSquares, 
           t.BlueCirles + t.RedCircles AS TotalCircles,
      FROM TABLE

 CREATE OR REPLACE VIEW layered_view AS
    SELECT iv.TotalSquares + iv.TotalCircles AS TotalShapes
      FROM initial_view iv

不幸的是,你试图做的实际上是不可能的方式,你正在做。通常,MySQL视图可以作为脚本选择查看。视图的结果不存储在数据库中,而是在引用视图时计算出来的。

大多数情况下,当您从视图中选择时,MySQL使用合并算法。这意味着它合并视图的定义和引用视图的select语句,以创建实际运行的最终select。因此,您不能真正拥有引用自身的视图。

这里的选项是:(1)定义引用第一个视图的第二个视图以创建求和,或(2)如下所示写出求和:

('TableName'.'BlueSquares' + 'TableName'.'RedSquares' + 'TableName'.'BlueCirles' + 'TableName'.'RedCircles') AS TotalShapes