原始表如下：
col1 col2 col3
a 1 12.0
a 2 13.0
b 1 9.0
c 2 42.0
c 2 8.0
要实现如下查询：
a 1 1行 12.0 2 1行 13.0
b 1 1行 9.0 2 0行 0.0
c 1 0行 0.0 2 2行 50.0
---------------------------------------------------------------
这样行不行
select col1, col2, count(*), sum(col3) from table group by col1, col2;
然后选出来再按col1 排成一行就行
---------------------------------------------------------------
select col1,1,sum(
case col2 when
1 then 1
else 0
end
) ¦ ¦"行",
sum( case col2 when 1 then col3 else 0.00 end) ,
2,
sum(
case col2 when
2 then 1
else 0
end
) ¦ ¦"行",
sum( case col2 when 2 then col3 else 0.00 end)
from at
group by 1
order by 1
---------------------------------------------------------------
用decode 函数的实现
select
col1,
1,
sum(decode ( col2,1,1,0)) ¦ ¦"行",
sum( decode(col2,1,col3,0.00)),
2,
sum( decode( col2,2,1,0) ) ¦ ¦"行",
sum( decode(col2,2,col3,0.00))
from at
group by 1
order by 1
---------------------------------------------------------------
case/decode要在7.3后才有。