mysql - 当我使用另一个表的 SUM 时无法解析列

Question

当我想使用Where子句时total_debtor_price，total_debtor_price我收到了以下错误消息：

无法解析列total_debtor_price（或 total_creditor_price）；

我的查询在没有where子句的情况下工作正常。

DROP TEMPORARY TABLE IF EXISTS tmp_AccountingDocument_datatable;

CREATE TEMPORARY TABLE tmp_AccountingDocument_datatable
SELECT 
  TAD.*,
  (
    SELECT SUM(TADD.debtor_price) AS total_debtor_price 
    FROM Vw_AccountingDocumentDetail TADD 
    WHERE TADD.accounting_document_id = TAD.id
  ) total_debtor_price,
  (
    SELECT SUM(TADD.creditor_price) AS total_creditor_price 
    FROM Vw_AccountingDocumentDetail TADD 
    WHERE TADD.accounting_document_id = TAD.id
  ) total_creditor_price
FROM Tb_Accounting_Documents TAD
WHERE IF(
  NOT ISNULL(_Filter_Price_Status),
  CASE
    WHEN _Filter_Price_Status = 'smaller' 
      THEN (
        total_debtor_price <= _Filter_Price OR 
        total_creditor_price <= _Filter_Price
      )
    WHEN _Filter_Price_Status = 'equal' 
      THEN (
        total_debtor_price = _Filter_Price OR 
        total_creditor_price = _Filter_Price
      )
    WHEN _Filter_Price_Status = 'bigger' 
      THEN (
        total_debtor_price >= _Filter_Price OR 
        total_creditor_price >= _Filter_Price
      )
  END,
  TRUE
)

标签： mysqlsql

Answer 1

您不能在 where 子句上使用列别名，但您可以将主查询放入子查询中，以便将 where 子句与列别名一起使用

 SELECT * FROM (
    SELECT TAD.*,(SELECT SUM(TADD.debtor_price) as total_debtor_price FROM Vw_AccountingDocumentDetail TADD WHERE TADD.accounting_document_id = TAD.id) total_debtor_price,
           (SELECT SUM(TADD.creditor_price) as total_creditor_price FROM Vw_AccountingDocumentDetail TADD WHERE TADD.accounting_document_id = TAD.id) total_creditor_price
        FROM Tb_Accounting_Documents TAD
  ) TMP
    WHERE
    IF(NOT isnull(_Filter_Price_Status),
             CASE
                 WHEN _Filter_Price_Status = 'smaller' THEN (total_debtor_price <= _Filter_Price OR total_creditor_price <= _Filter_Price)
                 WHEN _Filter_Price_Status = 'equal' THEN (total_debtor_price = _Filter_Price OR total_creditor_price = _Filter_Price)
                 WHEN _Filter_Price_Status = 'bigger' THEN (total_debtor_price >= _Filter_Price OR total_creditor_price >= _Filter_Price)
                 END, TRUE)