SQL Server使用基于秩的求和函数

您可以在外部查询中再次聚合以获得驱动器大小的总和。使用分区和将在分区窗口内为您提供重复的值。

SELECT
    *,
    TotalSize=SUM(SizeGB) OVER (PARTITION BY DriveName)
FROM
(
    SELECT sub.Name, sub.SizeGB, sub.DriveName,
    RANK() OVER   
    (PARTITION BY sub.DriveName ORDER BY sub.Name DESC) AS Rank
    FROM
    (
      SELECT NAME,
    SUBSTRING(physical_name, 0, 2) AS DriveName,
    SIZE * 8 / (1024*1024) AS SizeGB
    FROM sys.master_files
    WHERE database_id > 4
    ) as sub
    GROUP BY sub.DriveName, sub.Name, sub.SizeGB
)AS X

    SELECT sub.Name, sub.SizeGB, sub.DriveName,
    RANK() OVER   
    (PARTITION BY sub.DriveName ORDER BY sub.Name DESC) AS Rank,
    SUM(SizeGB) OVER (PARTITION BY DriveName) AS TotalSize
    FROM
    (
      SELECT NAME,
    SUBSTRING(physical_name, 0, 2) AS DriveName,
    SIZE * 8 / (1024*1024) AS SizeGB
    FROM sys.master_files
    WHERE database_id > 4
    ) as sub
    GROUP BY sub.DriveName, sub.Name, sub.SizeGB

This should retrieve it:

; with cte
AS
(SELECT  sub.Name, sub.SizeGB, sub.DriveName,
RANK() OVER   
(PARTITION BY sub.DriveName ORDER BY sub.Name DESC) AS Rank

FROM
(
  SELECT NAME,
SUBSTRING(physical_name, 0, 2) AS DriveName,
SIZE * 8 / (1024*1024) AS SizeGB
FROM sys.master_files
WHERE database_id > 4
) as sub
GROUP BY sub.DriveName, sub.Name, sub.SizeGB
)   
select * from cte  
JOIN (SELECT sum(sizeGB) sum, drivename dn from cte GROUP BY drivename) a 
ON a.dn = cte.drivename