select FirstName, TitleOfCourtesy, CourtesyInTC =
Case TitleOfCourtesy
When 'Ms.' Then '水姑娘'
When 'Dr.' Then '博士博'
When 'Mr.' Then '先仔'
When 'Mrs.' Then '女士'
Else '先生/小姐'
End
From employees
動態排列
了解了基本語法後咱們就可以開始學習按怎利用 Order By 加上 Case 敘述來做動態的資料排列
Declare @OrderKey TinyInt
Set @OrderKey = 2
Select CompanyName, ContactName, ContactTitle
From Customers
Order By Case
When @OrderKey = 1 Then CompanyName
When @OrderKey = 2 Then ContactName
Else ContactTitle
End
不過這親像還不太實用, 至少在 ASP 中, 所以接下來咱來做一個預儲程序
Create Procedure sp_SelectCustomersDynamicOrder
@OrderKey = Null
As
Select CompanyName, ContactName, ContactTitle
From Customers
Order By Case
When @OrderKey = 1 Then CompanyName
When @OrderKey = 2 Then ContactName
Else ContactTitle
End
