問題描述
我有一些復雜的函數(shù)，我想在多個查詢中使用它們.它獲取一些值列表并返回聚合值.
I have some complex function that I want to use in number of queries. It gets some list of values and return aggregate value.
例如(我簡化了，實際上更復雜):
For example (I simplify it, it is more complex in deed):
CREATE FUNCTION Mean(@N Numbers READONLY)
RETURNS TABLE AS RETURN (
SELECT mean = SUM(n.value) / COUNT(*) FROM @N n
)
我想在查詢中使用它:
SELECT d.DepartmentName, MeanRate = m.mean
FROM Departments d
CROSS APPLY Mean(
(
SELECT value = e.Rate
FROM Employees e
WHERE e.DepatmentId = d.DepatmentId
)
) m
但我收到一個錯誤:操作數(shù)類型沖突:浮點數(shù)與數(shù)字不兼容
But I get an error: Operand type clash: float is incompatible with Numbers
我知道我可以使用游標或將值作為 XML 傳遞，但我認為這種方式比內聯(lián)函數(shù)和表變量慢.
I know that I can use cursor or pass values as XML, but I think this ways are slower than inline function and table variables.
如何將值列表傳遞給內聯(lián)函數(shù)?
How can I pass a list of values to inline function?
推薦答案
首先你應該使用 Inline 函數(shù)中使用的 table type(Number) 創(chuàng)建一個 table 變量.
First you should create a table variable using the table type(Number) used in the Inline function.
將需要的行插入表變量并傳遞表變量 o 內聯(lián)函數(shù)
Insert the required rows into table variable and pass the table variable o Inline function
你需要做這樣的事情
declare @Numbers Numbers
Insert into @Numbers
select e.Rate
From Employees E join
Departments d on e.DepatmentId = d.DepatmentId
select * from Mean(@Numbers)
更新:根據(jù)您的評論
創(chuàng)建一個新的表格類型.
CREATE TYPE Dept_number AS TABLE
(
DepatmentId INT ,value numeric(22,6)
);
Alter 函數(shù)
ALTER FUNCTION Mean(@dept_number DEPT_NUMBER readonly)
returns TABLE
AS
RETURN
(SELECT depatmentid,
mean = Sum(n.value) / Count(*)
FROM @dept_number n
GROUP BY depatmentid)
調用函數(shù)
DECLARE @dept_number DEPT_NUMBER
INSERT INTO @dept_number
(depatmentid,
value)
SELECT d.depatmentid,
e.rate
FROM employees E
JOIN departments d
ON e.depatmentid = d.depatmentid
SELECT *
FROM Mean(@dept_number)
這篇關于如何將用戶定義的表類型傳遞給內聯(lián)函數(shù)的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！