SQL:在同一select语句中的函数中使用列的别名

SELECT 
  Result/3 AS V, 
  MIN(Result/3) as Min, 
  MAX(Result/3) as Max
FROM
(
  SELECT (a+b+c) AS Result from Table
) AS outerTable

甚至

SELECT 
  Result AS V, 
  MIN(Result) as Min, 
  MAX(Result) as Max
FROM
(
  SELECT (a+b+c)/3 AS Result from Table
) AS outerTable

(a+b+c)/3 在不止一个查询和SQL方言支持视图的情况下,您可能需要考虑使用视图。

以下代码可用于 MySQL 创建包含表的所有列的视图 MY_TABLE_NAME +包含值的附加列 .

CREATE ALGORITHM = UNDEFINED
SQL SECURITY DEFINER VIEW  `MY_TABLE_NAME_FULL_VIEW` AS
SELECT MY_TABLE_NAME.*, (a+b+c)/3 AS Average
   FROM  `MY_TABLE_NAME`;

以下代码可用于 MySQL数据库 创建仅包含列a、b和c的视图+包含该值的附加列 (a+b+c)/3

CREATE ALGORITHM = UNDEFINED
SQL SECURITY DEFINER VIEW  `MY_TABLE_NAME_LIMITED_VIEW` AS
SELECT a, b, c, (a+b+c)/3 AS Average
   FROM  `MY_TABLE_NAME`;

以下代码可用于 MySQL数据库 在MySQL中创建只包含 .

CREATE ALGORITHM = UNDEFINED
SQL SECURITY DEFINER VIEW  `MY_TABLE_NAME_AVERAGE_VIEW` AS
SELECT (a+b+c)/3 AS Average
   FROM  `MY_TABLE_NAME`;

创建视图后,在选择查询中使用视图而不是表。

MY_TABLE_NAME_AVERAGE_VIEW ,您可以替换 SELECT

SELECT Average AS V, MIN(Average) AS Min, MAX(Average) AS Max
FROM MY_TABLE_NAME_AVERAGE_VIEW;

假设您创建了视图 MY_TABLE_NAME_LIMITED_VIEW 选择 声明如下:

SELECT a, b, c, Average AS V, MIN(Average) AS Min, MAX(Average) AS Max
FROM MY_TABLE_NAME_LIMITED_VIEW;