Select sum(营业额表.金额) -(Select Sum(金额) From 费用表 Where 日期 Between @StartDate and @EndDate) -(Select Sum(金额) From 工资表 Where 日期 Between @StartDate and @EndDate) -(Select Sum(金额) From 耗材表 Where 日期 Between @StartDate and @EndDate) as 利润 From 营业额表 Where 日期 Between @StartDate and @EndDate 视图该做了吧.
--查询 2004-06-01到2004-06-30 的利润该怎么写?select 项目=isnull(a.项目,isnull(b.项目,c.项目)) ,利润=isnull(a.营业额,0)-isnull(b.费用,0)-isnull(c.耗材,0) from( select 日期,项目,营业额=sum(金额) from 营业额表 where 日期 between '2004-06-01' and '2004-06-30' group by 项目 )a full join( select 项目,费用=sum(金额) from 费用表 where 日期 between '2004-06-01' and '2004-06-30' group by 项目 )b on a.项目=b.项目 full join( select 项目,耗材=sum(金额) from 耗材表 where 日期 between '2004-06-01' and '2004-06-30' group by 项目 )c on a.项目=c.项目
--如果我要查2004-1-1 到 2004-5-31 的工资,条件该怎么写呢?--你的工资表中只有年月,没有日期,查询怎么又要按日期查呢?select * from 工资表 where right(10000+年,4)+'-'+right(100+月,2) between '2004-1' and '2004-5'
select * from 工资表 where right(10000+年,4)+'-'+right(100+月,2) between '2004-01' and '2004-05'
-(Select Sum(金额) From 费用表 Where 日期 Between @StartDate and @EndDate)
-(Select Sum(金额) From 工资表 Where 日期 Between @StartDate and @EndDate)
-(Select Sum(金额) From 耗材表 Where 日期 Between @StartDate and @EndDate) as 利润
From 营业额表
Where 日期 Between @StartDate and @EndDate 视图该做了吧.
,利润=isnull(a.营业额,0)-isnull(b.费用,0)-isnull(c.耗材,0)
from(
select 日期,项目,营业额=sum(金额)
from 营业额表
where 日期 between '2004-06-01' and '2004-06-30'
group by 项目
)a full join(
select 项目,费用=sum(金额)
from 费用表
where 日期 between '2004-06-01' and '2004-06-30'
group by 项目
)b on a.项目=b.项目 full join(
select 项目,耗材=sum(金额)
from 耗材表
where 日期 between '2004-06-01' and '2004-06-30'
group by 项目
)c on a.项目=c.项目
where right(10000+年,4)+'-'+right(100+月,2) between '2004-1' and '2004-5'
where right(10000+年,4)+'-'+right(100+月,2) between '2004-01' and '2004-05'