mysql - 联接表和过滤器并采用聚合函数sql访问

是否可以连接两个表，过滤数据并在MS访问中采用聚合功能？我有两张桌子

Inv_details:invnum,itmcde,qnt,price
Inv_summary:invnum,date,total

我需要加入此表并用日期范围过滤它们，并按itmcde获得qnt和价格顺序的总和。

我尝试过这样的事情。

SELECT inv.itmcde,SUM(inv.qnt),SUM(inv.price) FROM (SELECT inv_s.invnum,inv_s.date,inv_d.itmcde,inv_d.total FROM Inv_summary as inv_s INNER JOIN Invoice_details as inv_d ON inv_d.invnum =inv_s.invNum WHERE inv_s.date BETWEEN  #3/4/2013# And #5/16/2013# ) as inv ORDER BY inv.itmcde

但这是行不通的。

最佳答案

尝试

SELECT d.itmcde,
       SUM(d.qnt) AS qnt,
       SUM(d.price) AS price
  FROM Inv_summary s INNER JOIN Inv_details d
    ON d.invnum = s.invnum
 WHERE s.date BETWEEN #3/4/2013# AND #5/16/2013#
 GROUP BY d.itmcde
 ORDER BY d.itmcde

SQLFiddle尽管此摆弄是针对SQL Server的，但相同的一般规则也应适用于MS Access

关于mysql - 联接表和过滤器并采用聚合函数sql访问，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/16661949/