mysql - MYSQL内部联接2个表和sum()

连接两个表后，无法获取sum（），一个是HEADER和DETAIL。对于下面的sql语句，它只给出0个结果。需要帮助：
我的桌子：
投资回报：

    Invno,  Invdate,   Ac_code
    100     2013-04-01  2
    101     2013-04-30  2

    INVDTLS:
    Invno,  Prod_desc, Amount
    100     Argon      155
    100     Argon      250
    101     Oxygen     322
    101     Oxygen     065

预期结果：
总计：405
MYSQL语句到内部连接和sum（）

SELECT
        a.Invno, a.Ac_code, a.Invdate, b.*
    FROM INVHDR a
    INNER JOIN (
        SELECT
                Invno, Prod_desc, SUM( Amount ) AS amts
            FROM INVDTLS
            WHERE Prod_desc='Argon'
        ) AS b ON a.Invno = b.Invno
    WHERE
        a.Ac_code='2'
        AND a.Invdate BETWEEN '2013-04-01'
        AND '2013-04-30'
    GROUP BY a.Ac_code

最佳答案

为什么你要写这么复杂的Query，试试这个：

SELECT sum(b.Amount)
FROM INVHDR a
INNER JOIN INVDTLS b
ON a.Invno = b.Invno
WHERE a.Ac_code='2'
AND a.Invdate BETWEEN '2013-04-01' AND '2013-04-30'
AND b.Prod_desc='Argon'
--Group by b.Prod_desc,a.Invno, a.Ac_code

这里是SQL Fiddle