問題描述

我有一些復雜的函數，我想在多個查詢中使用它們.它獲取一些值列表并返回聚合值.

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

但我收到一個錯誤:操作數類型沖突:浮點數與數字不兼容

But I get an error: Operand type clash: float is incompatible with Numbers

我知道我可以使用游標或將值作為 XML 傳遞，但我認為這種方式比內聯函數和表變量慢.

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.

如何將值列表傳遞給內聯函數?

How can I pass a list of values to inline function?

推薦答案

首先你應該使用 Inline 函數中使用的 table type(Number) 創建一個 table 變量.

First you should create a table variable using the table type(Number) used in the Inline function.

將需要的行插入表變量并傳遞表變量 o 內聯函數

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)

更新:根據您的評論

創建一個新的表格類型.

CREATE TYPE Dept_number AS TABLE
(
DepatmentId  INT ,value numeric(22,6)
);

Alter 函數

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) 

調用函數

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) 

這篇關于如何將用戶定義的表類型傳遞給內聯函數的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！