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

How can I define a view that has two calculated fields, for instance...

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

... and create a third calculated field that's based on the first two calculated fields, as in...

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

...?

When I reference the first two calculated fields by name, I get a message that the fields are unknown.

Thanks!

解决方案

Since subqueries are not allowed in views, you will need to simulate them by creating multiple views.

For example, this query will solve your issue if directly executed:

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;

According to the MySQL documentation views have the restriction on not being able to contain subqueries in the FROM clause. To work around this limitation and turn this query into a view, break it up into 3 views (one for each subquery) with the last giving the desired combination of fields:

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;