日期:2014-05-17  浏览次数:20316 次

请各位高手进来看看,三言两语形容不明白了
举个例子,小刚要消费,消费包括生活费,和学杂费,学杂费包括买铅笔5元,钢笔3元,毛笔2元,一共10元,生活费包括买苹果3元,香蕉4元,鸭梨5元,一共12元,总共消费22元,请问大家,(总消费),(生活费、学杂费)和(每一个详细的消费)怎么建立表?如何连接呢?

------解决方案--------------------
SQL code

--我来个去,全被给你写出来了,你自己看
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

------解决方案--------------------
消费类型表 --> 该表记录消费类型 学杂费对应编码、生活费对应编码...

物品表 -->该表记录物品金额、物品编码、物品所属消费类型编码...

消费明细表 -->该表记录消费明细、包括物品编码、消费时间...

然后根据消费类型分组统计消费情况...