sql select case when 语句

有道笔试题：

服务器监控表server_status中,当服务器状态发生server_status变化时数据表中将被插入一条记录,状态0表示停机  1表示正常,用SQL查询Server A 的停机开始时间和结束时间,表中存在多台Server的状态记录.

SVR_ID	SVR_NAME	STATUS_TIME	STATUS
1	A	2013-10-31 00:00:00	1
2	B	2013-10-31 00:00:00	1
1	A	2014-11-31 00:00:00	0
2	B	2014-11-31 00:00:00	0
3	C	2014-11-31 00:00:00	0
...	...	...	...

 

我做的话只会 

select s1.svr_name, s1.status_time start_date, s2.status_time end_date
from server_status s1, server_status s2
where s1.svr_id = s2.svr_id
and s1.status = 1
and s2.status = 0

and s1.svr_name='A'

 

，今天看到别人用了case when语法，于是我也试着写了一下

select svr_name,
case status
when 1 then
status_time
end as start_time,
case status
when 0 then
status_time
end as end_time
from server_status
where svr_name = 'A'

不知道有没有更好的做法...

转载于:https://www.cnblogs.com/simple-code/p/4546221.html