A. 使用帶有簡單 CASE 函數的 SELECT 語句
在 SELECT 語句中，簡單 CASE 函數僅檢查是否相等，而不進行其它比較。下面的示例使用 CASE 函數更改圖書分類顯示，以使其更易於理解。
USE pubs
GO
SELECT Category =
CASE type
WHEN ''''''''popular_comp'''''''' THEN ''''''''Popular Computing''''''''
WHEN ''''''''mod_cook'''''''' THEN ''''''''Modern Cooking''''''''
WHEN ''''''''business'''''''' THEN ''''''''Business''''''''
WHEN ''''''''psychology'''''''' THEN ''''''''Psychology''''''''
WHEN ''''''''trad_cook'''''''' THEN ''''''''Traditional Cooking''''''''
ELSE ''''''''Not yet categorized''''''''
END,
CAST(title AS varchar(25)) AS ''''''''Shortened Title'''''''',
price AS Price
FROM titles
WHERE price IS NOT NULL
ORDER BY type, price
COMPUTE AVG(price) BY type
B. 使用帶有 SUBSTRING 和 SELECT 的 CASE 函數
下面的示例使用 CASE 和 THEN 生成一個有關作者、圖書標識號和每個作者所著圖書類型的列表。
USE pubs
SELECT SUBSTRING((RTRIM(a.au_fname) + '''''''' ''''''''+
RTRIM(a.au_lname) + '''''''' ''''''''), 1, 25) AS Name, a.au_id, ta.title_id,
Type =
CASE
WHEN SUBSTRING(ta.title_id, 1, 2) = ''''''''BU'''''''' THEN ''''''''Business''''''''
WHEN SUBSTRING(ta.title_id, 1, 2) = ''''''''MC'''''''' THEN ''''''''Modern Cooking''''''''
WHEN SUBSTRING(ta.title_id, 1, 2) = ''''''''PC'''''''' THEN ''''''''Popular Computing''''''''
WHEN SUBSTRING(ta.title_id, 1, 2) = ''''''''PS'''''''' THEN ''''''''Psychology''''''''
WHEN SUBSTRING(ta.title_id, 1, 2) = ''''''''TC'''''''' THEN ''''''''Traditional Cooking''''''''
END
FROM titleauthor ta JOIN authors a ON ta.au_id = a.au_id
C. 結合sum使用,實現統計和已知的有限的行列轉換
例如物品分為 四類
物品號 物品類別 物品性能
1 cpu 1
2 cpu 2
2 cpu 2
3 memory 3
select 物品類別，count(*) ,sum(case 物品性能 when 1 then 1 else 0 end),
sum(case 物品性能 when 2 then 1 else 0 end),
sum(case 物品性能 when 3 then 1 else 0 end)
from tablename
group by 物品類別
D:歸類省略顯示
cj:
id dwid name cj
1 1 aa 70
2 1 bb 71
3 1 cc 72
4 2 dd 73
5 2 ee 74
6 2 ff 75
dw:
dwid name
1 單位1
2 單位2
結果顯示：
單位1 aa 70
bb 71
cc 72
單位2 dd 73
ee 74
ff 75
select case when (select count(id) from cj where dwid=b.dwid and id<=b.id)=1
then name else '''' end,b.name,b.cj
from dw a left join cj b
on a.dwid=b.dwid
order by b.dwid,b.id
