用SQL语句实现行到列的转换

<案例>

 

log表

id    host            name           content              status     date
1    web主机     cpu使用率     cpu使用率5%       正常       2009-8-6
2    web主机     内存使用率    内存使用率20%     正常        2009-8-6
3    web主机     空间使用率    空间使用率39%     正常        2009-8-6
4    业务主机      cpu使用率    cpu使用率5%       正常       2009-8-6
5    业务主机      内存使用率   内存使用率20%      正常       2009-8-6
6    业务主机      空间使用率   空间使用率39%      正常       2009-8-6
7    接口主机      cpu使用率    cpu使用率5%       正常      2009-8-6
8    接口主机      内存使用率   内存使用率20%      不正常       2009-8-6
9    接口主机      空间使用率   空间使用率39%      正常      2009-8-6

 

希望能统计处如下结果

web主机状态  cup使用率    内存使用率      业务主机状态  cup使用率     内存使用率       接口主机状态  cup使用率  内存使用率 
正常          cpu使用率5%    内存使用率20% 正常             cpu使用率5%  内存使用率20%  不正常   cpu使用率5%  内存使用率20%  

 

 

<实现方法> 

select decode(max(case when host='web主机' and status<>'正常' then status end),null,'正常','不正常')  web主机状态,
  max(case when host='web主机' and rn=1 then content end ) cpu使用率,
  max(case when host='web主机' and rn=2 then content end ) 内存使用率,
  decode(max(case when host='业务主机' and status<>'正常' then status end),null,'正常','不正常')  业务主机状态,
  max(case when host='业务主机' and rn=1 then content end)  cpu使用率,
  max(case when host='业务主机' and rn=2 then content end) 内存使用率,
  decode(max(case when host='接口主机' and status<>'正常' then status end),null,'正常','不正常')  接口主机状态,
  max(case when host='接口主机' and rn=1 then content end)  cpu使用率,
  max(case when host='接口主机' and rn=2 then content end)  内存使用率

from(
     select row_number()over(partition by host order by id) rn,
         host,name,content,status from log);

group by date;

 

先整理一下源数据。这里主要使用row_number（）函数，将源数据依据字段host进行分组，然后再分组内部进行排序。排序生成的序号，赋值给新变量rn。

 

将多行数据整理成一行，上面的SQL语句通过select选取字段来实现。我们先看第一组，其他两组方法都是一样的。

 

第一个字段，遍历全表，挑出所有记录中host字段为“web主机”，同时判断status，有没有不等于“正常“的记录，如果有的话，该字段为”不正常“，反之，该字段等于“正常”。这里的max（）函数非常重要，它使得select在选取该字段时，需要遍历每一行。

 

第二个字段，遍历全表，选择记录中host字段为“web主机”，同时rn为2的记录，将这条记录的content值赋予该字段。这里的max（）也是不能或缺的，它使select需要遍历所有行，来选取符合条件的行中的字段值。

 

第三个字段，选取方法与第二个字段相同，不再重复。

 

<补充知识>

 

row_number() over (partition by col1 order by col2)
表示根据col1分组，在分组内部根据 col2排序
而这个值就表示每组内部排序后的顺序编号（组内连续的唯一的）

表内容如下：
name | seqno | description
A | 1 | test
A | 2 | test
A | 3 | test
A | 4 | test
B | 1 | test
B | 2 | test
B | 3 | test
B | 4 | test
C | 1 | test
C | 2 | test
C | 3 | test
C | 4 | test

我想有一个sql语句，搜索的结果是
A | 1 | test
A | 2 | test
B | 1 | test
B | 2 | test
C | 1 | test
C | 2 | test
实现:
select name,seqno,description
from(select name,seqno,description,row_number()over(partition by name order by seqno)id
from table_name) where id<=3;