跪求一个超难的ORACLE数据纵向存储转成横向查询输出
发布网友
发布时间：2022-04-08 11:00
共2个回答
热心网友
时间：2022-04-08 12:29
create table temp_bd_sbtz_gg
(
sbbh varchar2(20),
sbmc varchar2(20),
lxbh varchar2(20)
)
insert into temp_bd_sbtz_gg values('1','所内变压器','101');
insert into temp_bd_sbtz_gg values('2','真空断路器','102');
insert into temp_bd_sbtz_gg values('3','电磁式CT','103');
insert into temp_bd_sbtz_gg values('4','电容式CT','104');
commit;
create table temp_bm_sblx
(lxbh varchar2(20),
lxmc varchar2(20))
insert into temp_bm_sblx values('101','变压器');
insert into temp_bm_sblx values('102','断路器');
insert into temp_bm_sblx values('103','电磁式电压互感器');
insert into temp_bm_sblx values('104','电容式电压互感器');
commit;
create table temp_bm_sbsx
(
sxbh varchar2(20),
lxbh varchar2(20),
sxmc varchar2(20)
)
insert into temp_bm_sbsx values('201','101','电压等级');
insert into temp_bm_sbsx values('202','102','电压等级');
insert into temp_bm_sbsx values('203','103','电压等级');
insert into temp_bm_sbsx values('204','104','电压等级');
insert into temp_bm_sbsx values('205','101','产地');
insert into temp_bm_sbsx values('206','102','产地');
insert into temp_bm_sbsx values('207','103','产地');
insert into temp_bm_sbsx values('208','104','产地');
insert into temp_bm_sbsx values('209','104','维护人');
commit;
create table temp_bm_sbsxz
(sxzbh varchar2(20),
sbbh varchar2(20),
sxbh varchar2(20),
sxz varchar2(20))
insert into temp_bm_sbsxz values('301','1','201','220');
insert into temp_bm_sbsxz values('302','1','205','沈阳');
insert into temp_bm_sbsxz values('303','2','202','66');
insert into temp_bm_sbsxz values('304','2','206','长春');
insert into temp_bm_sbsxz values('305','3','203','220');
insert into temp_bm_sbsxz values('306','3','207','四平');
insert into temp_bm_sbsxz values('307','4','204','220');
insert into temp_bm_sbsxz values('308','4','208','四平');
insert into temp_bm_sbsxz values('309','4','209','张某');
commit;
create table temp_jieguo
(
bh varchar2(20),
name varchar2(20),
type varchar2(20),
lever varchar2(20),
address varchar2(20),
person varchar2(20)
)
--纵横转换的存储过程
create or replace procere p_temp_change is
sql_1 long;
begin
sql_1 := 'truncate table temp_jieguo';
execute immediate sql_1;
commit;
insert into temp_jieguo
select b.lxbh,
a.sbmc,
b.lxmc,
max(decode(c.sxmc, '电压等级', d.sxz)),
max(decode(c.sxmc, '产地', d.sxz)),
max(decode(c.sxmc, '维护人', d.sxz))
from temp_bd_sbtz_gg a,
temp_bm_sblx b,
temp_bm_sbsx c,
temp_bm_sbsxz d
where c.sxbh = d.sxbh
and a.lxbh = b.lxbh
and a.lxbh = c.lxbh
group by b.lxbh, a.sbmc, b.lxmc;
commit;
end;
--运行
declare
begin
p_temp_change;
end;
--查看结果
select * from temp_jieguo;
热心网友
时间：2022-04-08 13:47
create or replace view view_1 as
select
a.sbbh as 设备编号,
a.sbmc as 设备名称,
b.lxbh as 设备类型编号,
b.lxmc as 设备类型
from
bd_sbtz_gg as a,
bm_sblx as b
where
a.lxbh=b.sxbh;
--建立视图2-1用来关联【视图1】和【设备属性表】把【电压等级编号】的值取出
create or replace view view_2_1 as
select
a.设备编号,
a.设备名称,
a.设备类型,
b.sxbh as 电压等级编号
from
view_1 as a,
bm_sbsx as b
where
a.设备类型编号=b.lxbh
and
b.sxmc='电压等级';
--建立视图2-2用来关联【视图1】和【设备属性表】把【产地编号】的值取出
create or replace view view_2_2 as
select
a.设备编号,
a.设备名称,
a.设备类型,
b.sxbh as 产地编号
from
view_1 as a,
bm_sbsx as b
where
a.设备类型编号=b.lxbh
and
b.sxmc='产地';
--建立视图2-3用来关联【视图1】和【设备属性表】把【维护人编号】的值取出
create or replace view view_2_3 as
select
a.设备编号,
a.设备名称,
a.设备类型,
b.sxbh as 维护人编号
from
view_1 as a,
bm_sbsx as b
where
a.设备类型编号=b.lxbh
and
b.sxmc='维护人';
--建立视图3-1用来关联【视图2-1】和【设备属性值表】把【电压等级】的值取出
create or repalce view view_3_1 as
select
a.设备编号,
a.设备名称,
a.设备类型,
b.sxz as 电压等级
from
view_2_1 as a,
bm_sbsxz as b
where
a.电压等级编号=b.sxbh
and
a.设备编号=b.sbbh;
--建立视图3-2用来关联【视图2-2】和【设备属性值表】把【产地】的值取出
create or repalce view view_3_2 as
select
a.设备编号,
a.设备名称,
a.设备类型,
b.sxz as 产地
from
view_2_1 as a,
bm_sbsxz as b
where
a.产地编号=b.sxbh
and
a.设备编号=b.sbbh;
--建立视图3-3用来关联【视图2-3】和【设备属性值表】把【维护人】的值取出
create or repalce view view_3_3 as
select
a.设备编号,
a.设备名称,
a.设备类型,
b.sxz as 维护人
from
view_2_1 as a,
bm_sbsxz as b
where
a.维护人编号=b.sxbh
and
a.设备编号=b.sbbh;
--联合视图3-1~3-3检索最终结果
select
a.设备编号,
a.设备名称,
a.设备类型,
a.电压等级,
b.产地,
c.维护人
from
view_3_1 as a,
view_3_2 as b,
view_3_3 as c
where
a.设备编号=b.设备编号
and
b.设备编号=c.设备编号;
