日期:2014-05-16 浏览次数:20568 次
----------------------------------------------------------------
-- Author :fredrickhu(小F,向高手学习)
-- Date :2014-02-28 11:05:21
-- Verstion:
-- Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Enterprise Edition on Windows NT 6.1 <X86> (Build 7601: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([id] int,[项目] varchar(2),[金额] int,[日期] datetime)
insert [tb]
select 1,'李',5,'2013-01-10' union all
select 2,'李',15,'2013-01-15' union all
select 3,'张',10,'2013-01-20' union all
select 4,'李',1,'2013-01-25' union all
select 5,'李',2,'2013-01-25' union all
select 6,'赵',3,'2013-01-25'
--------------开始查询--------------------------
SELECT 序号=identity(INT,1,1),* INTO #tb FROM tb
SELECT '0' AS 序号,'呈前' AS 项目,SUM(金额) AS 金额 FROM TB WHERE 日期<'2013-01-25'
UNION ALL
SELECT LTRIM(序号),项目,金额 FROM #tb WHERE 日期='2013-01-25'
UNION ALL
SELECT '合计','',SUM(金额) FROM tb WHERE 日期='2013-01-25'
UNION ALL
SELECT '累计','',SUM(金额) FROM tb
DROP TABLE #tb
----------------结果----------------------------
/* 序号 项目 金额
------------------------ ---- -----------
0 呈前 30
1 李 1
2 李 2
3 &