引用外部查询MySQL

Question

我尝试在outer where中使用"Total“，但得到错误"Unknown "AL.Total"，如何修复它?以下是我的查询：

SELECT 
AL.CODE,
AL.NAME,
SUM(AL.BEGINING+AL.MUTATION) AS TOTAL
from
(SELECT
    ACC.CODE,
    ACC.NAME,
    ACC.BEGINING,
    SUM(JOURNAL.DEBIT-JOURNAL.KREDIT) AS MUTATION
FROM 
    G_JOURNAL INNER JOIN P ON Category.Category= G_JOURNAL.Category
    INNER JOIN JOURNAL ON G_JOURNAL.NOINDEX = JOURNAL.NOINDEX_JOURNAL
    INNER JOIN ACCOUNT ON JOURNAL.CODE = ACC.CODE  
    INNER JOIN SUBCLASS ON SUBCLASS.NOSUBCLASS= ACC.NOSUBCLASS
    INNER JOIN CLASS ON SUBCLASS.NOSUBCLASS = CLASS.NOCLASS
WHERE  
        (MONTH(G_JOURNAL.DATE) <= '12' AND YEAR (G_JOURNAL.DATE<= '2020') 
    AND 
        SUBCLASS.NOSUBCLASS=120
 GROUP BY ACC.NAME) AL
 WHERE AL.TOTAL >0
 GROUP BY AL.NAME
 ORDER BY AL.CODE ASC

Answer 1

尝试使用HAVING TOTAL >0而不是WHERE AL.TOTAL >0，因为TOTAL不在AL临时表中

SELECT 
AL.CODE,
AL.NAME,
SUM(AL.BEGINING+AL.MUTATION) AS TOTAL
from
(SELECT
    ACC.CODE,
    ACC.NAME,
    ACC.BEGINING,
    SUM(JOURNAL.DEBIT-JOURNAL.KREDIT) AS MUTATION
FROM 
    G_JOURNAL INNER JOIN P ON Category.Category= G_JOURNAL.Category
    INNER JOIN JOURNAL ON G_JOURNAL.NOINDEX = JOURNAL.NOINDEX_JOURNAL
    INNER JOIN ACCOUNT ON JOURNAL.CODE = ACC.CODE  
    INNER JOIN SUBCLASS ON SUBCLASS.NOSUBCLASS= ACC.NOSUBCLASS
    INNER JOIN CLASS ON SUBCLASS.NOSUBCLASS = CLASS.NOCLASS
WHERE  
        (MONTH(G_JOURNAL.DATE) <= '12' AND YEAR (G_JOURNAL.DATE<= '2020') 
    AND 
        SUBCLASS.NOSUBCLASS=120
 GROUP BY ACC.NAME) AL
 GROUP BY AL.NAME
 HAVING TOTAL >0
 ORDER BY AL.CODE ASC