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 )