SQL Server 2008 - SELECT 子句中的 Case/If 语句 [英] SQL Server 2008 - Case / If statements in SELECT Clause

查看:46
本文介绍了SQL Server 2008 - SELECT 子句中的 Case/If 语句的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我有一个应该像这样运行的查询 -

<前>如果(var = xyz)选择列 1,列 2否则 IF(var = zyx)选择 col2, col3别的选择 col7,col8从...

如何在 T-SQL 中实现这一点而不为每个子句编写单独的查询?目前我正在运行它

<前>如果 (var = xyz) {查询1}否则如果(var = zyx){查询2}别的 {查询 3}

这只是很多冗余代码,只是为了根据值选择不同的列.有其他选择吗?

解决方案

请注意,出于优化的原因,您实际上最好使用 3 个单独的 SELECTS.如果您只有一个 SELECT,那么生成的计划将必须投影所有列 col1、col2、col3、col7、col8 等,尽管根据运行时 @var 的值,只需要一些列.这可能会导致计划执行不必要的聚集索引查找,因为非聚集索引未涵盖 SELECT 投影的所有列.

另一方面,3 个单独的 SELECTS,每个仅投影所需的列可能会受益于在每种情况下仅覆盖您的投影列的非聚集索引.

当然,这取决于您的数据模型的实际架构和确切的查询,但这只是一个提示,因此您不会将过程式编程的命令式思维框架带入 SQL 的声明式世界.

>

I have a Query that's supposed to run like this -


If(var = xyz) 
   SELECT col1, col2
ELSE IF(var = zyx)
   SELECT col2, col3
ELSE
   SELECT col7,col8

FROM 

.
.
.

How do I achieve this in T-SQL without writing separate queries for each clause? Currently I'm running it as


IF (var = xyz) {
  Query1
}
ELSE IF (var = zyx) {
  Query2
}
ELSE {
  Query3
}

That's just a lot of redundant code just to select different columns depending on a value. Any alternatives?

解决方案

Just a note here that you may actually be better off having 3 separate SELECTS for reasons of optimization. If you have one single SELECT then the generated plan will have to project all columns col1, col2, col3, col7, col8 etc, although, depending on the value of the runtime @var, only some are needed. This may result in plans that do unnecessary clustered index lookups because the non-clustered index Doesn't cover all columns projected by the SELECT.

On the other hand 3 separate SELECTS, each projecting the needed columns only may benefit from non-clustered indexes that cover just your projected column in each case.

Of course this depends on the actual schema of your data model and the exact queries, but this is just a heads up so you don't bring the imperative thinking mind frame of procedural programming to the declarative world of SQL.

这篇关于SQL Server 2008 - SELECT 子句中的 Case/If 语句的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆