SQL中case用法

博客主要介绍了SQL中CASE语法,包含简单CASE函数和CASE搜索函数两种格式,并给出实例。作者以在seagate遇到的情况为例,展示如何使用CASE语法统计表格中testing、pass、fail、restart四种状态的数量。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

https://www.tablesgenerator.com/markdown_tables#

case 语法

case具有两种格式。

1.简单case函数:

WHEN when_expression 
THEN result_expression [ ...n ]   
[ ELSE else_result_expression ]   
END   

2.case搜索函数:

CASE  
WHEN Boolean_expression 
THEN result_expression [ ...n ]    
[ ELSE else_result_expression ]   
END  

3.实例:

以下是我在seagate时遇到的情况:

已知表status的结构如下:

ColumnModifiers
script_msgcharacter varying(64)
familycharacter varying(16)
cell_sncharacter varying(24)
send_parmsmallint
disabledinteger
active_configcharacter varying(64)
results_format_masksmallint
load_permissionscharacter varying(32)
cell_typecharacter varying(8)
batch_namecharacter varying(32)
part_numcharacter varying(32)
row_numsmallint
temperaturedouble precision
restart_flagstext
user_statecharacter varying(16)
jiffy_countinteger
cm_indexsmallint
hda_sncharacter varying(16)
status_change_tsdouble precision
do_not_startsmallint
wattagedouble precision
physical_cellinteger
carrier_sncharacter varying(16)
drive_powerinteger
discovery_statcharacter varying(16)
maptimeinteger
fis_rim_typecharacter varying(8)
slot_statecharacter varying(16)
end_timedouble precision
drive_pairing_curcharacter varying(32)
drive_pairing_reqcharacter varying(32)
local_configcharacter varying(64)
cool_demandinteger
operationcharacter varying(16)
cell_statuscharacter varying(16)
start_timedouble precision
cell_onlinecharacter varying(8)
slot_batch_statuscharacter varying(32)
wait_state_timedouble precision
drive_fansmallint
grid_namecharacter varying(64)
automation_reasoncharacter varying(256)
script_results_dircharacter varying(64)
replug_req_timedouble precision
script_errorcharacter varying(10)
sbrcharacter varying(32)
static_rim_typecharacter varying(32)
run_typecharacter varying(64)
script_infocharacter varying(64)
cm_ip_addrcharacter varying(16)
drive_gradecharacter varying(16)
pack_idcharacter varying(16)
adaptor_sncharacter varying(16)
matco_statecharacter varying(32)
cm_statuscharacter varying(8)
port_indexsmallint
tray_indexsmallint
heat_demandinteger
unload_timedouble precision
cell_fansmallint
column_numsmallint
rim_typecharacter varying(32)

此时我想要知道在表格status中testing, pass, fail,restart四种状态的数量。
如下代码所示:

select 
sum(case 
		when cell_status='TESTING' 
			then 1 
			else 0 
		end) 
as TESTING,
sum(case 
	when cell_status='PASS' 
		then 1 
		else 0 
	end) 
as PASS,
sum(case 
		when cell_status='FAIL' 
			then 1 
			else 0 
		end) 
as FAIL,
sum(case 
		when cell_status='RESTART' 
			then 1 
			else 0 
		end) as RESTART 
from status;
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值