昨天的问题，有点改动，谢谢大家！
create table t_zc_fk --支出费用(罚款表)
(
btbm varchar(50), --本趟编码
sj_name1 varchar(50),--主司机姓名
sj_name2 varchar(50),--副司机姓名
je decimal(13,2)--罚款金额
)
insert into t_zc_fk select '1 ', '主司机 ', '副司机 ',100
insert into t_zc_fk select '1 ', '主司机 ', '副司机 ',150
insert into t_zc_fk select '2 ', '主司机 ', '副司机 ',100
insert into t_zc_fk select '2 ', '主司机 ', '副司机 ',200
create table t_zc_jy --支出费用(加油表)
(
btbm varchar(50), --本趟编码
sj_name1 varchar(50),--主司机姓名
sj_name2 varchar(50),--副司机姓名
dj decimal(13,2),--汽油单价
ss int --加油升数
)
insert into t_zc_jy select '1 ', '主司机 ', '副司机 ',10,10
insert into t_zc_jy select '1 ', '主司机 ', '副司机 ',15,10
insert into t_zc_jy select '2 ', '主司机 ', '副司机 ',10,20
insert into t_zc_jy select '2 ', '主司机 ', '副司机 ',15,30
create table t_zc_lq --支出费用(路桥表)
(
btbm varchar(50), --本趟编码
sj_name1 varchar(50),--主司机姓名
sj_name2 varchar(50),--副司机姓名
je decimal(13,2)--过路费金额
)
insert into t_zc_lq select '1 ', '主司机 ', '副司机 ',100
insert into t_zc_lq select '1 ', '主司机 ', '副司机 ',150
insert into t_zc_lq select '2 ', '主司机 ', '副司机 ',100
insert into t_zc_lq select '2 ', '主司机 ', '副司机 ',200
create table t_zc_pj --支出费用(配件表)
(
btbm varchar(50), --本趟编码
sj_name1 varchar(50),--主司机姓名
sj_name2 varchar(50),--副司机姓名
dj decimal(13,2),--配件单价
sl int --配件数量
)
insert into t_zc_pj select '1 ', '主司机 ', '副司机 ',10,10
insert into t_zc_pj select '1 ', '主司机 ', '副司机 ',15,10
insert into t_zc_pj select '2 ', '主司机 ', '副司机 ',10,20
insert into t_zc_pj select '2 ', '主司机 ', '副司机 ',15,30
create table t_zc_xl --支出费用(修理表)
(
btbm varchar(50), --本趟编码
sj_name1 varchar(50),--主司机姓名
sj_name2 varchar(50),--副司机姓名
fy decimal(13,2)--修理费用
)
insert into t_zc_xl select '1 ', '主司机 ', '副司机 ',100
insert into t_zc_xl select '1 ', '主司机 ', '副司机 ',150
insert into t_zc_xl select '2 ', '主司机 ', '副司机 ',100
insert into t_zc_xl select '2 ', '主司机 ', '副司机 ',200
create table t_zc_zf --支出费用(杂费表)
(
btbm varchar(50), --本趟编码
sj_name1 varchar(50),--主司机姓名
sj_name2 varchar(50),--副司机姓名
je decimal(13,2)--杂费金额
)
insert into t_zc_zf select '1 ', '主司机 ', '副司机 ',100
insert into t_zc_zf select '1 ', '主司机 ', '副司机 ',150
insert into t_zc_zf select '2 ', '主司机 ', '副司机 ',100
insert into t_zc_zf select '2 ', '主司机 ', '副司机 ',200
create table t_xclc --行车里程表
(
btbm varchar(50), --本趟编码
sj_name1 varchar(50),--主司机姓名
sj_name2 varchar(50),--副司机姓名
ccdk int --出车带现金数
)
insert into t_xclc select '1 ', '主司机 ', '副司机 ',10000
insert into t_xclc select '2 ', '主司机 ', '副司机 ',8000
/*
要得到每一趟出车结余款的数目
第1趟出车，第一趟结余款的数目＝行车里程表中的第一趟出车带现金数 - (支出费用(罚款表)中的第1趟的金额总和 + 支出费用(加油表)中的第1趟的金额总和 + 支出费用(路桥表)中的第1趟的金额总和 + 支出费用(配件表)中的第1趟的金额总和 + 支出费用(修理表)中的第1趟的金额总和 + 支出费用(杂费表)中的第1趟的金额总和)
即: 第一趟结余款的数目＝10000 - (250 + 250 + 250 + 250 + 250 + 250)=8500
第2趟出车，第2趟结余款的数目＝第一趟结余款的数目 + 行车里程表中的第2趟出车带现金数 - (支出费用(罚款表)中的第2趟的金额总和 + 支出费用(加油表)中的第2趟的金额总和 + 支出费用(路桥表)中的第2趟的金额总和 + 支出费用(配件表)中的第2趟的金额总和 + 支出费用(修理表)中的第2趟的金额总和 + 支出费用(杂费表)中的第2趟的金额总和)
即: 第2趟结余款的数目＝9000+8000 - (300 + 650 + 300 + 650 + 300 + 300)=14000
第3趟出车的结余款按照公式以此类推.......
查询条件按照主，副司机的名字和出车趟数进行查询。
最后的报表格式是这样：
本趟编码 主司机姓名 副司机姓名 上趟款转入 出车带现金 合计 本趟支出合计 结余
1 主司机 副司机 0 10000 10000 1500 8500
2 主司机 副司机 8500 8000 16500 2500 14000
*/
drop table t_zc_fk
drop table t_zc_jy
drop table t_zc_lq
drop table t_zc_pj
drop table t_zc_xl
drop table t_zc_zf
drop table t_xclc
[解决办法]
create table t_zc_fk --支出费用(罚款表)
(
btbm varchar(50), --本趟编码
sj_name1 varchar(50),--主司机姓名
sj_name2 varchar(50),--副司机姓名
je decimal(13,2)--罚款金额
)
insert into t_zc_fk select '1 ', '主司机 ', '副司机 ',100
insert into t_zc_fk select '1 ', '主司机 ', '副司机 ',150
insert into t_zc_fk select '2 ', '主司机 ', '副司机 ',100
insert into t_zc_fk select '2 ', '主司机 ', '副司机 ',200
create table t_zc_jy --支出费用(加油表)
(
btbm varchar(50), --本趟编码
sj_name1 varchar(50),--主司机姓名
sj_name2 varchar(50),--副司机姓名
dj decimal(13,2),--汽油单价
ss int --加油升数
)
insert into t_zc_jy select '1 ', '主司机 ', '副司机 ',10,10
insert into t_zc_jy select '1 ', '主司机 ', '副司机 ',15,10
insert into t_zc_jy select '2 ', '主司机 ', '副司机 ',10,20
insert into t_zc_jy select '2 ', '主司机 ', '副司机 ',15,30
create table t_zc_lq --支出费用(路桥表)
(
btbm varchar(50), --本趟编码
sj_name1 varchar(50),--主司机姓名
sj_name2 varchar(50),--副司机姓名
je decimal(13,2)--过路费金额
)
insert into t_zc_lq select '1 ', '主司机 ', '副司机 ',100
insert into t_zc_lq select '1 ', '主司机 ', '副司机 ',150
insert into t_zc_lq select '2 ', '主司机 ', '副司机 ',100
insert into t_zc_lq select '2 ', '主司机 ', '副司机 ',200
create table t_zc_pj --支出费用(配件表)
(
btbm varchar(50), --本趟编码
sj_name1 varchar(50),--主司机姓名
sj_name2 varchar(50),--副司机姓名
dj decimal(13,2),--配件单价
sl int --配件数量
)
insert into t_zc_pj select '1 ', '主司机 ', '副司机 ',10,10
insert into t_zc_pj select '1 ', '主司机 ', '副司机 ',15,10
insert into t_zc_pj select '2 ', '主司机 ', '副司机 ',10,20
insert into t_zc_pj select '2 ', '主司机 ', '副司机 ',15,30
create table t_zc_xl --支出费用(修理表)
(
btbm varchar(50), --本趟编码
sj_name1 varchar(50),--主司机姓名
sj_name2 varchar(50),--副司机姓名
fy decimal(13,2)--修理费用
)
insert into t_zc_xl select '1 ', '主司机 ', '副司机 ',100
insert into t_zc_xl select '1 ', '主司机 ', '副司机 ',150
insert into t_zc_xl select '2 ', '主司机 ', '副司机 ',100
insert into t_zc_xl select '2 ', '主司机 ', '副司机 ',200
create table t_zc_zf --支出费用(杂费表)
(
btbm varchar(50), --本趟编码
sj_name1 varchar(50),--主司机姓名
sj_name2 varchar(50),--副司机姓名
je decimal(13,2)--杂费金额
)
insert into t_zc_zf select '1 ', '主司机 ', '副司机 ',100
insert into t_zc_zf select '1 ', '主司机 ', '副司机 ',150
insert into t_zc_zf select '2 ', '主司机 ', '副司机 ',100
insert into t_zc_zf select '2 ', '主司机 ', '副司机 ',200
create table t_xclc --行车里程表
(
btbm varchar(50), --本趟编码
sj_name1 varchar(50),--主司机姓名
sj_name2 varchar(50),--副司机姓名
ccdk int --出车带现金数
)
insert into t_xclc select '1 ', '主司机 ', '副司机 ',10000
insert into t_xclc select '2 ', '主司机 ', '副司机 ',8000
/*
要得到每一趟出车结余款的数目
第1趟出车，第一趟结余款的数目＝行车里程表中的第一趟出车带现金数 - (支出费用(罚款表)中的第1趟的金额总和 + 支出费用(加油表)中的第1趟的金额总和 + 支出费用(路桥表)中的第1趟的金额总和 + 支出费用(配件表)中的第1趟的金额总和 + 支出费用(修理表)中的第1趟的金额总和 + 支出费用(杂费表)中的第1趟的金额总和)
即: 第一趟结余款的数目＝10000 - (250 + 250 + 250 + 250 + 250 + 250)=9000
第2趟出车，第2趟结余款的数目＝第一趟结余款的数目 + 行车里程表中的第2趟出车带现金数 - (支出费用(罚款表)中的第2趟的金额总和 + 支出费用(加油表)中的第2趟的金额总和 + 支出费用(路桥表)中的第2趟的金额总和 + 支出费用(配件表)中的第2趟的金额总和 + 支出费用(修理表)中的第2趟的金额总和 + 支出费用(杂费表)中的第2趟的金额总和)
即: 第2趟结余款的数目＝9000+8000 - (300 + 650 + 300 + 650 + 300 + 300)=14500
第3趟出车的结余款按照公式以此类推.......
查询条件按照主，副司机的名字和出车趟数进行查询。
*/
go
create function fnLastLeavings(@btbm varchar(50), @sj_name1 varchar(50), @sj_name2 varchar(50))
returns decimal(13,2) as
begin
if cast(@btbm as int) = 1
return 0.0
declare @LastLeavings decimal(13,2),@nbtbm int
set @nbtbm = cast(@btbm as int) - 1
select @LastLeavings=sum(je) from
(
select je=-1.0*je from t_zc_fk where sj_name1=@sj_name1 and sj_name2=@sj_name2 and cast(btbm as int) <=@nbtbm union all
select je=-1.0*dj*ss from t_zc_jy where sj_name1=@sj_name1 and sj_name2=@sj_name2 and cast(btbm as int) <=@nbtbm union all
select je=-1.0*je from t_zc_lq where sj_name1=@sj_name1 and sj_name2=@sj_name2 and cast(btbm as int) <=@nbtbm union all
select je=-1.0*dj*sl from t_zc_pj where sj_name1=@sj_name1 and sj_name2=@sj_name2 and cast(btbm as int) <=@nbtbm union all
select je=-1.0*fy from t_zc_xl where sj_name1=@sj_name1 and sj_name2=@sj_name2 and cast(btbm as int) <=@nbtbm union all
select je=-1.0*je from t_zc_zf where sj_name1=@sj_name1 and sj_name2=@sj_name2 and cast(btbm as int) <=@nbtbm union all
select je=1.0*ccdk from t_xclc where sj_name1=@sj_name1 and sj_name2=@sj_name2 and cast(btbm as int) <=@nbtbm
) as tb
return @LastLeavings
end
go
create function fnCost(@btbm varchar(50), @sj_name1 varchar(50), @sj_name2 varchar(50))
returns decimal(13,2) as
begin
declare @Cost decimal(13,2)
select @Cost=sum(je) from
(
select je=1.0*je from t_zc_fk where sj_name1=@sj_name1 and sj_name2=@sj_name2 and btbm=@btbm union all
select je=1.0*dj*ss from t_zc_jy where sj_name1=@sj_name1 and sj_name2=@sj_name2 and btbm=@btbm union all
select je=1.0*je from t_zc_lq where sj_name1=@sj_name1 and sj_name2=@sj_name2 and btbm=@btbm union all
select je=1.0*dj*sl from t_zc_pj where sj_name1=@sj_name1 and sj_name2=@sj_name2 and btbm=@btbm union all
select je=1.0*fy from t_zc_xl where sj_name1=@sj_name1 and sj_name2=@sj_name2 and btbm=@btbm union all
select je=1.0*je from t_zc_zf where sj_name1=@sj_name1 and sj_name2=@sj_name2 and btbm=@btbm
) as tb
return @Cost
end
go
select 本趟编码=btbm, 主司机姓名=sj_name1, 副司机姓名=sj_name2, 上趟款转入=dbo.fnLastLeavings(btbm, sj_name1, sj_name2), 出车带现金=ccdk, 合计=dbo.fnLastLeavings(btbm, sj_name1, sj_name2) + ccdk, 本趟支出合计=dbo.fnCost(btbm, sj_name1, sj_name2), 结余=dbo.fnLastLeavings(btbm, sj_name1, sj_name2) + ccdk - dbo.fnCost(btbm, sj_name1, sj_name2)
from t_xclc
drop function fnLastLeavings
drop function fnCost
drop table t_zc_fk
drop table t_zc_jy
drop table t_zc_lq
drop table t_zc_pj
drop table t_zc_xl
drop table t_zc_zf
drop table t_xclc
[解决办法]
declare @t_zc_fk table--支出费用(罚款表)
(
btbm varchar(50), --本趟编码
sj_name1 varchar(50),--主司机姓名
sj_name2 varchar(50),--副司机姓名
je decimal(13,2)--罚款金额
)
insert into @t_zc_fk select '1 ', '主司机 ', '副司机 ',100
insert into @t_zc_fk select '1 ', '主司机 ', '副司机 ',150
insert into @t_zc_fk select '2 ', '主司机 ', '副司机 ',100
insert into @t_zc_fk select '2 ', '主司机 ', '副司机 ',200
declare @t_zc_jy table--支出费用(加油表)
(
btbm varchar(50), --本趟编码
sj_name1 varchar(50),--主司机姓名
sj_name2 varchar(50),--副司机姓名
dj decimal(13,2),--汽油单价
ss int --加油升数
)
insert into @t_zc_jy select '1 ', '主司机 ', '副司机 ',10,10
insert into @t_zc_jy select '1 ', '主司机 ', '副司机 ',15,10
insert into @t_zc_jy select '2 ', '主司机 ', '副司机 ',10,20
insert into @t_zc_jy select '2 ', '主司机 ', '副司机 ',15,30
declare @t_zc_lq table--支出费用(路桥表)
(
btbm varchar(50), --本趟编码
sj_name1 varchar(50),--主司机姓名
sj_name2 varchar(50),--副司机姓名
je decimal(13,2)--过路费金额
)
insert into @t_zc_lq select '1 ', '主司机 ', '副司机 ',100
insert into @t_zc_lq select '1 ', '主司机 ', '副司机 ',150
insert into @t_zc_lq select '2 ', '主司机 ', '副司机 ',100
insert into @t_zc_lq select '2 ', '主司机 ', '副司机 ',200
declare @t_zc_pj table--支出费用(配件表)
(
btbm varchar(50), --本趟编码
sj_name1 varchar(50),--主司机姓名
sj_name2 varchar(50),--副司机姓名
dj decimal(13,2),--配件单价
sl int --配件数量
)
insert into @t_zc_pj select '1 ', '主司机 ', '副司机 ',10,10
insert into @t_zc_pj select '1 ', '主司机 ', '副司机 ',15,10
insert into @t_zc_pj select '2 ', '主司机 ', '副司机 ',10,20
insert into @t_zc_pj select '2 ', '主司机 ', '副司机 ',15,30
declare @t_zc_xl table--支出费用(修理表)
(
btbm varchar(50), --本趟编码
sj_name1 varchar(50),--主司机姓名
sj_name2 varchar(50),--副司机姓名
fy decimal(13,2)--修理费用
)
insert into @t_zc_xl select '1 ', '主司机 ', '副司机 ',100
insert into @t_zc_xl select '1 ', '主司机 ', '副司机 ',150
insert into @t_zc_xl select '2 ', '主司机 ', '副司机 ',100
insert into @t_zc_xl select '2 ', '主司机 ', '副司机 ',200
declare @t_zc_zf table--支出费用(杂费表)
(
btbm varchar(50), --本趟编码
sj_name1 varchar(50),--主司机姓名
sj_name2 varchar(50),--副司机姓名
je decimal(13,2)--杂费金额
)
insert into @t_zc_zf select '1 ', '主司机 ', '副司机 ',100
insert into @t_zc_zf select '1 ', '主司机 ', '副司机 ',150
insert into @t_zc_zf select '2 ', '主司机 ', '副司机 ',100
insert into @t_zc_zf select '2 ', '主司机 ', '副司机 ',200
declare @t_xclc table--行车里程表
(
btbm varchar(50), --本趟编码
sj_name1 varchar(50),--主司机姓名
sj_name2 varchar(50),--副司机姓名
ccdk int --出车带现金数
)
insert into @t_xclc select '1 ', '主司机 ', '副司机 ',10000
insert into @t_xclc select '2 ', '主司机 ', '副司机 ',8000
/*
要得到每一趟出车结余款的数目
第1趟出车，第一趟结余款的数目＝行车里程表中的第一趟出车带现金数 - (支出费用(罚款表)中的第1趟的金额总和 + 支出费用(加油表)中的第1趟的金额总和 + 支出费用(路桥表)中的第1趟的金额总和 + 支出费用(配件表)中的第1趟的金额总和 + 支出费用(修理表)中的第1趟的金额总和 + 支出费用(杂费表)中的第1趟的金额总和)
即: 第一趟结余款的数目＝10000 - (250 + 250 + 250 + 250 + 250 + 250)=8500
第2 趟出车，第2趟结余款的数目＝第一趟结余款的数目 + 行车里程表中的第2趟出车带现金数 - (支出费用(罚款表)中的第2趟的金额总和 + 支出费用(加油表)中的第2趟的金额总和 + 支出费用(路桥表)中的第2趟的金额总和 + 支出费用(配件表)中的第2趟的金额总和 + 支出费用(修理表)中的第2趟的金额总和 + 支出费用(杂费表)中的第2趟的金额总和)
即: 第2趟结余款的数目＝9000+8000 - (300 + 650 + 300 + 650 + 300 + 300)=14000
第3趟出车的结余款按照公式以此类推.......
查询条件按照主，副司机的名字和出车趟数进行查询。
最后的报表格式是这样：
本趟编码 主司机姓名 副司机姓名 上趟款转入 出车带现金 合计 本趟支出合计 结余
1 主司机 副司机 0 10000 10000 1500 8500
2 主司机 副司机 8500 8000 16500 2500 14000
*/
select
本趟编码=a.btbm,
主司机姓名=a.sj_name1,
副司机姓名=a.sj_name2,
上趟款转入=
(select isnull(sum(ccdk),0) from @t_xclc where btbm <a.btbm)-
(select isnull(sum(je),0) from @t_zc_zf where btbm <a.btbm)-
(select isnull(sum(fy),0) from @t_zc_xl where btbm <a.btbm)-
(select isnull(sum(dj*sl),0) from @t_zc_pj where btbm <a.btbm)-
(select isnull(sum(je),0) from @t_zc_lq where btbm <a.btbm)-
(select isnull(sum(dj*ss),0) from @t_zc_jy where btbm <a.btbm)-
(select isnull(sum(je),0) from @t_zc_fk where btbm <a.btbm),
出车带现金=(select ccdk from @t_xclc where btbm=a.btbm),
合计=
(select ccdk from @t_xclc where btbm=a.btbm)+
(select isnull(sum(ccdk),0) from @t_xclc where btbm <a.btbm)-
(select isnull(sum(je),0) from @t_zc_zf where btbm <a.btbm)-
(select isnull(sum(fy),0) from @t_zc_xl where btbm <a.btbm)-
(select isnull(sum(dj*sl),0) from @t_zc_pj where btbm <a.btbm)-
(select isnull(sum(je),0) from @t_zc_lq where btbm <a.btbm)-
(select isnull(sum(dj*ss),0) from @t_zc_jy where btbm <a.btbm)-
(select isnull(sum(je),0) from @t_zc_fk where btbm <a.btbm),
本趟支出合计=
(select isnull(sum(je),0) from @t_zc_zf where btbm=a.btbm)+
(select isnull(sum(fy),0) from @t_zc_xl where btbm=a.btbm)+
(select isnull(sum(dj*sl),0) from @t_zc_pj where btbm=a.btbm)+
(select isnull(sum(je),0) from @t_zc_lq where btbm=a.btbm)+
(select isnull(sum(dj*ss),0) from @t_zc_jy where btbm=a.btbm)+
(select isnull(sum(je),0) from @t_zc_fk where btbm=a.btbm),
结余=
(select isnull(sum(ccdk),0) from @t_xclc where btbm <=a.btbm)-
(select isnull(sum(je),0) from @t_zc_zf where btbm <=a.btbm)-
(select isnull(sum(fy),0) from @t_zc_xl where btbm <=a.btbm)-
(select isnull(sum(dj*sl),0) from @t_zc_pj where btbm <=a.btbm)-
(select isnull(sum(je),0) from @t_zc_lq where btbm <=a.btbm)-
(select isnull(sum(dj*ss),0) from @t_zc_jy where btbm <=a.btbm)-
(select isnull(sum(je),0) from @t_zc_fk where btbm <=a.btbm)
from @t_xclc a
left join @t_zc_zf b
on a.btbm=b.btbm
left join @t_zc_xl c
on a.btbm=c.btbm
left join @t_zc_pj d
on a.btbm=d.btbm
left join @t_zc_lq e
on a.btbm=e.btbm
left join @t_zc_jy f
on a.btbm=f.btbm
left join @t_zc_fk g
on a.btbm=g.btbm
group by a.btbm,a.sj_name1,a.sj_name2
/*
本趟编码主司机姓名副司机姓名上趟款转入出车带现金合计本趟支出合计结余
1主司机副司机.001000010000.001500.008500.00
2主司机副司机8500.00800016500.002500.0014000.00
*/
