Oracle 中实现交叉表 查询

交叉表能清晰准确的反映出数据的关系，因此在实际应用中也经常见到，但是作为二维表格的关系型数据在统计时不能简单的通过查询来实现，这里以Oracle 为例简单介绍一下交叉表的查询：

百度模板图片：

• 一、创建数据表

--建表
create table tbUser(Gender varchar2(10),Handness varchar2(30));
--插入模拟数据
declare
 i integer;
begin
  for i in 1..4 loop
    insert into tbUser(Gender,Handness)
    values('Female','Left');
  end loop;
  commit;
end;

• 二、通过查询生成交叉表

with t1 as(
select gender,sum(left) left,sum(right) right,sum(total) total from(
select gender,sum(male) male,sum(female) female,sum(Left) left, sum(Right) right,(sum(male)+sum(female)) total from(
select gender,decode(gender,'Male',1,0) male,decode(gender,'Female',1,0) female,decode(handness,'Left',1,0) Left,decode(handness,'Right',1,0) Right
 from tbUser)
 group by gender,left,right
 )
group by gender)
select gender,right,left,total from t1
union all 
select 'total' gender,sum(right) right,sum(left) left,sum(total) total from t1;

效果：