代码之家 › 专栏 › 技术社区 › Jay

在SQL中执行if,其中

sql-server sql

Jay · 技术社区 · 15 年前

我想这么做

SELECT *
  FROM sales_order_header       
 WHERE order_reference LIKE @order_reference + '%'
   AND ((@booked_in IS NULL) OR ( booked_in = @booked_in))
   AND ((@depot_assigned IS NULL) OR ( IF @depot_assigned = 'Y' depot_code <> ' ' ELSE depot_code = ' ') )

我相信你们都能猜出或(如果@depot_assigned etc)位失败了。

我如何才能这样做,或者我必须根据@depot_分配的参数的值作为两个单独的select语句来做。

4 回复 | 直到 15 年前

Benoît Vidis 15 年前

SELECT
        *

    FROM
        sales_order_header

    WHERE
        order_reference LIKE @order_reference + '%'         AND
        ((@booked_in IS NULL) OR ( booked_in = @booked_in)) AND
        ((@depot_assigned IS NULL) OR 
            ( @depot_assigned = 'Y' AND depot_code <> ' ' ) OR
            ( @depot_assigned <> 'Y' AND depot_code = ' ') )

jamesaharvey 15 年前

T-SQL CASE 声明就是你要找的。对下面代码的修改应该对您有用:

SELECT
    *
FROM
    sales_order_header

WHERE
    order_reference LIKE @order_reference + '%'         AND
    ((@booked_in IS NULL) OR ( booked_in = @booked_in)) AND
    ((@depot_assigned IS NULL) OR (CASE WHEN @depot_assigned = 'Y' THEN depot_code <> ' ' ELSE depot_code = ' ' END) )

Remus Rusanu 15 年前

当您构建一个与您尝试的查询类似的查询时,查询优化器必须创建一个计划来适应任何参数的值。这将导致最坏的计划。因此,即使您想出了正确的表达式(使用case),由于矛盾或条件,查询也将非常糟糕。

将IFS分为SQL条件,并对每种情况使用不同的查询:

IF @booked_in IS NULL and @depot_assigned IS NULL
  SELECT * FROM sales_order_header
  WHERE order_reference LIKE @order_reference + '%'
ELSE IF @depot_assigned IS NULL
  SELECT * FROM sales_order_header
  WHERE order_reference LIKE @order_reference + '%'
  AND booked_in = @booked_in
ELSE IF @booked_in IS NULL AND @depot_code='Y'
  SELECT * FROM sales_order_header
  WHERE order_reference LIKE @order_reference + '%'
  AND depot_code <> ' '
ELSE IF IF @booked_in IS NULL AND @depot_code<>'Y'
  SELECT * FROM sales_order_header
  WHERE order_reference LIKE @order_reference + '%'
  AND depot_code = ' '
ELSE IF @depot_code = 'Y'
  SELECT * FROM sales_order_header
  WHERE order_reference LIKE @order_reference + '%'
  AND booked_in = @booked_in
  AND depot_code <> ' '
ELSE
  SELECT * FROM sales_order_header
  WHERE order_reference LIKE @order_reference + '%'
  AND booked_in = @booked_in
  AND depot_code = ' '

尽管表面上很难看,缺乏优雅,但这是最好的方法。它的主要特点是可维护性问题,为了解决使用动态SQL构建查询时存在的一种可行的替代方法。

归根结底,if条件的复杂性是一种代码味道,您的数据访问API做了太多应该是独立的API入口点的事情。

Kevin Fairchild 15 年前

SELECT * FROM sales_order_header       
WHERE 
order_reference LIKE @order_reference + '%'
AND 
booked_in = ISNULL(@booked_in,booked_in)
AND 
CASE 
    WHEN @depot_assigned IS NULL THEN 1 
    WHEN @depot_assigned  = 'Y' AND depot_code <> ' ' THEN 1 
    WHEN @depot_assigned <> 'Y' AND depot_code  = ' ' THEN 1
    ELSE 0 
END = 1

编辑:类似于其他一些答案。只是稍微简单一点。