MySQL 视图：在另一个计算字段中引用一个计算字段（按名称）

例如，如何定义具有两个计算字段的视图......

 ('TableName'.'BlueSquares' + 'TableName'.'RedSquares') AS TotalSquares, ('TableName'.'BlueCirles' + 'TableName'.'RedCircles') AS TotalCircles

...并创建基于前两个计算字段的第三个计算字段，如...

 ('ViewName'.'TotalSquares' + 'ViewName'.'TotalCircles') AS TotalShapes

...?

当我按名称引用前两个计算字段时，我收到一条消息，指出这些字段未知。

Thanks!

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

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

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 文档 http://dev.mysql.com/doc/refman/5.0/en/create-view.html视图有不能在 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;