sqlserver 小计合计总计

值为空时 GROUPING(F1) = 1

SELECT CASE WHEN GROUPING(F1) = 1 THEN '总计'
WHEN GROUPING(F1) = 0 AND GROUPING(F2) = 1 THEN  F1+'合计'
WHEN GROUPING(F1) = 0 AND GROUPING(F2) = 0 AND GROUPING(F3) = 1 THEN  F1+'的'+F2+'小计'
ELSE F1
        END AS F1 ,
        CASE WHEN GROUPING(F3) = 1 THEN '' ELSE F2
        END AS F2 ,
        ISNULL(F3, '') AS F3 ,
        SUM(F4) AS F4,
SUM(F5) AS F5
FROM  TB
GROUP BY F1,F2,F3 WITH ROLLUP;

 

示例一

select deptName,routeName,vehicleName, sum(CCW1) as CCW1 from View_LocalConsumeDetailReport GROUP BY deptName,routeName,vehicleName with rollup

实例二

select case when grouping(deptName)=1 then '合计' else deptName end as deptName, case when grouping(deptName)=0 and grouping(vehicleName)=1 then '小计'  else vehicleName end as vehicleName,driverName ,sum(CTW1) as CTW1 from View_TodayDriverByCollectDate group by deptName,vehicleName,driverName with rollup having (grouping(deptName)=1) or (grouping(deptName)=0 and grouping(vehicleName)=1) or (grouping(deptname)= 0 and grouping(vehicleName)=0 and grouping(drivername)=0)

示例三

SELECT     CASE WHEN grouping(deptName) = 1 THEN '合计' WHEN grouping(deptName) = 0 AND grouping(routeName) = 1 THEN '小计' else deptName END AS deptName,
CASE WHEN grouping(deptName) = 0 AND grouping(routeName) = 0 and grouping(vehicleName)=1 THEN '小计' ELSE routeName END AS routeName,
ISNULL(vehicleName, '') AS vehicleName,
 SUM(CTW1) AS CTW1, SUM(CCW1) AS CCW1,
                      SUM(dbo.F2Y(CCM1)) AS CCM1, SUM(dbo.F2Y(CFM1)) AS CFM1
FROM         View_ConsumeDetailReport AS mcis
WHERE     (1 = 1) AND (CONVERT(varchar(100), reportDate, 23) >= '2014-11-01') AND ('2014-11-30' >= CONVERT(varchar(100), reportDate, 23))
GROUP BY deptName, routeName, vehicleName WITH ROLLUP

示例四

SELECT
 CASE
WHEN grouping(deptName)= 1 THEN
 '合计'
WHEN grouping(deptName)= 0
AND grouping(routeName)= 1 THEN
 '小计'
ELSE
 deptName
END AS deptName,
 CASE
WHEN grouping(deptName)= 0
AND grouping(routeName)= 0
AND grouping(vehicleName)= 1
AND grouping(driverName)= 1
AND grouping(reportDate)= 1 THEN
 '小计'
ELSE
 routeName
END AS routeName,
 mcis.vehicleName AS vehicleName,
 mcis.driverName AS driverName,
 mcis.reportDate AS reportDate,
 SUM(WMCCT1)AS WMCCT1,
 SUM(WMMCT1)AS WMMCT1,
 SUM(WMCCT2)AS WMCCT2,
 SUM(WMMCT2)AS WMMCT2,
 SUM(WMCCT3)AS WMCCT3,
 SUM(WMMCT3)AS WMMCT3,
 SUM(WMCCT1 + WMCCT2 + WMCCT3)AS total,
 SUM(WMMCT1 + WMMCT2 + WMMCT3)AS totalMoney
FROM
 view_dayTotalArchivedReport mcis
WHERE
 1 = 1
AND mcis.reportDate >= '2017-06-01'
AND '2017-08-04' >= mcis.reportDate
AND mcis.deptOID IN('0')
GROUP BY
 deptName,
 routeName,
 vehicleName,
 driverName,
 reportDate WITH ROLLUP

 

示例五

select case when grouping(RechargeCentersName)=1 then '合计' when grouping(RechargeCentersName)=0 and grouping(terminalsName)=1
and grouping(operatorName)=1 and grouping(summaryDate)=1 then '小计' when grouping(RechargeCentersName)=0
and grouping(terminalsName)=0 and grouping(operatorName)=0 and grouping(summaryDate)=0 then mcis.RechargeCentersName else 'nothing'
end as RechargeCentersName,mcis.terminalsName as terminalsName,mcis.operatorName as operatorName,mcis.summaryDate as summaryDate,
ISNULL(sum(mcis.issueCount), 0) as issueCount,dbo.F2Y(ISNULL(sum(mcis.depositeMoney), 0)) as depositeMoney,
ISNULL(sum(mcis.wRechargeCount), 0) as wRechargeCount,dbo.F2Y(ISNULL(sum(mcis.wRechargeMoney), 0)) as wRechargeMoney,
ISNULL(sum(mcis.disCountRechargeCount), 0) as disCountRechargeCount,dbo.F2Y(ISNULL(sum(mcis.disCountRechargeMoney), 0))
as disCountRechargeMoney,ISNULL(sum(mcis.creditChangeCount), 0) as creditChangeCount,ISNULL(sum(mcis.creditNums), 0) as creditNums,
ISNULL(sum(mcis.mRechargeCount), 0) as mRechargeCount,dbo.F2Y(ISNULL(sum(mcis.mRechargeMoney), 0)) as mRechargeMoney,
ISNULL(sum(mcis.lossCount), 0) as lossCount,dbo.F2Y(ISNULL(sum(mcis.lossdeposit), 0)) as lossdeposit,ISNULL(sum(mcis.quitCount), 0)
as quitCount,dbo.F2Y(ISNULL(sum(mcis.quitFactorage), 0)) as quitFactorage,dbo.F2Y(ISNULL(sum(mcis.quitMoney-mcis.quitFactorage), 0))
as quitMoney,ISNULL(sum(mcis.issueCount+mcis.wRechargeCount+mcis.mRechargeCount+mcis.lossCount+mcis.quitCount), 0) as Cheji,
dbo.F2Y(ISNULL(SUM(mcis.depositeMoney+mcis.wRechargeMoney+mcis.mRechargeMoney+mcis.lossdeposit-mcis.quitMoney),0)) as Mheji from
View_SellAndReTodayReport mcis where 1=1 and mcis.summaryDate >='2016-01-16' and '2018-01-16' >= mcis.summaryDate and
mcis.operatorOID like '%' and mcis.terminalsOID in ('2372E6480F2248A694809A2447415CB0','297edff8523eaf8f01523ed87d6f001e',
'297edff852581e0801525888d09b0017','297edff852581e08015258ba14be0031','297edff852581e08015258bf43f60037','297edff852581e0801525c8645620059',
'297edff8525efa7901526252aa8902b9','297edff85902bbe901591a9f65dd14c3','297edff85a216d3e015a875fea065d73') group by RechargeCentersName ,
terminalsName ,operatorName ,summaryDate WITH ROLLUP HAVING (grouping(RechargeCentersName) = 1 ) or
( grouping(RechargeCentersName) = 0 AND grouping(terminalsName) = 1 AND grouping(operatorName) = 1 AND grouping(summaryDate) = 1 )
or ( grouping(RechargeCentersName) = 0 AND grouping(terminalsName) = 0 AND grouping(operatorName) = 0 AND grouping(summaryDate) = 0 )