SELECT x.SubCategoryID
,x.brand
,x.[Avg Revenue]
( Select Sum(x2.[Avg Revenue])
From FROM [BrandRevenue] x2
Where x2.[SubCategoryID] = x.[SubCategoryID] AND x2.[Avg Revenue] <= x.[Avg Revenue]) As [Running Sum]
FROM (Select SubCategoryID, brand, [Avg Revenue]
FROM [BrandRevenue]
Order BY SubCategoryID, [Avg Revenue] DESC) As x
Group BY x.SubCategoryID, x.brand, x.[Avg Revenue];
( Select Sum(x2.[Avg Revenue])
From FROM [BrandRevenue] x2
...
我认为您不需要 from 子句中的子查询,也不需要 GROUP BY。我认为这会更好,而且肯定更简单:
SELECT x.SubCategoryID,
x.brand,
x.[Avg Revenue],
( SELECT SUM(x2.[Avg Revenue])
FROM [BrandRevenue] x2
WHERE x2.[SubCategoryID] = x.[SubCategoryID]
AND x2.[Avg Revenue] <= x.[Avg Revenue]
) AS [Running Sum]
FROM BrandRevenue AS x
ORDER BY x.SubCategoryID, x.[Avg Revenue] DESC;
附录
我认为要解决品牌具有相同收入的问题,您需要在相关子查询中添加一些额外的逻辑:
SELECT x.SubCategoryID,
x.brand,
x.[Avg Revenue],
( SELECT SUM(x2.[Avg Revenue])
FROM [BrandRevenue] x2
WHERE x2.[SubCategoryID] = x.[SubCategoryID]
AND ( x2.[Avg Revenue] <= x.[Avg Revenue]
OR (x2.[Avg Revenue] = x.[Avg Revenue]
AND x2.Brand <= x.Brand
)
) AS [Running Sum]
FROM BrandRevenue AS x
ORDER BY x.SubCategoryID, x.[Avg Revenue] DESC, x.Brand;