SQL在其中的子句中未识别列别名

Question

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;

我收到此错误：

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 对结果进行降序排序。

Answer 1

可以在查询选择列表中使用一个别名为列提供不同的名称。您可以通过，订购或拥有的组中使用别名
参考列的条款。

标准SQL在某个子句中删除对列别名的引用。强加了此限制，因为当where子句是
评估，列值可能尚未确定。

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

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>

列允许

• <
• oter： group in：
• 组。 strong>具有，

您可以在以下情况下参考列中的列别名：

1. subquery
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>

Answer 2

从 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;

Answer 3

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

：

查询将在当时首先检查运行时的列名称“ item_total”在表“ order_items”中找不到，因为它是作为别名的给出的不存储在任何地方，而是在所需的输出中分配该列仅

替代：

如果您要使用该类型使用sub询问，则性能不好，但它是替代方式之一

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;