SQL中case用法
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的结构如下:
Column | Modifiers |
---|---|
script_msg | character varying(64) |
family | character varying(16) |
cell_sn | character varying(24) |
send_parm | smallint |
disabled | integer |
active_config | character varying(64) |
results_format_mask | smallint |
load_permissions | character varying(32) |
cell_type | character varying(8) |
batch_name | character varying(32) |
part_num | character varying(32) |
row_num | smallint |
temperature | double precision |
restart_flags | text |
user_state | character varying(16) |
jiffy_count | integer |
cm_index | smallint |
hda_sn | character varying(16) |
status_change_ts | double precision |
do_not_start | smallint |
wattage | double precision |
physical_cell | integer |
carrier_sn | character varying(16) |
drive_power | integer |
discovery_stat | character varying(16) |
maptime | integer |
fis_rim_type | character varying(8) |
slot_state | character varying(16) |
end_time | double precision |
drive_pairing_cur | character varying(32) |
drive_pairing_req | character varying(32) |
local_config | character varying(64) |
cool_demand | integer |
operation | character varying(16) |
cell_status | character varying(16) |
start_time | double precision |
cell_online | character varying(8) |
slot_batch_status | character varying(32) |
wait_state_time | double precision |
drive_fan | smallint |
grid_name | character varying(64) |
automation_reason | character varying(256) |
script_results_dir | character varying(64) |
replug_req_time | double precision |
script_error | character varying(10) |
sbr | character varying(32) |
static_rim_type | character varying(32) |
run_type | character varying(64) |
script_info | character varying(64) |
cm_ip_addr | character varying(16) |
drive_grade | character varying(16) |
pack_id | character varying(16) |
adaptor_sn | character varying(16) |
matco_state | character varying(32) |
cm_status | character varying(8) |
port_index | smallint |
tray_index | smallint |
heat_demand | integer |
unload_time | double precision |
cell_fan | smallint |
column_num | smallint |
rim_type | character 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;