CREATE FUNCTION BOOK_PROFIT(@year int)
RETURNS @tb_book_profit TABLE(书号 varchar(50), 销售总额 int)
AS
BEGIN
INSERT INTO @tb_book_profit
(
SELECT 书号, SUM(单价*销售数量) AS 销售总额
FROM 销售表 X JOIN 图书表 T
ON X.书号 = T.书号
WHERE X.销售时间 = @year
GROUP BY X.书号#2书号
)
RETURN#1
END
实操:
- 加上RETURN
- 书号指定为X表中
CREATE FUNCTION DBO.BOOK_PROFIT(@year int)
RETURNS @tb_book_profit TABLE(书号 varchar(50), 销售总额 int)
AS
BEGIN
INSERT @tb_book_profit
SELECT x.no as 书号, SUM(cost*quantity) AS 销售总额
FROM sell X JOIN commodity T
ON X.no = T.no
WHERE X.time = @year
GROUP BY X.no
RETURN
END
SELECT * FROM DBO.BOOK_PROFIT(11) RESULT
GO
利用SQL SERVER及自建表演练: