SUM和按日期和物料分组

debugcn 发表于 Dev

大步向前

仍在学习SQL，请原谅我。我有3张桌子。物料表，material_req表和material_trans表。我想按材料分组，然后按年份分组。因此应为[物料，2019、2018、2017、2016，总计（总计为每种物料使用的总数量。

我试图将日期放置在select语句中，并按日期进行分组。但是返回的结果是很多相同的材料和很多的日期。我只需要一年。也许尝试同样的方法，只返回一年？

SELECT  material_req.Material
    --    , Material_Trans_Date   
      , SUM(-1 * material_trans.Quantity) AS 'TOTAL'
      ,Standard_Cost

    FROM
       Material_Req inner join Material_Trans 
    ON
        Material_Req.Material_Req = Material_Trans.Material_Req
        LEFt JOIN Material
    ON
        Material.Material = Material_Req.Material
    WHERE 
      material_trans.Material_Trans_Date between '20180101' AND GETDATE() 
    --   Material_Trans_Date between '20180101' AND '20181231'
    --   Material_Trans_Date between '20170101' AND '20171231'
    --   Material_Trans_Date between '20160101' AND '20161231'
    GROUP BY 
      material_req.Material   ,Standard_Cost 

    ORDER BY 
    Material_Req.Material, Standard_Cost

预期结果应按物料，2019、2018、2017、2016，Standard_Cost分组。“年份”列将包含该年每种材料的数量总和。

结果看起来像这样current_results

塞伯勒

如果您使用的是SQL Server，则可以尝试以下操作：

SELECT  material_req.Material
      , SUM(CASE WHEN DATEPART(YEAR, Material_Trans_Date) = '2019' THEN material_trans.Quantity ELSE 0 END) [2019 TOTAL] 
      , SUM(CASE WHEN DATEPART(YEAR, Material_Trans_Date) = '2018' THEN material_trans.Quantity ELSE 0 END) [2018 TOTAL]  
      ,Standard_Cost

    FROM
       Material_Req inner join Material_Trans 
    ON
        Material_Req.Material_Req = Material_Trans.Material_Req
        LEFt JOIN Material
    ON
        Material.Material = Material_Req.Material
    WHERE 
      material_trans.Material_Trans_Date between '20180101' AND GETDATE() 
    GROUP BY 
      material_req.Material   ,Standard_Cost 

    ORDER BY 
    Material_Req.Material, Standard_Cost

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。