请教一个Function
医药系统下4个table:
Medicine

MID           Mcname
1 頭孢拉定膠囊
2 頭孢氨苄膠囊
4 左氧氟沙星片1
6 硝酸甘油

SITE

SiteID     Sitename
1 園區衛生所
2 F1保健站
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
1 1 0.08
1 1 0.13
1 2 0.50
1 1 1.00
1 6 1.00
2 2 0.50
1 17 0.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)