USE [PM]
GO
/****** 對象:? StoredProcedure [dbo].[LfangSatstics]??? 腳本日期: 08/24/2013 10:57:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:??<Author,,Name>
-- Create date: <Create Date,,>
-- Description:?<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[LfangSatstics]
(
?@begintime datetime,
?@OverTime datetime,
?@PName nvarchar(100)
)
AS
BEGIN
? DECLARE @str nvarchar(2000)
? SET @str=''
CREATE table #T
(
?? proId int,
?? gid int,
?? allMoney money
)
--插入數據：
SET @str=@str+'insert into #T(proId,gid,allMoney) SELECT ProjectId,Id,AllMoney? FROM View_LFangStastics where IsSD=''是'' '
if(@PName<>'')
? set @str=@str+' and PName LIKE ''%'' + REPLACE(''' + @PName + ''', ''%'', ''/%'') + ''%'' ESCAPE ''/'''
if(@begintime<>'')
? set @str=@str+' and SignTime>='''+convert(varchar(100),@begintime)+''''
if(@OverTime<>'')
?? set @str=@str+' and SignTime<='''+convert(varchar(100),@OverTime)+''''
?EXEC (@str)
--統計數據：
SELECT CASE WHEN t.tag='2' THEN '單個項目（匯總）' WHEN t.tag='1' THEN '所有項目（匯總）' ELSE v.PName END as ProName,v.GName,t.allmoney FROM
(SELECT proId,gid, SUM(allMoney) as allmoney,CASE WHEN GROUPING(gid)=1 AND GROUPING(proId)=1 THEN '1' WHEN
?GROUPING(gid)=1 THEN '2' ELSE '' END as tag
?FROM #T GROUP BY proId,gid WITH ROLLUP) as t LEFT join View_LFangStastics as v on
t.proId=v.ProjectId AND t.gid=v.Id
--刪除臨時表
DROP TABLE #T
END

sql簡單實用的統計匯總案例參考