交叉表能清晰准确的反映出数据的关系,因此在实际应用中也经常见到,但是作为二维表格的关系型数据在统计时不能简单的通过查询来实现,这里以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;
效果: