sql - SUM 由两个不同的 GROUP BY

我从我的报告中得到了错误的结果。也许我错过了一些简单的东西。

该报告是一个内联表值函数，它应该计算我们商店中的 cargo 移动以及这些备件的 claim 频率(在维修中更换)。

问题:商店表中的不同备件(我们称之为 SP )可以链接到“维修表”( TSP )中的同一个备件。我需要 SP 中每个备件的 cargo 移动以及 TSP 中每个不同备件的 claim 计数。

这是相关部分的非常简化的摘录:

create table #tsp(id int, name varchar(20),claimed int);
create table #sp(id int, name varchar(20),fiTsp int,ordered int);

insert into #tsp values(1,'1235-6044',300);
insert into #tsp values(2,'1234-5678',400);

insert into #sp values(1,'1235-6044',1,30);
insert into #sp values(2,'1235-6044',1,40);
insert into #sp values(3,'1235-6044',1,50);
insert into #sp values(4,'1234-5678',2,60);

WITH cte AS(
    select tsp.id As TspID,tsp.name as TspName,tsp.claimed As Claimed
    ,sp.id As SpID,sp.name As SpName,sp.ordered As Ordered
    from #sp sp inner join #tsp tsp
    on sp.fiTsp=tsp.id
)
SELECT TspName, SUM(Claimed) As Claimed, Sum(Ordered) As Ordered
FROM cte
Group By TspName

drop table #tsp;
drop table #sp;

结果:

TspName       Claimed   Ordered
1234-5678       400       60
1235-6044       900       120

Ordered -count 是正确的，但对于 TspName='1235-6044'，Claimed -count 应该是 300 而不是 900。

我需要按 Tsp.ID 分组为 claim 计数，并按 Sp.ID 分组为订单计数。但是如何在一个查询中？

编辑 :实际上 TVF 看起来像(注意 getOrdered 和 getClaimed 是 SVF，我在 TSP 类别的外部选择中分组):

CREATE FUNCTION [Gambio].[rptReusedStatistics](
     @fromDate datetime
    ,@toDate datetime
    ,@fromInvoiceDate datetime
    ,@toInvoiceDate datetime
    ,@idClaimStatus varchar(50)
    ,@idSparePartCategories varchar(1000)
    ,@idSpareParts varchar(1000)
)

RETURNS TABLE AS
RETURN(
    WITH ExclusionCat AS(
        SELECT idSparePartCategory AS ID From tabSparePartCategory
        WHERE idSparePartCategory IN(- 3, - 1, 6, 172,168)
    ), Report AS(
        SELECT Cat.SparePartCategoryName AS Category
        ,TSP.SparePartDescription AS Part
        ,TSP.SparePartName AS PartNumber
        ,SP.Inventory
        ,Gambio.getGoodsIn(SP.idSparePart,@FromDate,@ToDate) GoodsIn
        ,Gambio.getOrdered(SP.idSparePart,@FromDate,@ToDate) Ordered
        --,CASE WHEN TSP.idSparePart IS NULL THEN 0 ELSE
        --  Gambio.getClaimed(TSP.idSparePart,@FromInvoiceDate,@ToInvoiceDate,@idClaimStatus,NULL)END AS Claimed
        ,CASE WHEN TSP.idSparePart IS NULL THEN 0 ELSE
            Gambio.getClaimed(TSP.idSparePart,@FromInvoiceDate,@ToInvoiceDate,@idClaimStatus,1)END AS ClaimedReused
        ,CASE WHEN TSP.idSparePart IS NULL THEN 0 ELSE
            Gambio.getCostSaving(TSP.idSparePart,@FromInvoiceDate,@ToInvoiceDate,@idClaimStatus)END AS Costsaving
        FROM  Gambio.SparePart AS SP
        INNER JOIN tabSparePart AS TSP ON SP.fiTabSparePart = TSP.idSparePart
        INNER JOIN tabSparePartCategory AS Cat
        ON Cat.idSparePartCategory=TSP.fiSparePartCategory
        WHERE Cat.idSparePartCategory NOT IN(SELECT ID FROM ExclusionCat)
        AND (@idSparePartCategories IS NULL
            OR TSP.fiSparePartCategory IN(
                SELECT Item From dbo.Split(@idSparePartCategories,',')
            )
        )
        AND (@idSpareParts IS NULL
            OR TSP.idSparePart IN(
                SELECT Item From dbo.Split(@idSpareParts,',')
            )
        )
    )
    SELECT Category
    --, Part
    --, PartNumber
    , SUM(Inventory)As InventoryCount
    , SUM(GoodsIn) As GoodsIn
    , SUM(Ordered) As Ordered
    --, SUM(Claimed) As Claimed
    , SUM(ClaimedReused)AS ClaimedReused
    , SUM(Costsaving) As Costsaving
    , Count(*) AS PartCount
    FROM Report
    GROUP BY Category
)

解决方案 :

感谢 Aliostad，我通过先分组然后加入(实际的 TVF，减少到最低限度)解决了这个问题:

WITH Report AS(
        SELECT Cat.SparePartCategoryName AS Category
        ,TSP.SparePartDescription AS Part
        ,TSP.SparePartName AS PartNumber
        ,SP.Inventory
        ,SP.GoodsIn
        ,SP.Ordered
        ,Gambio.getClaimed(TSP.idSparePart,@FromInvoiceDate,@ToInvoiceDate,@idClaimStatus,1) AS ClaimedReused
        ,Gambio.getCostSaving(TSP.idSparePart,@FromInvoiceDate,@ToInvoiceDate,@idClaimStatus) AS Costsaving
        FROM  (
            SELECT GSP.fiTabSparePart
            ,SUM(GSP.Inventory)AS Inventory
            ,SUM(Gambio.getGoodsIn(GSP.idSparePart,@FromDate,@ToDate))AS GoodsIn
            ,SUM(Gambio.getOrdered(GSP.idSparePart,@FromDate,@ToDate))AS Ordered
            FROM Gambio.SparePart GSP
            GROUP BY GSP.fiTabSparePart
        )As SP
        INNER JOIN tabSparePart TSP ON  SP.fiTabSparePart = TSP.idSparePart
        INNER JOIN tabSparePartCategory AS Cat
        ON Cat.idSparePartCategory=TSP.fiSparePartCategory
    )
    SELECT Category
    , SUM(Inventory)As InventoryCount
    , SUM(GoodsIn) As GoodsIn
    , SUM(Ordered) As Ordered
    , SUM(ClaimedReused)AS ClaimedReused
    , SUM(Costsaving) As Costsaving
    , Count(*) AS PartCount
    FROM Report
    GROUP BY Category

最佳答案

你先加入，然后分组。你需要反转它，先GROUP BY，然后JOIN。

所以在我的子查询中，我首先分组，然后加入:

select
    claimed,
    ordered
from
     #tsp
inner JOIN
      (select
             fitsp,
             SUM(ordered) as ordered
       from
              #sp
        group by
              fitsp) as SUMS
on
     SUMS.fiTsp = id;

关于sql - SUM 由两个不同的 GROUP BY，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/9533173/