引用外部查询MySQL

Question

我尝试使用"总计"；在外面的哪里，我得到了错误"；未知"；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