sql - MySQL View : Referencing one calculated field (by name) in another calculated field

Question

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

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

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

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

...?

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

谢谢!

Answer 1

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

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

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 View 有不能在 FROM 子句中包含子查询的限制。要解决此限制并将此查询转换为 View ，请将其分成 3 个 View (每个子查询一个)，最后一个提供所需的字段组合:

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;