选择不同的和组

Question

SELECT DISTINCT [SC Vermorel SRL$Production Order].No_, 
                [SC Vermorel SRL$Production Order].[Replan Ref_ No_], 
                [SC Vermorel SRL$Production Order].Description, 
                [SC Vermorel SRL$Production Order].[Source No_], 
                [SC Vermorel SRL$Production Order].[Routing No_], 
                [SC Vermorel SRL$Production Order].Quantity, 
                [SC Vermorel SRL$Production Order].[Old Prod_ Order No_], 
                [SC Vermorel SRL$Routing Line].[Run Time], 
                [SC Vermorel SRL$Routing Line].[Run Time] * [SC Vermorel SRL$Production Order].Quantity AS TotalOre, 
                [SC Vermorel SRL$Prod_ Order Line].[Finished Quantity], 
                SUM([SC Vermorel SRL$ProductieZilnica].[Cantitate sudura]) AS [Cantitate sudura], 
                SUM([SC Vermorel SRL$ProductieZilnica].[Cantitate montaj]) AS [Cantitate montaj]
FROM [SC Vermorel SRL$Production Order] 
INNER JOIN [SC Vermorel SRL$Routing Line] 
    ON [SC Vermorel SRL$Production Order].[Routing No_] = [SC Vermorel SRL$Routing Line].[Routing No_] 
INNER JOIN [SC Vermorel SRL$Prod_ Order Line] 
    ON [SC Vermorel SRL$Production Order].No_ = [SC Vermorel SRL$Prod_ Order Line].[Prod_ Order No_] 
INNER JOIN [SC Vermorel SRL$ProductieZilnica] 
    ON [SC Vermorel SRL$Production Order].No_ = [SC Vermorel SRL$ProductieZilnica].RPO
WHERE ([SC Vermorel SRL$Production Order].Status = 3) 
        AND ([SC Vermorel SRL$Production Order].[Location Code] = 'MACH FIN') 
        AND ([SC Vermorel SRL$Production Order].[Gen_ Prod_ Posting Group] = 'FINISHED G') 
        AND ([SC Vermorel SRL$Routing Line].No_ = '150')
GROUP BY [SC Vermorel SRL$Production Order].No_

我需要附加来自另一个表([SC Vermorel SRL$ProductieZilnica])的所有条目的和，该表对应于来自[SC Vermorel SRL$Production Order]表的每个不同的No_。

因为上帝的爱，我不能让它发挥作用。有人能给我指明正确的方向吗？

Answer 1

首先，使用表别名 -它将使您的查询更具可读性。

如果使用GROUP BY，则需要添加所有列，但在聚合函数中的列除外(获得SUM)，例如：

SELECT  PO.No_, 
        PO.[Replan Ref_ No_], 
        PO.[Description], 
        PO.[Source No_], 
        PO.[Routing No_], 
        PO.Quantity, 
        PO.[Old Prod_ Order No_], 
        RL.[Run Time], 
        RL.[Run Time] * PO.Quantity AS TotalOre, 
        POL.[Finished Quantity], 
        SUM(PZ.[Cantitate sudura]) AS [Cantitate sudura], 
        SUM(PZ.[Cantitate montaj]) AS [Cantitate montaj]
FROM  [SC Vermorel SRL$Production Order] PO
INNER JOIN [SC Vermorel SRL$Routing Line] RL
    ON PO.[Routing No_] = RL.[Routing No_] 
INNER JOIN [SC Vermorel SRL$Prod_ Order Line] POL
    ON PO.No_ = POL.[Prod_ Order No_] 
INNER JOIN [SC Vermorel SRL$ProductieZilnica] PZ
    ON PO.No_ = PZ.RPO
WHERE     (PO.Status = 3) AND (PO.[Location Code] = 'MACH FIN') AND 
                  (PO.[Gen_ Prod_ Posting Group] = 'FINISHED G') AND (RL.No_ = '150')
GROUP BY PO.No_,
        PO.[Replan Ref_ No_], 
        PO.[Description], 
        PO.[Source No_], 
        PO.[Routing No_], 
        PO.Quantity, 
        PO.[Old Prod_ Order No_], 
        RL.[Run Time], 
        PO.Quantity, 
        POL.[Finished Quantity]