sqlserverwhencase

① sqlserver的case when 用法

---下文舉例分析了casewhen常用的用法，如下所示：涉及排序欄位的應用
createtabletest
(
qtyint,
sortvarchar(20)
)
insertintotest(qty,sort)values
(1,'a'),(2,'b'),(3,'d'),(1,'e')
go
----方法1:
selectsort,qty,
caseqty
when1then'少'
when2then'中'
when3then'多'
else'未知'
endas[數量范圍]
fromtest
--方法2:
selectsort,qty,
case
whenqty=1then'少'
whenqty=2then'中'
whenqty=3then'多'
else'未知'
endas[數量范圍]
fromtest

---sum統計用法
select
sum(casewhenqty=1then1else0end)as[少],
sum(caseqtywhen2then1else0end)as[中],
sum(casewhenqty=3then1else0end)as[多],
sum(casewhenqty<>1andqty<>2andqty<>3then1else0end)as[位置]
fromtest


---casewhen做排序欄位
declare@iint
set@i=0
select*fromtest
orderby
case@iwhen0thenqtyelsesortendgo
truncatetabletest
droptabletest

② sqlserver語句中 case when then else X end

SELECTNODE_TREEVIEW.NODE_NAME,
(CASE
WHEN(TEMPERATURE>='100')THEN
'1'
ELSE
NULL
END),
(CASE
WHEN(BATTERY>='100')THEN
'2'
ELSE
NULL
END),
(CASE
WHEN(FENGSU>='100')THEN
'3'
ELSE
NULL
END),
(CASE
WHEN(SHIDU>='100')THEN
'4'
ELSE
NULL
END),
(CASE
WHEN(WASI>='1000')THEN
'5'
ELSE
NULL
END),
(CASE
WHEN(FENCHEN>='100')THEN
'6'
ELSE
NULL
END)
FROMNODE_LATEST
INNERJOINNODE_TREEVIEW
ONNODE_LATEST.LL_ADDR=NODE_TREEVIEW.LL_ADDR

從上面的sql中可以明顯看出，把不滿足條件的數據顯示為null