mysql错误: an alias was previously found

Question

我在mysql查询中使用这些代码

    select c1.date,sum(SELECT IFNULL(cx.purchase-cx.sold,0))+MAX(p.openqty) as opening,c1.purchase,c1.sold,
    sum(SELECT IFNULL(cx.purchase-cx.sold,0))+MAX(p.openqty) + c1.purchase-c1.sold as closing 
from 
    (select open_qty as openqty from stock ) p
left join
(select IFNULL(a.date,b.date) as date,SELECT IFNULL(a.qty,0) as purchase,SELECT IFNULL(b.qty,0) as sold from arrival a FULL JOIN pouring b ON a.date = b.date order by 1) c1 
on c1.purchase +p.openqty>0
left join
 (select IFNULL(a.date,b.date) as date,SELECT IFNULL(a.qty,0) as purchase,SELECT IFNULL(b.qty,0) as sold from arrival a FULL JOIN pouring b ON a.date = b.date order by 1) cx 
on c1.date>cx.date
 group by c1.date,c1.purchase,c1.sold

它说:

An alias was previously found. (near "purchase" at position 170)
An alias was previously found. (near "c1" at position 179)
An alias was previously found. (near "sold" at position 182)
An alias was previously found. (near "closing" at position 190)

MySQL 说:文档

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use

near 'SELECT IFNULL(cx.purchase-cx.sold,0))+MAX(p.openqty) asopening,c1.purchase,c1.' at line 1

请帮忙如何克服？

Answer 1

首先，子查询需要有自己的括号。其次，子查询不能作为聚合函数的参数。第三，你根本不需要子查询。所以:

select c1.date,
       (COALESCE(sum(cx.purchase-cx.sold), 0) + MAX(p.openqty)
       ) as opening, c1.purchase, c1.sold,
       (COALESCE(sum(cx.purchase-cx.sold), 0) + MAX(p.openqty) + c1.purchase - c1.sold
       ) as closing