悠悠博客 关注Ajax技术与web开发

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