一个统计报表的SQL语句

select t.lngShopID,

case when t.lngShopID = 6666666666 --and t.lngDateID = 7777777777
          then '总计'
     else s.strShopName
     end as strShopName,

case when t.lngShopID = 6666666666 --and t.lngDateID = 7777777777
 then ''
     when t.lngShopID < 6666666666 and t.lngChannelID = 8888888888
 then '合计'
     else convert(varchar(10), d.dtDateTime, 121) end as strDate,

t.lngChannelID,

case when t.lngShopID < 6666666666 and t.lngChannelID = 8888888888
          then ''
     when t.lngArticleID = 9999999999
          then '小计'
     else c.strChannelName
     end as strChannelName,

a.strTitle,
t.lngArticleID,
t.lngIPcounts
from (SELECT Case When GROUPING(a.lngShopID) = 1 Then 6666666666 Else lngShopID End As lngShopID,
  Case When Grouping(b.lngDateID) = 1 Then 7777777777 Else b.lngDateID End As lngDateID,
  Case When Grouping(a.lngChannelID) = 1 Then 8888888888 Else lngChannelID End As lngChannelID, 
  Case When Grouping(a.lngArticleID) = 1 Then 9999999999 Else a.lngArticleID End As lngArticleID,   
  Sum(a.lngIPcounts) As lngIPcounts
 From IISLogCount a
  Inner Join dimDate b On Convert(varchar(10),b.dtDateTime,121) = Convert(varchar(10),a.dtmCountTime,121)
   Group By a.lngShopID,b.lngDateID,a.lngChannelID,a.lngArticleID With RollUp
) as t

 left join dimshop s on t.lngshopID = s.lngShopID
 left join dimChannel c on t.lngChannelID = c.lngChannelID
 left join dimDate d on t.lngDateID = d.lngDateID
 left join BNManage.dbo.article a on t.lngArticleID = a.lngArticleID

  where not (t.lngShopID <> 6666666666 and t.lngDateID = 7777777777 and t.lngChannelID = 8888888888)
 {?strWhere}{?strWhere}
   Order By t.lngShopID,t.lngDateID,t.lngChannelID, t.lngArticleID 

传入报表的参数为:strWhere,本语句的关键就是蓝色的两个关键字的运用.

运行后生成如下类似效果:

大类	日期	小类	活动	点击率
大类	2006-9-28	类别1	A	2
			0	5
			B	3
		小计		10
		类别2	A	1
			B	2
			C	3
			D	4
		小计		10
	合计			20
	2006-9-29	类别3	A	2
			0	5
			B	3
		小计		10
		类别4	A	1
			B	2
			C	3
			D	4
		小计		10
	合计			20