/**
* 初始化数据
*/
-- 初始化 update td_basic_mile_file 别名
update td_basic_mile_file a set
en_alias_name=(select station_name from td_basic_station_branch b where a.en_station_name=b.file_station_name and rownum=1)
,ex_alias_name=(select station_name from td_basic_station_branch b where a.ex_station_name=b.file_station_name and rownum=1);
-- branch表到每个站点(file_station_name)必须只有一条记录,否则报错
-- 桩号
update td_basic_mile_file a
set stakenum=(select min(stakenum) from td_basic_station b
where a.en_alias_name=b.stationname) ;
-- 不能用b.name, 不用min会多行,有bug
update td_basic_mile_file a
set stakenum2=(select min(stakenum) from td_basic_station b
where a.ex_alias_name=b.stationname) ;
update td_basic_mile_file set stakenum2='45' where id=6;
-- 上行,下行
update td_basic_mile_file a set opposite='0' where to_number(stakenum)<to_number(stakenum2) ;
update td_basic_mile_file a set opposite='1' where to_number(stakenum)>to_number(stakenum2) ;
-- 初始化 收费单元等编码
update td_basic_mile_file a set unit_node_code=(select id||'11' from td_basic_station b where a.en_alias_name=b.name)
,opposite_node_code=(select id||'22' from td_basic_station b where a.en_alias_name=b.name)
,en_code=(select id from td_basic_station b where a.en_alias_name=b.name)
,ex_code=(select id from td_basic_station b where a.ex_alias_name=b.name)
,create_time='2019-08-17 16:48:49'
where a.opposite='0';
-- null处理
update td_basic_mile_file a set unit_node_code=(select id||'33' from td_basic_station b where a.ex_alias_name=b.name)
,opposite_node_code=(select id||'44' from td_basic_station b where a.ex_alias_name=b.name)
where unit_node_code is null or unit_node_code='' or unit_node_code like '12345%' and a.opposite='0';
update td_basic_mile_file a set unit_node_code='G005544001014111',opposite_node_code='G005544001014122'where id=14;
update td_basic_mile_file a set unit_node_code='G005544001013111',opposite_node_code='G005544001013122'where id=17;
-- 反向生成一份
insert into td_basic_mile_file
select seq_file.nextval,ex_station_name,en_station_name,miles,create_time,null,ex_alias_name,en_alias_name,
opposite_node_code,unit_node_code,EX_CODE,EN_CODE,1-to_number(opposite),stakenum2,stakenum
from td_basic_mile_file t;
/**
* 开始生成数据
*/
drop table td_node_code;
create table td_node_code as
-- 收费站
select substr(b.id,1,7) toll_road_id,substr(b.id,1,11) section_id,b.id road_node_id
,b.name road_node_name ,'3' road_node_type, '0' status, null opposite_id, '166'version
from td_basic_station b,(select distinct en_alias_name from td_basic_mile_file) c
where b.NAME = c.en_alias_name
union all
-- 收费单元(包括反向)
select distinct substr(unit_node_code,1,7) toll_road_id,substr(b.unit_node_code,1,11) section_id,unit_node_code road_node_id,
en_alias_name||'-'||ex_alias_name road_node_name
,'1' road_node_type, '0' status, opposite_node_code opposite_id, '166'version
from td_basic_mile_file b
;
drop table td_node_relation;
create table td_node_relation as
-- 单元与单元relation
select distinct a.unit_node_code en_road_node_id,b.unit_node_code ex_road_node_id , a.miles,'166' version, '190819' valid_date
from td_basic_mile_file a, td_basic_mile_file b where a.next_id=b.id
union
-- 反向单元与单元relation
select distinct b.opposite_node_code en_road_node_id,a.opposite_node_code ex_road_node_id , b.miles,'166' version, '190819' valid_date
from td_basic_mile_file a, td_basic_mile_file b where a.next_id=b.id -- 注意这里是b.miles
union
-- 上行-站与单元relation
select distinct b.en_code en_road_node_id,b.unit_node_code ex_road_node_id ,-- 不distinct会重复
'0' miles,'166' version, '190819' valid_date
from td_basic_station_branch a,td_basic_mile_file b
where a.station_name=b.en_alias_name
and a.up_en='1' and en_code is not null and b.opposite='0'
union
-- 上行-单元与站relation
select distinct b.unit_node_code en_road_node_id,b.ex_code ex_road_node_id ,b.miles miles,'166' version, '190819' valid_date
from td_basic_station_branch a,td_basic_mile_file b
where a.station_name=b.ex_alias_name -- 这里是ex_alias_name
and a.up_ex='1' and ex_code is not null and b.opposite='0' -- null则没有,不能生成关系
union
-- 下行-站与单元relation
select distinct b.ex_code en_road_node_id,b.opposite_node_code ex_road_node_id ,'0' miles,'166' version, '190819' valid_date
from td_basic_station_branch a,td_basic_mile_file b
where a.station_name=b.ex_alias_name
and a.down_en='1' and ex_code is not null and b.opposite='0'
union
-- 下行-单元与站relation
select distinct b.opposite_node_code en_road_node_id,b.en_code ex_road_node_id ,b.miles miles,'166' version, '190819' valid_date
from td_basic_station_branch a,td_basic_mile_file b
where a.station_name=b.en_alias_name
and a.down_ex='1' and en_code is not null and b.opposite='0'
union
-- link-relation
select b.unit_node_code en_road_node_id,c.unit_node_code ex_road_node_id ,b.miles miles,'166' version, '190819' valid_date
from td_basic_road_link a,td_basic_mile_file b,td_basic_mile_file c
where a.en_start_station=b.en_station_name and a.en_end_station=b.ex_station_name and
a.ex_start_station=c.en_station_name and a.ex_end_station=c.ex_station_name
;
--------------------------------
-- 费率表
--------------------------------
-- update fee_file
update td_basic_fee_file a set unit_node_code=(select unit_node_code from td_basic_mile_file b where a.mile_file_id=b.id)
,opposite_node_code=(select opposite_node_code from td_basic_mile_file b where a.mile_file_id=b.id);
drop table td_province_price;
create table td_province_price as
select substr(unit_node_code,1,7) toll_road_id,substr(unit_node_code,1,11) section_id,unit_node_code toll_Interval_Id,
vehicle_Type,price fee,'166' version, '190819' valid_date
from td_basic_fee_file;
mysql
/**
* 初始化数据
*/
-- 初始化 update td_basic_mile_file 别名
update td_basic_mile_file a set
en_alias_name=(select station_name from td_basic_station_branch b where a.en_station_name=b.file_station_name limit 1)
,ex_alias_name=(select station_name from td_basic_station_branch b where a.ex_station_name=b.file_station_name limit 1);
-- branch表到每个站点(file_station_name)必须只有一条记录,否则报错
-- 桩号
update td_basic_mile_file a
set stakenum=(select min(stakenum) from td_basic_station b
where a.en_alias_name=b.stationname) ;
-- 不能用b.name, 不用min会多行,有bug
update td_basic_mile_file a
set stakenum2=(select min(stakenum) from td_basic_station b
where a.ex_alias_name=b.stationname) ;
update td_basic_mile_file set stakenum2='45' where id=6;
-- 上行,下行
update td_basic_mile_file a set opposite='0' where cast(stakenum as unsigned int)<cast(stakenum2 as unsigned int) ;
update td_basic_mile_file a set opposite='1' where cast(stakenum as unsigned int)>cast(stakenum2 as unsigned int) ;
-- 初始化 收费单元等编码
update td_basic_mile_file a set unit_node_code=(select concat(id,'11') from td_basic_station b where a.en_alias_name=b.name)
,opposite_node_code=(select concat(id,'22') from td_basic_station b where a.en_alias_name=b.name)
,en_code=(select id from td_basic_station b where a.en_alias_name=b.name)
,ex_code=(select id from td_basic_station b where a.ex_alias_name=b.name)
,create_time='2019-08-17 16:48:49'
where a.opposite='0';
-- null处理
update td_basic_mile_file a set unit_node_code=(select concat(id,'33') from td_basic_station b where a.ex_alias_name=b.name)
,opposite_node_code=(select concat(id,'44') from td_basic_station b where a.ex_alias_name=b.name)
where unit_node_code is null or unit_node_code='' or unit_node_code like '12345%' and a.opposite='0';
update td_basic_mile_file a set unit_node_code='G005544001014111',opposite_node_code='G005544001014122'where id=14;
update td_basic_mile_file a set unit_node_code='G005544001013111',opposite_node_code='G005544001013122'where id=17;
-- 反向生成一份
insert into td_basic_mile_file
select null,ex_station_name,en_station_name,miles,create_time,null,ex_alias_name,en_alias_name,
opposite_node_code,unit_node_code,EX_CODE,EN_CODE,1-cast(opposite as UNSIGNED integer),stakenum2,stakenum
from td_basic_mile_file t;
/**
* 开始生成数据
*/
drop table td_node_code;
create table td_node_code as
-- 收费站
select substr(b.id,1,7) toll_road_id,substr(b.id,1,11) section_id,b.id road_node_id
,b.name road_node_name ,'3' road_node_type, '0' status, null opposite_id, '166'version
from td_basic_station b,(select distinct en_alias_name from td_basic_mile_file) c
where b.NAME = c.en_alias_name
union all
-- 收费单元(包括反向)
select distinct substr(unit_node_code,1,7) toll_road_id,substr(b.unit_node_code,1,11) section_id,unit_node_code road_node_id,
concat(en_alias_name,'-',ex_alias_name) road_node_name
,'1' road_node_type, '0' status, opposite_node_code opposite_id, '166'version
from td_basic_mile_file b
;
drop table td_node_relation;
create table td_node_relation as
-- 单元与单元relation
select distinct a.unit_node_code en_road_node_id,b.unit_node_code ex_road_node_id , a.miles,'166' version, '190819' valid_date
from td_basic_mile_file a, td_basic_mile_file b where a.next_id=b.id
union
-- 反向单元与单元relation
select distinct b.opposite_node_code en_road_node_id,a.opposite_node_code ex_road_node_id , b.miles,'166' version, '190819' valid_date
from td_basic_mile_file a, td_basic_mile_file b where a.next_id=b.id -- 注意这里是b.miles
union
-- 上行-站与单元relation
select distinct b.en_code en_road_node_id,b.unit_node_code ex_road_node_id ,-- 不distinct会重复
'0' miles,'166' version, '190819' valid_date
from td_basic_station_branch a,td_basic_mile_file b
where a.station_name=b.en_alias_name
and a.up_en='1' and en_code is not null and b.opposite='0'
union
-- 上行-单元与站relation
select distinct b.unit_node_code en_road_node_id,b.ex_code ex_road_node_id ,b.miles miles,'166' version, '190819' valid_date
from td_basic_station_branch a,td_basic_mile_file b
where a.station_name=b.ex_alias_name -- 这里是ex_alias_name
and a.up_ex='1' and ex_code is not null and b.opposite='0' -- null则没有,不能生成关系
union
-- 下行-站与单元relation
select distinct b.ex_code en_road_node_id,b.opposite_node_code ex_road_node_id ,'0' miles,'166' version, '190819' valid_date
from td_basic_station_branch a,td_basic_mile_file b
where a.station_name=b.ex_alias_name
and a.down_en='1' and ex_code is not null and b.opposite='0'
union
-- 下行-单元与站relation
select distinct b.opposite_node_code en_road_node_id,b.en_code ex_road_node_id ,b.miles miles,'166' version, '190819' valid_date
from td_basic_station_branch a,td_basic_mile_file b
where a.station_name=b.en_alias_name
and a.down_ex='1' and en_code is not null and b.opposite='0'
union
-- link-relation
select b.unit_node_code en_road_node_id,c.unit_node_code ex_road_node_id ,b.miles miles,'166' version, '190819' valid_date
from td_basic_road_link a,td_basic_mile_file b,td_basic_mile_file c
where a.en_start_station=b.en_station_name and a.en_end_station=b.ex_station_name and
a.ex_start_station=c.en_station_name and a.ex_end_station=c.ex_station_name
;
--------------------------------
-- 费率表
--------------------------------
-- update fee_file
update td_basic_fee_file a set unit_node_code=(select unit_node_code from td_basic_mile_file b where a.mile_file_id=b.id)
,opposite_node_code=(select opposite_node_code from td_basic_mile_file b where a.mile_file_id=b.id);
drop table td_province_price;
create table td_province_price as
select substr(unit_node_code,1,7) toll_road_id,substr(unit_node_code,1,11) section_id,unit_node_code toll_Interval_Id,
vehicle_Type,price fee,'166' version, '190819' valid_date
from td_basic_fee_file;
CREATE DEFINER=`motor`@`%` PROCEDURE `NewProc`()
BEGIN
-- 自定义变量
-- declare stopflag int default 0;
DECLARE done BOOLEAN DEFAULT 0 ;
-- DECLARE var_me DOUBLE DEFAULT NULL ;
-- DECLARE var_pay_time TIMESTAMP DEFAULT NULL ;
DECLARE var_en_name VARCHAR (100) DEFAULT NULL ;
DECLARE var_ex_name VARCHAR (100) DEFAULT NULL ;
DECLARE v_next_id VARCHAR (100) DEFAULT NULL ;
DECLARE v_id VARCHAR (100) DEFAULT NULL ;
DECLARE v_name VARCHAR (100) DEFAULT NULL ;
DECLARE v_count int DEFAULT 0 ;
DECLARE v_count2 int DEFAULT 0 ;
DECLARE v_is_station BOOLEAN DEFAULT false;
DECLARE v_miles VARCHAR (100) DEFAULT NULL ;
declare cur CURSOR for select en_alias_name,ex_alias_name,next_id,miles from td_basic_mile_file;
-- 1 游标是保存查询结果的临时内存区域
-- 2 游标变量uname_cur保存了查询的临时结果,实际上就是查询结果集
-- declare continue handler for not found set stopflag=1;
declare continue handler for not found set done=1;
-- DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1 ;
-- 3 声明句柄,当游标变量中保存的结果都查询一遍(遍历),到达结尾,就把变量stopflag设置为1 用于循环中判断是否遍历结束
#Routine body goes here...
delete from td_node_code;
delete from td_node_relation; -- important!
-- select 1;
open cur; # 打开游标
-- 批读取数据到指定变量上
FETCH cur INTO var_en_name,var_ex_name,v_next_id,v_miles;
while(done=0) DO # 如果游标还没到结尾,就继续循环
begin
# 根据入口生成收费站
select count(*) into v_count2 from td_basic_station where name=var_en_name;
if (v_count2 > 0) then
select id,name into v_id,v_name from td_basic_station where name=var_en_name;
select count(*) into v_count from td_node_code where road_node_name=var_en_name;
if (v_count < 1) then
insert into td_node_code -- (toll_road_id,section_id,road_node_id,road_node_name)
values(substr(v_id,1,7),substr(v_id,1,11),v_id,v_name,'3','0',null,'166','190818');
end if;
set v_is_station = true;
end if;
# 生成收费单元
insert into td_node_code
values(substr(v_id,1,7),substr(v_id,1,11),concat(v_id,'10'),concat(var_en_name,'-',var_ex_name),'1','0',null,'166','190818');
# 生成节点关系
if (v_is_station = true) then
insert into td_node_relation
values(v_id,concat(v_id,'10'),v_miles,'166','190818');
end if;
-- if (v_next_id is null) then
-- select var_ex_name;
-- select id,name into v_id,v_name from td_basic_station where name=var_ex_name;
-- insert into td_node_code
-- values(substr(v_id,1,7),substr(v_id,1,11),v_id,v_name,'3','0',null,'166','190818');
-- end if;
set v_is_station = false;
# 根据出口生成收费站
select count(*) into v_count2 from td_basic_station where name=var_ex_name;
if (v_count2 > 0) then
select id,name into v_id,v_name from td_basic_station where name=var_ex_name;
select count(*) into v_count from td_node_code where road_node_name=var_ex_name;
if (v_count < 1) then
insert into td_node_code
values(substr(v_id,1,7),substr(v_id,1,11),v_id,v_name,'3','0',null,'166','190818');
end if;
set v_is_station = true;
end if;
# 生成节点关系
if (v_is_station = true) then
insert into td_node_relation
values(concat(v_id,'10'),v_id,v_miles,'166','190818');
end if;
FETCH cur INTO var_en_name,var_ex_name,v_next_id,v_miles;
end;
end while;
close cur;
END
转载于:https://my.oschina.net/u/2464371/blog/3097900