求一个得出结余款的SQL写法？
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趟出车的结余款按照公式以此类推.......
查询条件按照主，副司机的名字和出车趟数进行查询。
*/
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
[解决办法]
先写个单次结余的：
select
a.btbm,a.sj_name1,a.sj_name2,
a.ccdk-isnull(b.fee,0)
-isnull(c.fee,0)
-isnull(d.fee,0)
-isnull(e.fee,0)
-isnull(f.fee,0)
-isnull(g.fee,0) as 结余
from
t_xclc a
left join (select btbm,sj_name1,sj_name2,sum(je) as fee from t_zc_fk group by btbm,sj_name1,sj_name2) b on a.btbm=b.btbm and a.sj_name1=b.sj_name1 and a.sj_name2=b.sj_name2
left join (select btbm,sj_name1,sj_name2,sum(dj*ss) as fee from t_zc_jy group by btbm,sj_name1,sj_name2) c on a.btbm=c.btbm and a.sj_name1=c.sj_name1 and a.sj_name2=c.sj_name2
left join (select btbm,sj_name1,sj_name2,sum(je) as fee from t_zc_lq group by btbm,sj_name1,sj_name2) d on a.btbm=d.btbm and a.sj_name1=d.sj_name1 and a.sj_name2=d.sj_name2
left join (select btbm,sj_name1,sj_name2,sum(dj*sl) as fee from t_zc_pj group by btbm,sj_name1,sj_name2) e on a.btbm=e.btbm and a.sj_name1=e.sj_name1 and a.sj_name2=e.sj_name2
left join (select btbm,sj_name1,sj_name2,sum(fy) as fee from t_zc_xl group by btbm,sj_name1,sj_name2) f on a.btbm=f.btbm and a.sj_name1=f.sj_name1 and a.sj_name2=f.sj_name2
left join (select btbm,sj_name1,sj_name2,sum(je) as fee from t_zc_zf group by btbm,sj_name1,sj_name2) g on a.btbm=g.btbm and a.sj_name1=g.sj_name1 and a.sj_name2=g.sj_name2
order by
a.btbm,a.sj_name1,a.sj_name2
[解决办法]
declare @sj_name1 varchar(50),--主司机姓名
@sj_name2 varchar(50),--副司机姓名
@btbm varchar(50) --本趟编码
select @sj_name1= '主司机 ',@sj_name2= '副司机 ',@btbm= '1 '
select 余额=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) <=cast(@btbm as int) 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) <=cast(@btbm as int) 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) <=cast(@btbm as int) 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) <=cast(@btbm as int) 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) <=cast(@btbm as int) 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) <=cast(@btbm as int) 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) <=cast(@btbm as int)
) as tb
select @sj_name1= '主司机 ',@sj_name2= '副司机 ',@btbm= '2 '
select 余额=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) <=cast(@btbm as int) 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) <=cast(@btbm as int) 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) <=cast(@btbm as int) 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) <=cast(@btbm as int) 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) <=cast(@btbm as int) 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) <=cast(@btbm as int) 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) <=cast(@btbm as int)
) as tb
[解决办法]
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)=9000
第2 趟出车，第2趟结余款的数目＝第一趟结余款的数目 + 行车里程表中的第2趟出车带现金数 - (支出费用(罚款表)中的第2趟的金额总和 + 支出费用(加油表)中的第2趟的金额总和 + 支出费用(路桥表)中的第2趟的金额总和 + 支出费用(配件表)中的第2趟的金额总和 + 支出费用(修理表)中的第2趟的金额总和 + 支出费用(杂费表)中的第2趟的金额总和)
即: 第2趟结余款的数目＝9000+8000 - (300 + 650 + 300 + 650 + 300 + 300)=14500
第3趟出车的结余款按照公式以此类推.......
查询条件按照主，副司机的名字和出车趟数进行查询。
*/
select
a.btbm,
结余=a.total-isnull(b.total,0)-isnull(c.total,0)-isnull(d.total,0)-isnull(e.total,0)-isnull(f.total,0)-isnull(g.total,0)
from
(select btbm, total=(select sum(ccdk) from @t_xclc where btbm <=a.btbm) from @t_xclc a group by btbm) a
left join
(select btbm, total=(select sum(je) from @t_zc_zf where btbm <=a.btbm) from @t_zc_zf a group by btbm) b
on a.btbm=b.btbm
left join
(select btbm, total=(select sum(fy) from @t_zc_xl where btbm <=a.btbm) from @t_zc_xl a group by btbm) c
on a.btbm=c.btbm
left join
(select btbm, total=(select sum(dj*sl) from @t_zc_pj where btbm <=a.btbm) from @t_zc_pj a group by btbm) d
on a.btbm=d.btbm
left join
(select btbm, total=(select sum(je) from @t_zc_lq where btbm <=a.btbm) from @t_zc_lq a group by btbm) e
on a.btbm=e.btbm
left join
(select btbm, total=(select sum(dj*ss) from @t_zc_jy where btbm <=a.btbm) from @t_zc_jy a group by btbm) f
on a.btbm=f.btbm
left join
(select btbm, total=(select sum(je) from @t_zc_fk where btbm <=a.btbm) from @t_zc_fk a group by btbm) g
on a.btbm=g.btbm
/*
btbm,结余
1,8500.00
2,14000.00
*/
