请教一个Function
医药系统下4个table:
Medicine
MID Mcname
1頭孢拉定膠囊
2頭孢氨苄膠囊
4左氧氟沙星片1
6硝酸甘油
SITE
SiteID Sitename
1園區衛生所
2F1保健站
3臨時宿舍區保健站
Instore
SiteID MID Innum
1 2 19.12
1 3 16.00
1 9 10.00
1 6 6.75
1 12 10.00
1 6 19.50
1 7 34.00
1 2 25.75
2 1 14.00
3 8 17.00
Outstore
SiteID MID OutNum
110.08
110.13
120.50
111.00
161.00
220.50
1170.50
现需做一个function ,根據SiteID和MID,返回不同工作區的某药品的库存量(入库减去出库的数量)
[解决办法]
create function fn(SiteID int,MID int)
returns numeric(18,2)
as
begin
return (
select sum(Store) from (
select Innum as Store from Instore where SiteID=@SiteID and MID=@MID
union all
select -OutNum as Store from Outstore where SiteID=@SiteID and MID=@MID
) as t
)
end
go
[解决办法]
create function dbo.aa(@SiteID int, @MID int )
returns @t table (Sitename varchar(20),Mcname varchar(100),qty int)
as
begin
insert into @t
select b.Sitename,a.Mcname,c.qty
from
(
select a.SiteID ,a.MID, sum(a.Innum-isnull(b.OutNum,0))as qty from Instore a left join Outstore b
on a.SiteID =b.SiteID and a.MID=b.MID
where a.SiteID =@SiteID and a.MID=@MID
group by a.SiteID ,a.MID
)c
left join SITE b on c.SiteID =b.SiteID
left join Medicine a on c.MID=a.Medicine
return
end
select * from dbo.aa(1,2)
