34,838
社区成员




--我来个去,全被给你写出来了,你自己看
create table ConsumeCategory
(
ConsumeCategoryID int primary key,
ConsumeCategoryName varchar(20),
Remark varchar(200)
)
create table GoodsCategory
(
GoodsCategoryID int primary key,
ConsumeCategoryID int foreign key (ConsumeCategoryID) REFERENCES ConsumeCategory(ConsumeCategoryID),
GoodsCategoryName varchar(20),
Remark varchar(200)
)
create table ConsumeDetail
(
ConsumeDetailID int primary key,
GoodsCategoryID int foreign key (GoodsCategoryID) REFERENCES GoodsCategory(GoodsCategoryID),
ConsumeMoney decimal(18,5),
ConsumeDate datetime,
Remark Varchar(200)
)
insert into ConsumeCategory values (1,'生活费','生活费类别的消费')
insert into ConsumeCategory values (2,'学杂费','学杂费类别的消费')
insert into GoodsCategory values (1,1,'苹果','')
insert into GoodsCategory values (2,1,'梨','')
insert into GoodsCategory values (3,1,'香蕉','')
insert into GoodsCategory values (4,2,'钢笔','')
insert into GoodsCategory values (5,2,'毛笔','')
insert into ConsumeDetail values (1,1,3,GETDATE(),'')
insert into ConsumeDetail values (2,2,5,GETDATE(),'')
insert into ConsumeDetail values (3,3,7,GETDATE(),'')
insert into ConsumeDetail values (4,4,3,GETDATE(),'')
insert into ConsumeDetail values (5,5,4,GETDATE(),'')
select * from ConsumeCategory
select * from GoodsCategory
select * from ConsumeDetail
select ConsumeCategoryName,SUM(ConsumeMoney) as totleMoney
from ConsumeCategory A inner join GoodsCategory B on A.ConsumeCategoryID=B.ConsumeCategoryID
inner join ConsumeDetail C on B.GoodsCategoryID=C.GoodsCategoryID
group by A.ConsumeCategoryName