有道笔试题:
服务器监控表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'
不知道有没有更好的做法...