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