熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> SQL Server >> 正文

探討SQL Server中Case 的不同用法

2013-11-15 14:38:39  來源: SQL Server 

  CASE 可能是 SQL 中被誤用最多的關鍵字之一雖然你可能以前用過這個關鍵字來創建字段但是它還具有更多用法例如你可以在 WHERE 子句中使用 CASE
  
  首先讓我們看一下 CASE 的語法在一般的 SELECT 中其語法如下
  
  SELECT <myColumnSpec> =
  CASE
  WHEN <A> THEN <somethingA>
  WHEN <B> THEN <somethingB>
  ELSE <somethingE>
  END
  
  在上面的代碼中需要用具體的參數代替尖括號中的內容下面是一個簡單的例子
  
  USE pubs
  GO
  SELECT
  Title
  Price Range =
  CASE
  WHEN price IS NULL THEN Unpriced
  WHEN price < THEN Bargain
  WHEN price BETWEEN and THEN Average
  ELSE Gift to impress relatives
  END
  FROM titles
  ORDER BY price
  GO
  
  這是 CASE 的典型用法但是使用 CASE 其實可以做更多的事情比方說下面的 GROUP BY 子句中的 CASE
  
  SELECT Number of Titles Count(*)
  FROM titles
  GROUP BY
  CASE
  WHEN price IS NULL THEN Unpriced
  WHEN price < THEN Bargain
  WHEN price BETWEEN and THEN Average
  ELSE Gift to impress relatives
  END
  GO
  
  你甚至還可以組合這些選項添加一個 ORDER BY 子句如下所示
  
  USE pubs
  GO
  SELECT
  CASE
  WHEN price IS NULL THEN Unpriced
  WHEN price < THEN Bargain
  WHEN price BETWEEN and THEN Average
  ELSE Gift to impress relatives
  END AS Range
  Title
  FROM titles
  GROUP BY
  CASE
  WHEN price IS NULL THEN Unpriced
  WHEN price < THEN Bargain
  WHEN price BETWEEN and THEN Average
  ELSE Gift to impress relatives
  END
  Title
  ORDER BY
  CASE
  WHEN price IS NULL THEN Unpriced
  WHEN price < THEN Bargain
  WHEN price BETWEEN and THEN Average
  ELSE Gift to impress relatives
  END
  Title
  GO
  
  注意為了在 GROUP BY 塊中使用 CASE查詢語句需要在 GROUP BY 塊中重復 SELECT 塊中的 CASE 塊
  
  除了選擇自定義字段之外在很多情況下 CASE 都非常有用再深入一步你還可以得到你以前認為不可能得到的分組排序結果集
From:http://tw.wingwit.com/Article/program/SQLServer/201311/22106.html
    推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.