代码之家 › 专栏 › 技术社区 › Imran Ali Khan

如何在Sql Server中基于标题和子标题获取小计和总计

grouping group-by sql-server-2008 sql-server sql

Imran Ali Khan · 技术社区 · 7 年前

我有这个SQL代码

SELECT TOP (100) PERCENT 
    dbo._SHIFT._SHIFTNAME AS [Shift Name], dbo._TANK._TANKNAME AS [Tank Name],
    dbo._NOZZLE._NOZZLENAME AS [Nozzle Name], 
    dbo._SHIFTENTRYDET._OPENING AS Opening, dbo._SHIFTENTRYDET._CLOSING AS Closing, 
    SUM(dbo._SHIFTENTRYDET._TOTALSALE) AS [Total Sale], 
    dbo._SHIFTENTRYDET._RATE AS Rate, 
    CASE
       WHEN GROUPING(dbo._SHIFT._SHIFTNAME) = 1 
          THEN NULL 
       WHEN GROUPING(dbo._TANK._TANKNAME) = 1 
          THEN NULL
       ELSE SUM(dbo._SHIFTENTRYDET._TOTALAMOUNT)
    END AS Amount
FROM      
    dbo._NOZZLE 
RIGHT OUTER JOIN
    dbo._SHIFTENTRYDET ON dbo._NOZZLE._NOZZLEID_PK = dbo._SHIFTENTRYDET._NOZZLEID_PK 
LEFT OUTER JOIN
    dbo._TANK ON dbo._SHIFTENTRYDET._TANKID_PK = dbo._TANK._TANKID_PK 
RIGHT OUTER JOIN
    dbo._SHIFTENTRY ON dbo._SHIFTENTRYDET._SHIFTENTRYID_PK = dbo._SHIFTENTRY._SHIFTENTRYID_PK 
LEFT OUTER JOIN
    dbo._SHIFT ON dbo._SHIFTENTRY._SHIFTID_PK = dbo._SHIFT._SHIFTID_PK
WHERE     
    (dbo._SHIFTENTRY._ISDELETED = N'1') 
    AND (dbo._SHIFT._ISDELETED = N'1') 
    AND (dbo._SHIFTENTRYDET._ISDELETED = N'1')
GROUP BY GROUPING SETS  
    ((dbo._SHIFT._SHIFTNAME, dbo._TANK._TANKNAME, dbo._NOZZLE._NOZZLENAME, dbo._SHIFTENTRYDET._OPENING, dbo._SHIFTENTRYDET._CLOSING, dbo._SHIFTENTRYDET._RATE), (dbo._SHIFT._SHIFTNAME, dbo._TANK._TANKNAME))

后果

但我想要下面的结果

我使用 GROUP BY GROUPING SETS , GROUP BY ROLLUP , GROUP BY CUBE 但运气不好。

如果删除GROUP BY和GROUPING SETS等,简单数据如下

Excel文件Google Drive链接: https://drive.google.com/open?id=1DgyI_7-p1l7n1KjeAnf1DerQTGLJC-wB

Excel文件有3个工作表

示例数据,此处为不带groupiung的数据
预期结果
分组集的结果

1 回复 | 直到 7 年前

Wolfgang Kais 7 年前

两个分组集都包含Shiftname和Tankname,因此分组函数将始终返回0。

由于WHERE子句包含Shift、Shiftentry和Shiftentrydet的标准,因此记录必须存在,因此连接是内部连接(好的,可能不适用于油箱和喷嘴)。

最好是按键值分组,而不是按显示名称分组,而且按其他信息(打开、关闭)分组在我看来似乎是错误的。唯一应该分组的字段是Shift、Tank和Shiftentrydet的键(而不是喷嘴,因为喷嘴似乎构成了细节),因此我将在子查询(CTE)中进行分组和计算,并在主查询中连接其余字段,尽管会查询两次Shiftentrydet和Shift表(一次用于计算,一次用于显示)。我猜列Shiftentrydet\u PK确实存在)。

最后,您的查询将不会在同一列中为总计返回班次-储罐-和喷嘴名称和标题,也不会为班次和储罐创建标题行。为此,我建议使用报告(SSRS)。

对于仅查询的非报告解决方案,请尝试以下操作(未测试):

WITH CTE_grp (SHIFTID_PK, TANKID_PK, Shipentrydet_PK, Amount) AS (
    SELECT se._SHIFTID_PK, sed._TANKID_PK, sed.Shipentrydet_PK, SUM(sed._TOTALAMOUNT)
    FROM dbo._SHIFT s
      INNER JOIN dbo._SHIFTENTRY se ON s._SHIFTID_PK = se._SHIFTID_PK
      INNER JOIN dbo._SHIFTENTRYDET sed ON se._SHIFTENTRYID_PK = sed._SHIFTENTRYID_PK
    WHERE (se._ISDELETED = N'1') AND (sed._ISDELETED = N'1') AND (s._ISDELETED = N'1')
    GROUP BY se._SHIFTID_PK, sed._TANKID_PK, sed.Shipentrydet_PK WITH ROLLUP
)
SELECT s._SHIFTNAME AS [Shift Name], t._TANKNAME AS [Tank Name], n._NOZZLENAME AS [Nozzle Name]
  , sed._OPENING AS Opening, sed._CLOSING AS Closing, sed._TOTALSALE AS [Total Sale]
  , sed._RATE AS Rate, c.Amount
FROM CTE_grp c
  LEFT OUTER JOIN dbo._SHIFT s ON c.SHIFTID_PK = s._SHIFTID_PK
  LEFT OUTER JOIN dbo._TANK t ON c.TANKID_PK = t._TANKID_PK
  LEFT OUTER JOIN  dbo._SHIFTENTRYDET sed ON c.Shipentrydet_PK = sed.Shipentrydet_PK
    LEFT OUTER JOIN dbo._NOZZLE n ON sed._NOZZLEID_PK = n._NOZZLEID_PK;