日期:2014-05-17 浏览次数:20555 次
--------创建测试数据
--项目类型表
if OBJECT_ID('ItemType_20121129') is not null drop table ItemType_20121129
create table ItemType_20121129(id int identity(1,1),name nvarchar(50))
go
insert into ItemType_20121129(name)
select '国家重大项目' union all
select '国家级项目' union all
select '部级项目'
--项目明细表
if OBJECT_ID('Projectinfo_20121129') is not null drop table Projectinfo_20121129
create table Projectinfo_20121129(id int identity(1,1),IT_id int,number int,outlay int)
go
insert into Projectinfo_20121129(IT_id,number,outlay)
select 1,3,9 union all
select 2,5,20 union all
select 3,4,20 union all
select 2,7,28 union all
select 3,4,12 union all
select 2,5,15 union all
select 3,6,24
--------查询
;with T as(
select IT_id,SUM(number)as [项目个数],SUM(outlay) as [经费(万)] from Projectinfo_20121129 group by IT_id
)
select it.name,t.[项目个数],t.[经费(万)] from T t left join
ItemType_20121129 it on it.id=t.IT_id
--------查询结果
/*
name 项目个数 经费(万)
-------------------------------------------------- ----------- -----------
国家重大项目 3 9
国家级项目 &