主表tbM:
sfid(身份证),xm(姓名),type(类型);
补助标准表tbMax:
type(类型),MaxBz(年度补助最高限额)
补助表tbBz:
sfid(身份证号),bzsj(补助实施时间),bzje(补助金额)
求:
给定身份证号Asfid,该人员当年还可享受的补助。
可用一条SQL语句求解,或使用自定义的SQL函数。
sfid(身份证),xm(姓名),type(类型);
补助标准表tbMax:
type(类型),MaxBz(年度补助最高限额)
补助表tbBz:
sfid(身份证号),bzsj(补助实施时间),bzje(补助金额)
求:
给定身份证号Asfid,该人员当年还可享受的补助。
可用一条SQL语句求解,或使用自定义的SQL函数。
from tbM a
inner join tbMax b
on a.[type] = b.[type]
left join
(
select sfid, bzje = SUM(bzje)
from tbBz where DATEDIFF(year, bzsj, GETDATE()) = 0
group by sfid
) c
on a.sfid = c.sfid
select a.*, b.MaxBz, 当年还可享受的补助 = b.MaxBz - c.bzje
from tbM a
inner join tbMax b
on a.[type] = b.[type]
left join
(
select sfid, bzje = SUM(bzje)
from tbBz where DATEDIFF(year, bzsj, GETDATE()) = 0
group by sfid
) c
on a.sfid = c.sfid
where a.sfid = '130xxxxxxxxxxx'
b.MaxBz - c.bzje as 当年还可享受的补助
from
tbm a, tbmax b,
select afid,sum(bzje) as bzje from tbBz where datediff(yy,bzsj,getdate())=0 group by sfid)c
where
a.[type] = b.[type]
and
a.sfid = c.sfid
and
a.sfid='Asfid'
isnull((b.MaxBz - c.bzje),0) as 当年还可享受的补助
from
tbm a, tbmax b,
select afid,sum(bzje) as bzje from tbBz where datediff(yy,bzsj,getdate())=0 group by sfid)c
where
a.[type] = b.[type]
and
a.sfid = c.sfid
and
a.sfid='Asfid'
select
isnull((b.MaxBz - c.bzje),0) as 当年还可享受的补助
from
tbm a, tbmax b,
(select afid,sum(bzje) as bzje from tbBz where datediff(yy,bzsj,getdate())=0 group by sfid)c
where
a.[type] = b.[type]
and
a.sfid = c.sfid
and
a.sfid='Asfid'
tbm a join tbmax b on a.type=b.type
join tbbz c on a.sfid=c.fid
tbm a join tbmax b on a.type=b.type
join tbbz c on a.sfid=c.fid