表一:
ID 日期 合同额
1 2006-1-3 123,456.00
2 2003-1-16 323,456.00
3 2006-2-2 30.00
4 2006-2-8 50.00
目标表二:
月份 完成
一月份 446,912.00
二月份 80.00
......................
全年 446,992.00大家帮看看,谢谢
ID 日期 合同额
1 2006-1-3 123,456.00
2 2003-1-16 323,456.00
3 2006-2-2 30.00
4 2006-2-8 50.00
目标表二:
月份 完成
一月份 446,912.00
二月份 80.00
......................
全年 446,992.00大家帮看看,谢谢
SELECT 1, '2006-1-3', 123456.00
UNION
SELECT 2, '2006-1-16', 323456.00
UNION
SELECT 3, '2006-2-2', 30.00
UNION
SELECT 4, '2006-2-8', 50.00SELECT MONTH([DATE]), SUM(AMOUNT) FROM @TEST WHERE YEAR([DATE]) = 2006 GROUP BY MONTH([DATE])
use pubs
if exists(select * from sysobjects where name='A') drop table A
GO
----生成测试数据
create table A(ID int,日期 datetime,合同额 money)
insert into A select 1,'2006-1-3',123456.00
union all select 2,'2006-1-16',323456.00
union all select 3,'2006-2-2',30.00
union all select 4,'2006-2-8',50.00select * from A----完成功能的select语句
select 月份=convert(varchar(6),日期,112),完成数=sum(合同额)
from A
group by convert(varchar(6),日期,112)
order by convert(varchar(6),日期,112) ----删除测试数据
drop table A
use pubs
if exists(select * from sysobjects where name='A') drop table A
GO
----生成测试数据
create table A(ID int,日期 datetime,合同额 money)
insert into A select 1,'2006-1-3',123456.00
union all select 2,'2006-1-16',323456.00
union all select 3,'2006-2-2',30.00
union all select 4,'2006-2-8',50.00select * from A
----完成功能的select语句
select 年月=datename(yy,日期) + '年'+datename(mm,日期)+'月',完成数=sum(合同额)
from A
group by datename(yy,日期) + '年'+datename(mm,日期)+'月'
order by 年月----删除测试数据
drop table A
insert into @t select 1,'2006-1-3',123456.00
union all select 2,'2006-1-16',323456.00
union all select 3,'2006-2-2',30.00
union all select 4,'2006-2-8', 50.00select top 12 identity(int,1,1) as [month] into # from syscolumns a,syscolumns b
select cast(isnull(月份,b.[month]) as varchar)+'月份' as 月份
,isnull(完成,0) as 完成
from
(select 月份=month([date]),
完成=sum(amount)
from @t
group by month([date]))a,# b
where 月份=*b.[month] drop table #
insert into @t select 1,'2006-1-3',123456.00
union all select 2,'2006-1-16',323456.00
union all select 3,'2006-2-2',30.00
union all select 4,'2006-2-8', 50.00select top 12 identity(int,1,1) as [month] into # from syscolumns a,syscolumns b
select cast(isnull(月份,b.[month]) as varchar)+'月份' as 月份
,isnull(完成,0) as 完成
from
(select 月份=month([date]),
完成=sum(amount)
from @t
group by month([date]))a,# b
where 月份=*b.[month]
union all
select '全年' as 月份,sum(amount) as 完成 from @t group by year([date])drop table #