mysql - mysql如何在另一个子查询中访问由子查询生成的临时表？

我试过这个：

    SELECT ProductId AS GmId, (
    SELECT SUM(Amount) FROM tmp WHERE ProductId=GmId AND (AssignDate BETWEEN '$Date1' AND '$Date2')
) AS sum0, (
    SELECT SUM(Amount) FROM tmp WHERE ProductId=GmId AND (AssignDate BETWEEN '$Date2' AND '$Date3')
) AS sum1 FROM (
    SELECT * FROM somerealtable
) tmp GROUP BY ProductId

但mysql显示错误消息table <database name>.tmp doesn't exist!
如何使这个工作？
编辑：sqlfiddle samplehere
EDIT2：更复杂的sqlfiddlesample。

最佳答案

不需要使用子查询。你可以用JOIN做同样的事情
试试这个：

SELECT a.ProductId AS GmId,
       SUM(IF(b.AssignDate BETWEEN '$Date1' AND '$Date2', b.Amount, 0)) AS sum0,
       SUM(IF(b.AssignDate BETWEEN '$Date2' AND '$Date3', b.Amount, 0)) AS sum1
FROM somerealtable a
LEFT JOIN tmp b ON a.ProductId=b.GmId
GROUP BY ProductId

关于mysql - mysql如何在另一个子查询中访问由子查询生成的临时表？，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/20992744/