如果在 CTE 内? [英] if else within CTE?

问题描述

我想根据代码在 CTE 中执行 select 语句.类似下面的东西

;带有CTE_AorB(如果(条件)select * from table_A别的select * from table_B),CTE_C 为(select * from CTE_AorB//处理被移除)

但是我在这方面出错了.是否可以在 CTE 中使用 if else?如果没有，是否有解决方法或更好的方法.

谢谢.

解决方案

尝试:

;带有CTE_AorB(select * from table_A WHERE(条件为真)联合所有select * from table_B WHERE NOT(条件为真)),CTE_C 为(select * from CTE_AorB//处理被移除)

动态搜索条件的关键是确保使用索引，这是一篇关于如何处理这个主题的非常全面的文章:

Erland Sommarskog 的 T-SQL 中的动态搜索条件

它涵盖了尝试使用多个可选搜索条件编写查询的所有问题和方法.您需要关注的主要事情不是代码的重复，而是索引的使用.如果您的查询未能使用索引，则它的执行效果会很差.可以使用多种技术，这些技术可能允许也可能不允许使用索引.

这是目录:

<前>介绍案例研究:搜索订单北风数据库动态 SQL介绍使用 sp_executesql使用 CLR使用 EXEC()当缓存不是你真正想要的静态 SQL介绍x = @x 或 @x 为空使用 IF 语句乌玛查达尔的诡计包使用临时表x = @x AND @x 不为空处理复杂情况混合解决方案——同时使用静态和动态 SQL使用视图使用内联表函数结论反馈和致谢修订历史

如果您使用的是正确版本的 SQL Server 2008，则可以使用其他技术，请参阅:SQL 2008(SP1 CU5 及更高版本)的 T-SQL 版本中的动态搜索条件

如果您使用的是 SQL Server 2008 的正确版本，您只需将 OPTION (RECOMPILE) 添加到查询中，并且运行时的局部变量值用于优化.

考虑到这一点，OPTION (RECOMPILE) 将采用此代码(其中没有索引可以用于这种乱七八糟的 OR s):

WHERE(@search1 为 NULL 或 Column1=@Search1)AND(@search2 为 NULL 或 Column2=@Search2)AND(@search3 为 NULL 或 Column3=@Search3)

并在运行时对其进行优化(前提是只有@Search2 传入了一个值):

WHERE列 2=@搜索 2

并且可以使用索引(如果您在 Column2 上定义了一个索引)

I want to execute select statement within CTE based on a codition. something like below

;with CTE_AorB
(
  if(condition)
    select * from table_A
   else
    select * from table_B
),
CTE_C as
(
   select * from CTE_AorB // processing is removed
)

But i get error on this. Is it possible to have if else within CTEs? If not is there a work around Or a better approach.

Thanks.

解决方案

try:

;with CTE_AorB
(
    select * from table_A WHERE (condition true)
    union all
    select * from table_B WHERE NOT (condition true)
),
CTE_C as
(
   select * from CTE_AorB // processing is removed
)

the key with a dynamic search condition is to make sure an index is used, Here is a very comprehensive article on how to handle this topic:

Dynamic Search Conditions in T-SQL by Erland Sommarskog

it covers all the issues and methods of trying to write queries with multiple optional search conditions. This main thing you need to be concerned with is not the duplication of code, but the use of an index. If your query fails to use an index, it will preform poorly. There are several techniques that can be used, which may or may not allow an index to be used.

here is the table of contents:

  Introduction
      The Case Study: Searching Orders
      The Northgale Database
   Dynamic SQL
      Introduction
      Using sp_executesql
      Using the CLR
      Using EXEC()
      When Caching Is Not Really What You Want
   Static SQL
      Introduction
      x = @x OR @x IS NULL
      Using IF statements
      Umachandar's Bag of Tricks
      Using Temp Tables
      x = @x AND @x IS NOT NULL
      Handling Complex Conditions
   Hybrid Solutions – Using both Static and Dynamic SQL
      Using Views
      Using Inline Table Functions
   Conclusion
   Feedback and Acknowledgements
   Revision History

if you are on the proper version of SQL Server 2008, there is an additional technique that can be used, see: Dynamic Search Conditions in T-SQL Version for SQL 2008 (SP1 CU5 and later)

If you are on that proper release of SQL Server 2008, you can just add OPTION (RECOMPILE) to the query and the local variable's value at run time is used for the optimizations.

Consider this, OPTION (RECOMPILE) will take this code (where no index can be used with this mess of ORs):

WHERE
    (@search1 IS NULL or Column1=@Search1)
    AND (@search2 IS NULL or Column2=@Search2)
    AND (@search3 IS NULL or Column3=@Search3)

and optimize it at run time to be (provided that only @Search2 was passed in with a value):

WHERE
    Column2=@Search2

and an index can be used (if you have one defined on Column2)

这篇关于如果在 CTE 内?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！