select 名称,
sum(case when 日期 BETWEEN '2013-04-01' and '2013-06-30' then 数额 else 0 end) as 本季度数额,
sum(case when year(日期)=2013 then 数额 else 0 end) as 本年度数额,
sum(数额) as 累计数额
from test
group by 名称
select T.名称
,(SELECT SUM([数额]) 本季度数额 FROM Test
WHERE CONVERT(VARCHAR(10),[日期],120)>='2013-04-01'
AND CONVERT(VARCHAR(10),[日期],120)<='2013-06-30' --2013年2季度
and [名称]=T.名称
GROUP BY [名称]) AS 本季度数额
,(SELECT SUM([数额]) 本年度数额 FROM Test
WHERE YEAR([日期])='2013'--本年度
and [名称]=T.名称
GROUP BY [名称]) AS 本季度数额
,(SELECT SUM([数额]) 累计数额 FROM Test --累计数额
WHERE [名称]=T.名称
GROUP BY [名称]) AS 累计数额
FROM test T
GROUP BY t.名称
--建立测试环境
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Test]') AND type in (N'U'))
DROP TABLE [dbo].[Test]
GO
CREATE TABLE [dbo].[Test](
[日期] [smalldatetime] NULL,
[名称] [varchar](50) NULL,
[数额] [decimal](18, 0) NULL
) ON [PRIMARY]
GO
INSERT INTO dbo.Test
SELECT
'2013-01-01','AA',100 UNION ALL SELECT
'2013-02-01','AA',100 UNION ALL SELECT
'2013-06-30','AA',100 UNION ALL SELECT
'2012-02-05','BB',50 UNION ALL SELECT
'2013-05-05','BB',10
--查询
SELECT x.[名称],x.本季度数额,y.本年度数额,z.累计数额
FROM
(SELECT [名称],SUM([数额]) 本季度数额 FROM Test
WHERE CONVERT(VARCHAR(10),[日期],120)>='2013-04-01'
AND CONVERT(VARCHAR(10),[日期],120)<='2013-06-30' --2013年2季度
GROUP BY [名称]) x
INNER JOIN
(SELECT [名称],SUM([数额]) 本年度数额 FROM Test
WHERE YEAR([日期])='2013'--本年度
GROUP BY [名称]) y ON x.[名称]=y.[名称]
INNER JOIN
(SELECT [名称],SUM([数额]) 累计数额 FROM Test --累计数额
GROUP BY [名称]) z ON x.[名称]=z.[名称]
--结果
/*
名称 本季度数额 本年度数额 累计数额
AA 100 300 300
BB 10 10 60
*/