数据库sql-170611用户定义函数

Question

drop function f1
go
create function f1(@pname varchar(20))
returns @big table(amt decimal(12,2),per decimal(4,2))
as
begin
insert into @big (amt,per)
select sum(amount) as amt,sum(amount)/(select sum(amount) from orderitems)
as per from orderitems
where productid in(select productid from products where productname=@pname)
return
end

go
select * from dbo.f1('tofu')
有人知道
怎样调用这个用户定义函数，以此检索所有商品的销售额及其占总销售额的百分比？

Answer 1

SELECT productname, amt, per * 100 AS percentage
FROM products
CROSS APPLY dbo.f1(products.productname) AS f