SQL 无法识别 where 子句中的列别名

SQL not recognizing column alias in where clause

提问by Somus

I am only a beginner in SQL, but I've come across this annoying error. SQL is having an issue with the WHERE clause of this script:

我只是 SQL 的初学者，但我遇到了这个烦人的错误。SQL 遇到此脚本的 WHERE 子句的问题：

SELECT
  ITEM_ID, ITEM_PRICE, DISCOUNT_AMOUNT, QUANTITY, 
  (ITEM_PRICE*QUANTITY) AS price_total, 
  (DISCOUNT_AMOUNT*QUANTITY) AS discount_total, 
  ((ITEM_PRICE-DISCOUNT_AMOUNT)*QUANTITY) AS item_total
FROM ORDER_ITEMS
WHERE item_total > 500
ORDER BY item_total;

I am receiving this error:

我收到此错误：

Error starting at line : 1 in command -
SELECT 
  ITEM_ID, ITEM_PRICE, DISCOUNT_AMOUNT, QUANTITY,  
  (ITEM_PRICE*QUANTITY) AS price_total,  
  (DISCOUNT_AMOUNT*QUANTITY) AS discount_total,  
  ((ITEM_PRICE-DISCOUNT_AMOUNT)*QUANTITY) AS item_total 
FROM ORDER_ITEMS 
WHERE item_total > 500 
ORDER BY item_total DESC;
Error at Command Line : 7 Column : 7
Error report -
SQL Error: ORA-00904: "ITEM_TOTAL": invalid identifier
00904. 00000 -  "%s: invalid identifier"
*Cause:    
*Action:

I have no idea why it has no issue with price_total nor discount_total, but is reporting item_total as invalid. I am trying to first select only the items which have a total greater than 500 when the discount amount is subtracted and it is multiplied by the quantity. Then, I need to sort the results in descending order by item_total

我不知道为什么它对 price_total 和 discount_total 都没有问题，但将 item_total 报告为无效。当减去折扣金额并乘以数量时，我试图首先只选择总数量大于 500 的项目。然后，我需要按 item_total 按降序对结果进行排序

回答by Lalit Kumar B

An alias can be used in a query select list to give a column a different name. You can use the alias in GROUP BY, ORDER BY, or HAVING clauses to refer to the column.

Standard SQL disallows references to column aliases in a WHERE clause. This restriction is imposed because when the WHERE clause is evaluated, the column value may not yet have been determined.

可以在查询选择列表中使用别名来为列指定不同的名称。您可以在 GROUP BY、ORDER BY 或 HAVING 子句中使用别名来引用该列。

标准 SQL 不允许在 WHERE 子句中引用列别名。强加此限制是因为在评估 WHERE 子句时，可能尚未确定列值。

So, the following query is illegal:

因此，以下查询是非法的：

SQL> SELECT empno AS employee, deptno AS department, sal AS salary
  2  FROM emp
  3  WHERE employee = 7369;
WHERE employee = 7369
      *
ERROR at line 3:
ORA-00904: "EMPLOYEE": invalid identifier


SQL>

The column alias is allowed in:

列别名允许用于：

• GROUP BY
• ORDER BY
• HAVING

• 通过...分组
• 订购者
• 有

You could refer to the column alias in WHERE clause in the following cases:

在以下情况下，您可以在 WHERE 子句中引用列别名：

1. Sub-query
2. Common Table Expression(CTE)

1. 子查询
2. 公用表表达式(CTE)

For example,

例如，

SQL> SELECT * FROM
  2  (
  3  SELECT empno AS employee, deptno AS department, sal AS salary
  4  FROM emp
  5  )
  6  WHERE employee = 7369;

  EMPLOYEE DEPARTMENT     SALARY
---------- ---------- ----------
      7369         20        800

SQL> WITH DATA AS(
  2  SELECT empno AS employee, deptno AS department, sal AS salary
  3  FROM emp
  4  )
  5  SELECT * FROM DATA
  6  WHERE employee = 7369;

  EMPLOYEE DEPARTMENT     SALARY
---------- ---------- ----------
      7369         20        800

SQL>

回答by koushik veldanda

You cannot use the column name which is used as alias one in the query

您不能使用在查询中用作别名的列名

Reason:

原因：

The query will first checks for runtime at that time the column name "item_total" is not found in the table "ORDER_ITEMS" because it was give as alias which is not stored in anywhere and you are assigning that column in desired output only

查询将首先检查运行时在表“ORDER_ITEMS”中找不到列名“item_total”，因为它是作为别名提供的，没有存储在任何地方，并且您只在所需的输出中分配该列

Alternate:

备用：

If you want to use that type go with sub queries it's performance is not good but it is one of the alternate way

如果你想使用这种类型的子查询，它的性能不好，但它是另一种方式

SELECT * FROM
 (SELECT
  ITEM_ID, ITEM_PRICE, DISCOUNT_AMOUNT, QUANTITY, 
  (ITEM_PRICE*QUANTITY) AS price_total, 
  (DISCOUNT_AMOUNT*QUANTITY) AS discount_total, 
  ((ITEM_PRICE-DISCOUNT_AMOUNT)*QUANTITY) AS item_total
 FROM ORDER_ITEMS) as  tbl
WHERE tbl.item_total > 500
ORDER BY tbl.item_total;

回答by Lukasz Szozda

Starting from Oracle 12c you could use CROSS APPLYto define expression and then you could refer to them in WHEREclause:

从 Oracle 12c 开始，您可以使用CROSS APPLY定义表达式，然后您可以在WHERE子句中引用它们：

SELECT
  o.ITEM_ID, o.ITEM_PRICE, o.DISCOUNT_AMOUNT, o.QUANTITY, 
  s.price_total, s.discount_total, s.item_total
FROM ORDER_ITEMS o
CROSS APPLY (SELECT ITEM_PRICE*QUANTITY AS price_total, 
                    DISCOUNT_AMOUNT*QUANTITY AS discount_total, 
                  (ITEM_PRICE-DISCOUNT_AMOUNT)*QUANTITY AS item_total FROM dual) s
WHERE s.item_total > 500
ORDER BY s.item_total;