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

探討SQL Server中Case的不同用法

2013-11-12 23:41:24  來源: SQL Server 

  CASE 可能是 SQL 中被誤用最多的關鍵字之一雖然你可能以前用過這個關鍵字來創建字段但是它還具有更多用法例如你可以在 WHERE 子句中使用 CASE

  首先讓我們看一下 CASE 的語法在一般的 SELECT 中其語法如下

  SELECT  =

  CASE

  WHEN  THEN

  WHEN  THEN

  ELSE

  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/11236.html
    推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.