SQL无法识别where子句中的列别名 [英] SQL not recognizing column alias in where clause

问题描述

我只是SQL的初学者，但是遇到了这个令人讨厌的错误. SQL与此脚本的WHERE子句有关:

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:

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;

我收到此错误:

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:

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

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

推荐答案

可以在查询选择列表中使用别名来为列指定其他名称.您可以在GROUP BY，ORDER BY或HAVING中使用别名 条款以引用该列.

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.

标准SQL不允许在WHERE子句中引用列别名.之所以施加此限制，是因为当WHERE子句是 进行评估，则可能尚未确定列值.

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.

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

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

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

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

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

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

例如，

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>

这篇关于SQL无法识别where子句中的列别名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！