--地区表
create table places(
id number primary key,
name varchar2(12)
);
insert into places(id,name) values(1,'广州');
insert into places(id,name) values(2,'深圳');
insert into places(id,name) values(3,'珠海');
insert into places(id,name) values(4,'佛山');
--码头表
create table docks(
id number primary key,
name varchar2(12)
);
insert into docks(id,name) values(1,'深圳湾');
insert into docks(id,name) values(2,'大亚湾');
insert into docks(id,name) values(3,'马六甲湾');
--统计信息主表
create table info_main(
id number primary key,
place1 varchar2(12),--起运港口（对应places的id，下同）
place2 varchar2(12),--中转港口1
place3 varchar2(12),--中转港口2
place4 varchar2(12)--终点港口
);
insert into info_main(id,place1,place2,place3,place4) values(1,1,2,3,4);
insert into info_main(id,place1,place2,place3,place4) values(2,1,2,2,4);
insert into info_main(id,place1,place2,place3,place4) values(3,1,2,3,3);
insert into info_main(id,place1,place2,place3,place4) values(4,2,2,3,4);
--箱信息表
create table box(
id number primary key,
info_main_id number,--统计主表id
dock_id number,--对应码头信息
box20 number,--20号箱
box40 number--40号箱
);
insert into box(id,info_main_id,dock_id,box20,box40) values(1,1,1,23,34);
insert into box(id,info_main_id,dock_id,box20,box40) values(2,1,2,12,34);
insert into box(id,info_main_id,dock_id,box20,box40) values(3,2,3,34,6);
insert into box(id,info_main_id,dock_id,box20,box40) values(4,3,4,67,78);
--港湾信息表
create table dock_info(
id number primary key,
info_main_id number,--统计主表id
dock_id number,--对应码头信息
other varchar(20)--其它信息
);
insert into dock_info(id,info_main_id,dock_id,other) values(1,1,1,'备注');
insert into dock_info(id,info_main_id,dock_id,other) values(2,1,2,'备注');
insert into dock_info(id,info_main_id,dock_id,other) values(3,2,3,'备注');
insert into dock_info(id,info_main_id,dock_id,other) values(4,2,4,'备注');
insert into dock_info(id,info_main_id,dock_id,other) values(5,3,2,'备注');
insert into dock_info(id,info_main_id,dock_id,other) values(6,4,3,'备注');
输入的统计条件：dock_id
要输出的结果：
地区：对应地区表的名字
航次：info_main中地区出现的次数，place1--4的值凡是与places中的id对应上则算一次，重复的值不算
insert into info_main(id,place1,place2,place3,place4) values(1,1,2,3,4);