MySQL视图:在另一个计算字段中引用一个计算字段(按名称) [英] MySQL Views: Referencing one calculated field (by name) in another calculated field
问题描述
如何定义一个具有两个计算字段的视图,例如...
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.
谢谢!
推荐答案
由于视图中不允许使用子查询,因此您需要通过创建多个视图来模拟它们.
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;
根据 MySQL文档视图具有对不能在FROM子句中包含子查询的限制.要解决此限制并将此查询转换为视图,请将其分为3个视图(每个子查询一个),最后一个给出所需的字段组合:
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;
这篇关于MySQL视图:在另一个计算字段中引用一个计算字段(按名称)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!