如何在SQL Server中的表值函数中使用CTE语句

I have come to understand that some versions of Microsoft OLE DB Provider for SQL Server (mostly on Windows XP) do not support WITH statement. So, I decided to move my SQL statement into a table-valued function, and call it from my application. Now, I'm stuck. How should I use the INSERT INTO statement with WITH? Here's the code I have come with so far, but SQL Server doesn't like it... :-(

我已经了解到某些版本的Microsoft OLE DB Provider for SQL Server（主要在Windows XP上）不支持WITH语句。所以，我决定将我的SQL语句移动到表值函数中，并从我的应用程序中调用它。现在，我被困住了。我应该如何在WITH中使用INSERT INTO语句？这是我到目前为止的代码，但SQL Server不喜欢它... :-(

CREATE FUNCTION GetDistributionTable 
(
    @IntID int,
    @TestID int,
    @DateFrom datetime,
    @DateTo datetime
)
RETURNS 
@Table_Var TABLE 
(
    [Count] int, 
    Result float
)
AS
BEGIN
INSERT INTO @Table_Var ([Count], Result) WITH T(Result)
     AS (SELECT ROUND(Result - AVG(Result) OVER(), 1)
         FROM RawResults WHERE IntID = @IntID AND DBTestID = @TestID AND Time >= @DateFrom AND Time <= @DateTo)
SELECT COUNT(*) AS [Count],
       Result
FROM   T
GROUP  BY Result

    RETURN 
END
GO

2 个解决方案

#1

You can omit the CTE (WITH statement), and instead create an inline table valued function that uses subquery:

您可以省略CTE（WITH语句），而是创建一个使用子查询的内联表值函数：

CREATE FUNCTION GetDistributionTable 
(
    @IntID int,
    @TestID int,
    @DateFrom datetime,
    @DateTo datetime
)
RETURNS TABLE
AS
RETURN  
(
    SELECT  COUNT(*) AS [Count],
            Result
    FROM    (
                 SELECT ROUND(Result - AVG(Result) OVER(), 1) Result
                 FROM   RawResults 
                 WHERE  IntID = @IntID 
                 AND    DBTestID = @TestID 
                 AND    Time >= @DateFrom 
                 AND Time <= @DateTo    
    ) t
    GROUP  BY 
            Result
)
GO

Syntax for the CTE in table valued function would be:

表值函数中CTE的语法将是：

CREATE FUNCTION GetDistributionTable 
(
    @IntID int,
    @TestID int,
    @DateFrom datetime,
    @DateTo datetime
)
RETURNS TABLE
AS
RETURN  
(
    WITH cte AS
    (
        SELECT ROUND(Result - AVG(Result) OVER(), 1) Result
        FROM   RawResults 
        WHERE  IntID = @IntID 
        AND    DBTestID = @TestID 
        AND    Time >= @DateFrom 
        AND Time <= @DateTo    
    )

    SELECT  COUNT(*) AS [Count],
            Result
    FROM    cte
    GROUP  BY 
            Result
)
GO

Your example seems to be using a multi-statement TVF (insert and select), when you have a choice try using the inline TVF because the multi-statement TVF can prevent query optimizer in choosing a better execution plan (performance difference explained here)

您的示例似乎是使用多语句TVF（插入和选择），当您有选择尝试使用内联TVF时，因为多语句TVF可以阻止查询优化器选择更好的执行计划（此处说明的性能差异）

#2

LIKE THIS..

喜欢这个..

CREATE FUNCTION GetDistributionTable 
(
    @IntID int,
    @TestID int,
    @DateFrom datetime,
    @DateTo datetime
)
RETURNS 
@Table_Var TABLE 
(
    [Count] int, 
    Result float
)
AS
BEGIN
  WITH T 
    AS (    
        select Ticket_Id,COUNT(1) Result from 
        Customer_Survey
        group by MemberID,SiteId,Ticket_Id
   )
  INSERT INTO @Table_Var ([Count], Result)
  SELECT COUNT(*) AS [Count],
       Result
  FROM   T
  GROUP  BY Result
  RETURN 
END
GO

#1