请教一个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)