sql参数化cte查询

Question

我有一个如下所示的查询

select  * 
from        (
               select   *
               from     callTableFunction(@paramPrev)
                .....< a whole load of other joins, wheres , etc >........
            ) prevValues
            full join
            (
                select  *
                from    callTableFunction(@paramCurr)
                .....< a whole load of other joins, wheres , etc >........
            ) currValues                on prevValues.Field1 = currValues.Field1
            ....<other joins with the same subselect as the above two with different parameters passed in
where       ........
group by    ....

以下子选择对于查询栏中的所有子选择都是通用的，@param 到表函数。

        select  *
        from    callTableFunction(@param)
            .....< a whole load of other joins, wheres , etc >........

一种选择是我将其转换为函数并调用该函数，但我不喜欢这个，因为我可能会更改 经常进行子选择查询......或者我想知道是否有使用 CTE 的替代方案 有

with sometable(@param1) as 
(
        select  *
        from    callTableFunction(@param)
                .....< a whole load of other joins, wheres , etc >........
)
select      
        sometable(@paramPrev)       prevValues
        full join sometable(@currPrev)  currValues  on prevValues.Field1 = currValues.Field1
where       ........
group by    ....

没有像这样的语法或我可以这样使用的技术。

这是 SQL Server 2008 R2 中的，

谢谢。

Answer 1

您尝试执行的语法不受支持 - CTE 无法以这种方式参数化。

在线查看书籍 - http://msdn.microsoft.com/en-us/library /ms175972.aspx。

（CTE 名称后面括号中的值是输出列名称的可选列表）

如果只有两个参数值（paramPrev 和 currPrev），您也许可以通过将代码分成两个 CTE，代码更容易阅读 - 如下所示：

with prevCTE as  (
          select  *
          from    callTableFunction(@paramPrev)
                  .....< a whole load of other joins, wheres , etc 
 ........ )
,curCTE as  (
          select  *
          from    callTableFunction(@currPrev)
                  .....< a whole load of other joins, wheres , etc 
 ........ ),
 select      
          prevCTE       prevValues
          full join curCTE  currValues  on 
 prevValues.Field1 = currValues.Field1 where 
 ........ group by   
 ....

Answer 2

您应该能够将子查询包装为参数化内联表值函数，然后将它们与 OUTER JOIN 一起使用：

CREATE FUNCTION wrapped_subquery(@param int) -- assuming it's an int type, change if necessary...
RETURNS TABLE
RETURN
    SELECT * FROM callTableFunction(@param)
    .....< a whole load of other joins, wheres , etc ........
GO

SELECT *
FROM
    wrapped_subquery(@paramPrev) prevValues
        FULL OUTER JOIN wrapped_subquery(@currPrev) currValues ON prevValues.Field1 = currValues.Field1
WHERE       ........
GROUP BY    ....

Answer 3

在 with 之前分配标量变量失败后，我终于得到了一个使用存储过程和临时表的工作解决方案：

create proc hours_absent(@wid nvarchar(30), @start date, @end date)
as
with T1 as(
  select c from t
),
T2 as(
  select c from T1
)
select c from T2
order by 1, 2
OPTION(MAXRECURSION 365)

调用存储过程：

if object_id('tempdb..#t') is not null drop table #t

create table #t([month] date, hours float)
insert into #t exec hours_absent '9001', '2014-01-01', '2015-01-01'

select * from #t

Answer 4

我的示例与您想要的之间可能存在一些差异，具体取决于您后续 ON 语句的制定方式。由于您没有指定，我假设所有后续连接都针对第一个表。
在我的示例中，我使用文字而不是 @prev、@current，但您可以轻松地用变量代替文字来实现您想要的效果。

-- Standin function for your table function to create working example.
CREATE FUNCTION TestMe(
    @parm int)
RETURNS TABLE
AS
RETURN
    (SELECT @parm AS N, 'a' AS V UNION ALL
     SELECT @parm + 1,  'b'      UNION ALL
     SELECT @parm + 2,  'c'      UNION ALL
     SELECT @parm + 2,  'd'      UNION ALL
     SELECT @parm + 3,  'e');
go         
-- This calls TestMe first with 2 then 4 then 6... (what you don't want)
-- Compare these results with those below
SELECT t1.N AS AN, t1.V as AV,
       t2.N AS BN, t2.V as BV,
       t3.N AS CN, t3.V as CV
  FROM TestMe(2)AS t1
  FULL JOIN TestMe(4)AS t2 ON t1.N = t2.N
  FULL JOIN TestMe(6)AS t3 ON t1.N = t3.N;

-- Put your @vars in place of 2,4,6 adding select statements as needed
WITH params
    AS (SELECT 2 AS p UNION ALL
        SELECT 4 AS p UNION ALL
        SELECT 6 AS p)
    -- This CTE encapsulates the call to TestMe (and any other joins)
    ,AllData
    AS (SELECT *
          FROM params AS p
          OUTER APPLY TestMe(p.p))  -- See! only coded once
          -- Add any other necessary joins here

    -- Select needs to deal with all the columns with identical names
    SELECT d1.N AS AN, d1.V as AV,
           d2.N AS BN, d2.V as BV,
           d3.N AS CN, d3.V as CV
      -- d1 gets limited to values where p = 2 in the where clause below
      FROM AllData AS d1
      -- Outer joins require the ANDs to restrict row multiplication
      FULL JOIN AllData AS d2 ON d1.N = d2.N
                             AND d1.p = 2 AND d2.p = 4
      FULL JOIN AllData AS d3 ON d1.N = d3.N
                             AND d1.p = 2 AND d2.p = 4 AND d3.p = 6
      -- Since AllData actually contains all the rows we must limit the results
      WHERE(d1.p = 2 OR d1.p IS NULL)
       AND (d2.p = 4 OR d2.p IS NULL)
       AND (d3.p = 6 OR d3.p IS NULL);

您想要做的事情类似于数据透视，因此所需查询的复杂性类似于在不使用数据透视语句的情况下创建数据透视结果。
如果您使用数据透视表，重复的行（例如我在本示例中包含的行）将被聚合。这也是在不需要聚合的情况下进行数据透视的解决方案。