PL / SQL：如何从3个表中选择行并使用特定列值重新排列行？

Vehicle Passing Summary table structure

车辆传递汇总表结构

    PsngSmry_ID(Number),Vehicle_iD(Number),PsngSmryTime(datetime)

Vehicle table structure

车辆表结构

    Vehicle_iD(Number),VehicleName(VarChar2),VehicleType(VarChar2)

Here Vehicle_iD is the Primary Key

这里Vehicle_iD是主键

Equipment Table Structure

设备表结构

    Eqpmt_id(Number),Vehicle_iD(Number),EqpmtName(VarChar2),EqpmtType(VarChar2)

Here Eqpmt_id is the Primary Key and Vehicle_iD is the foreign Key

这里Eqpmt_id是主键,Vehicle_iD是外键

Equipment Component Table Structure

设备组件表结构

    Eqpmt_Cmpnt_id(Number) ,Eqpmt_id(Number),EqpmtCmpntName(VarChar2),EqpmtCmpntName(VarChar2),Parent_Eqpmnt_ID(Number)

Here Eqpmt_Cmpnt_id is the Primary Key and Eqpmt_id is the foreign Key

这里Eqpmt_Cmpnt_id是主键,Eqpmt_id是外键

The rows in the Vehicle Passing Summary table

Vehicle Passing Summary表中的行

PsngSmry_ID Vehicle_ID  PsngSmryTime
111111      80986246    2010/10/11
111112      80986247    2010/10/12
111113      80986248    2010/10/10

The rows in the Vehicle Table

车辆表中的行

Vehicle_iD  VehicleName VehicleType
80986246        Lorry       Four Wheeler
80986247        Van     Four Wheeler
80986248        Bus     Four Wheeler

The rows in the Equipment Table:

设备表中的行:

Eqpmt_id    Vehicle_iD  EqpmtName       EqpmtType
109846      80986246        2 Axle Lorry        CAR
    109821      80986246        4 Axle Lorry        CAR
    109825      80986246        4 Axle Lorry        CAR
109562      80986247        2 Axle VAn      CAR
    109555      80986247        3 Axle VAn      CAR
    109777      80986247        3 Axle VAn      CAR
109587      80986248        2 Axle Bus      CAR

The rows in the Equipment Component Table :

设备组件表中的行:

Eqpmt_Cmpnt_id Eqpmt_id EqpmtCmpntName Parent_Eqpmnt_ID
20904146        109846      Truck             
20904147        109846      Truck
20904148        109846      Axle            20904146
20904159        109846      Axle            20904146
20904167        109846      Wheel           20904148
20904177        109846      Wheel           20904148
20904185        109846      Wheel           20904159
20904325        109846      Wheel           20904159
20904188        109846      Axle            20904147
20904189        109846      Axle            20904147
20904195        109846      Wheel           20904188
20904196        109846      Wheel           20904188
20904197        109846      Wheel           20904189
20904398        109846      Wheel           20904189
10904146        109562      Truck             
10904147        109562      Truck
10904148        109562      Axle            10904146
10904159        109562      Axle            10904146
10904167        109562      Wheel           10904148
10904177        109562      Wheel           10904148
10904185        109562      Wheel           10904159
10904325        109562      Wheel           10904159
10904188        109562      Axle            10904147
10904189        109562      Axle            10904147
10904195        109562      Wheel           10904188
10904196        109562      Wheel           10904188
10904197        109562      Wheel           10904189
10904398        109562      Wheel           10904189

Note : In Equipment Component Table,the hierarchy will be Truck-->Axle-->Wheel.So

注意:在设备组件表中,层次结构将为Truck - > Axle - > Wheel.So

1.the Parent_Eqpmnt_ID of Axle is Truck's Eqpmt_Cmpnt_id. 2.the Parent_Eqpmnt_ID of Wheel is Axle's Eqpmt_Cmpnt_id.

1. Axle的Parent_Eqpmnt_ID是Truck的Eqpmt_Cmpnt_id。 2. Wheel的Parent_Eqpmnt_ID是Axle的Eqpmt_Cmpnt_id。

Now I want to write the store procedure which will take "PsngSmry_ID(Number)" as input and the o/p will be in the format :

现在我想编写存储过程,它将“PsngSmry_ID(Number)”作为输入,o / p将采用以下格式:

Eqpmt_Cmpnt_id Eqpmt_id EqpmtCmpntName Parent_Eqpmnt_ID
20904146        109846      Truck   
20904148        109846      Axle            20904146
20904167        109846      Wheel           20904148
20904177        109846      Wheel           20904148    
20904159        109846      Axle            20904146
20904185        109846      Wheel           20904159
20904325        109846      Wheel           20904159
20904147        109846      Truck
20904188        109846      Axle            20904147
20904195        109846      Wheel           20904188
20904196        109846      Wheel           20904188
20904189        109846      Axle            20904147
20904197        109846      Wheel           20904189
20904398        109846      Wheel           20904189
10904146        109562      Truck   
10904148        109562      Axle            10904146
10904167        109562      Wheel           10904148
10904177        109562      Wheel           10904148    
10904159        109562      Axle            10904146
10904185        109562      Wheel           10904159
10904325        109562      Wheel           10904159
10904147        109562          Truck
10904188        109562          Axle            10904147
10904195        109562          Wheel           10904188
10904196        109562          Wheel           10904188
10904189        109562          Axle            10904147
10904197        109562          Wheel           10904189
10904398        109562          Wheel           10904189

       **Please add these columns in the o/p **
        1.EqpmtName and EqpmtType from Eqpmt table
        2.VehicleName and Vehicle Type from Vehicle table
        3.PsngSmryTime from PassingSummary table **

Can anyone tell me the solution?

谁能告诉我解决方案?

1 个解决方案

#1

For such questions, you should always post the corresponding DDL and DML statements. I am sure anyone willing to answer the question will appreciate such an effort. Also, it would have prevented the foreign key error and probably the -1.

对于这些问题,您应该始终发布相应的DDL和DML语句。我相信任何愿意回答这个问题的人都会感激这样的努力。此外,它可以防止外键错误,可能是-1。

That said... I have constructed such statements:

那说......我已经构建了这样的陈述:

drop table         tq84_equipment_component;
drop table         tq84_equipment;
drop table         tq84_vehicle;

create table       tq84_vehicle (
  vehicle_id       number primary key,
  vehicleName      varchar2(20),
  vehicleType      varchar2(20)
);

create table       tq84_equipment (
  eqpmt_id         number primary key,
  vehicle_id       not null references tq84_vehicle,
  eqpmtName        varchar2(20),
  eqpmtType        varchar2(20)
);

create table       tq84_equipment_component (
  eqpmt_cmpnt_id   number primary key,
  eqpmt_id         not null references tq84_equipment,
  eqpmtCmpntName   varchar2(20),
  parent_eqpmnt_id null references tq84_equipment_component
);


insert into tq84_vehicle values (80986246, 'Lorry', 'Four Wheeler');
insert into tq84_vehicle values (80986247, 'Van'  , 'Four Wheeler');
insert into tq84_vehicle values (80986248, 'Bus'  , 'Four Wheeler');

insert into tq84_equipment values (109846, 80986246, 'Axle Lorry', 'CAR');
insert into tq84_equipment values (109562, 80986247, 'Axle Van',   'CAR');
insert into tq84_equipment values (109587, 80986248, 'Axle Bus',   'CAR');


insert into tq84_equipment_component values (20904146, 109846, 'Truck',     null);
insert into tq84_equipment_component values (20904148, 109846, 'Axle' , 20904146);
insert into tq84_equipment_component values (20904167, 109846, 'Wheel', 20904148);
insert into tq84_equipment_component values (20904177, 109846, 'Wheel', 20904148);
insert into tq84_equipment_component values (20904159, 109846, 'Axle' , 20904146);
insert into tq84_equipment_component values (20904185, 109846, 'Wheel', 20904159);
insert into tq84_equipment_component values (20904325, 109846, 'Wheel', 20904159);
insert into tq84_equipment_component values (20904147, 109846, 'Truck',     null);
insert into tq84_equipment_component values (20904188, 109846, 'Axle' , 20904147);
insert into tq84_equipment_component values (20904195, 109846, 'Wheel', 20904188);
insert into tq84_equipment_component values (20904196, 109846, 'Wheel', 20904188);
insert into tq84_equipment_component values (20904189, 109846, 'Axle' , 20904147);
insert into tq84_equipment_component values (20904197, 109846, 'Wheel', 20904189);
insert into tq84_equipment_component values (20904398, 109846, 'Wheel', 20904189);
insert into tq84_equipment_component values (10904146, 109562, 'Truck',     null);
insert into tq84_equipment_component values (10904148, 109562, 'Axle' , 10904146);
insert into tq84_equipment_component values (10904167, 109562, 'Wheel', 10904148);
insert into tq84_equipment_component values (10904177, 109562, 'Wheel', 10904148);
insert into tq84_equipment_component values (10904159, 109562, 'Axle ', 10904146);
insert into tq84_equipment_component values (10904185, 109562, 'Wheel', 10904159);
insert into tq84_equipment_component values (10904325, 109562, 'Wheel', 10904159);
insert into tq84_equipment_component values (10904147, 109562, 'Truck',     null);
insert into tq84_equipment_component values (10904188, 109562, 'Axle' , 10904147);
insert into tq84_equipment_component values (10904195, 109562, 'Wheel', 10904188);
insert into tq84_equipment_component values (10904196, 109562, 'Wheel', 10904188);
insert into tq84_equipment_component values (10904189, 109562, 'Axle' , 10904147);
insert into tq84_equipment_component values (10904197, 109562, 'Wheel', 10904189);
insert into tq84_equipment_component values (10904398, 109562, 'Wheel', 10904189);

With these data, I believe you're after something like this:

有了这些数据,我相信你会这样:

select
  eqpmt_cmpnt_id,
  eqpmt_id,
  eqpmtCmpntName
  parent_eqpmnt_id
from
  tq84_equipment_component
start with
  eqpmt_id in (select eqpmt_id 
                from tq84_equipment
               where vehicle_id = 80986246) 
connect by
  prior eqpmt_cmpnt_id = parent_eqpmnt_id
order by 1;

Edit: changed eqpmt_id = (select ... to eqpmt_id in (select ...

编辑:更改了eqpmt_id =(选择...到eqpmt_id in(选择...)

#1