首页 / ORACLE / Oracle行转列+排序
Oracle行转列+排序
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了Oracle行转列+排序，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含6881字，纯文字阅读大概需要10分钟。
内容图文
drop table biz_bus_station_direct_0711;
--2.将站点数据等放入临时表
create table biz_bus_station_direct_0711 as
select ls.line_no line_no,
bb.line_name line_name,
t1.line_direct line_direct,
s.station_id station_id,
s.station_name station_name,
t.point_num point_num_now,
s.location_x x_now,
s.location_y y_now,
t1.point_num point_num_last,
t1.point_x x_last,
t1.point_y y_last,
s.location_x - t1.point_x x_value,
s.location_y - t1.point_y y_value,
0 direction,
0 rn
from biz_bus_station s
join biz_bus_line_station ls
on ls.station_id = s.station_id
left join biz_bus_line bb on ls.line_no=bb.line_no
left join biz_bus_line_distance t
on t.station_id = s.station_id
and t.line_no = ls.line_no
left join biz_bus_line_distance t1
on t1.line_no = t.line_no
and t1.point_num + 1 = t.point_num
and t.line_direct = t1.line_direct;
/*select ls.line_no line_no,
s.station_id station_id,
s.station_name station_name,
t.point_num point_num_now,
s.location_x x_now,
s.location_y y_now,
t1.point_num point_num_last,
t1.point_x x_last,
t1.point_y y_last,
s.location_x - t1.point_x x_value,
s.location_y - t1.point_y y_value,
0 direction,
0 rn
from biz_bus_station s
join biz_bus_line_station ls
on ls.station_id = s.station_id
left join biz_bus_line_distance t
on t.station_id = s.station_id
and t.line_no = ls.line_no
left join biz_bus_line_distance t1
on t1.line_no = t.line_no
and t1.point_num + 1 = t.point_num
and t.line_direct = t1.line_direct;*/
--where s.station_name=‘安徽工程大学-2‘
--order by ls.line_no, t.point_num;
--3.新增途径站点字段
ALTER TABLE biz_bus_station_direct_0711 ADD Station_DIRECT CLOB;
--4.创建相关索引
create index idx_bus_station_line_no on biz_bus_station_direct_0711(line_no,station_id);
--5.给行号赋值
begin
for t2 in (select t1.line_no,t1.station_id,rank() over(partition by replace(replace(t1.station_name,‘-1‘,‘‘),‘-2‘,‘‘) order by t1.line_no,t1.station_id) rn from biz_bus_station_direct_0711 t1) loop
update biz_bus_station_direct_0711 t
set t.rn=t2.rn
where t.line_no=t2.line_no
and t.station_id=t2.station_id;
commit;
end loop;
end;
--6.根据行号，逐行取值，赋方向值
/*cosθ=向量a.向量b/|向量a|×|向量b|
=(x1x2+y1y2)/[√(x12+y12)*√(x22+y22)]
夹角小于180度，则cosθ大于0，表示当前计算的车辆与RN=1的车辆方向一致
*/
begin
for t2 in (select replace(replace(t1.station_name,‘-1‘,‘‘),‘-2‘,‘‘) station_name,t1.x_value,t1.y_value,t1.rn from biz_bus_station_direct_0711 t1 where t1.rn=1) loop
update biz_bus_station_direct_0711 t
set t.direction=case when t.x_value!=0 and t.y_value!=0 and t2.x_value!=0 and t2.y_value!=0 then (case when (t2.x_value*t.x_value+t2.y_value*t.y_value)/(SQRT(t2.x_value*t2.x_value+t2.y_value*t2.y_value)*SQRT(t.x_value*t.x_value+t.y_value*t.y_value))>0 then 1 else -1 end) else 1 end
where t.rn <=(select max(rn) from biz_bus_station_direct_0711 where replace(replace(station_name,‘-1‘,‘‘),‘-2‘,‘‘)=t2.station_name)
and replace(replace(t.station_name,‘-1‘,‘‘),‘-2‘,‘‘)=t2.station_name;
commit;
end loop;
end;
--7.根据站点给各个线路赋值途径站点（行转列+排序）
begin
for t2 in (
select A.line_no,A.line_direct,max(KEY) Station_DIRECT from (
select t.line_no,t.line_direct,
WMSYS.WM_CONCAT(t.station_name) OVER(PARTITION BY t.line_no,t.line_direct ORDER BY t.line_direct,t.point_num) KEY,
row_number() over(PARTITION BY t.line_no,t.line_direct ORDER BY t.line_direct,t.point_num) rs
from (
select t1.line_no,t1.line_direct,t1.point_num,t2.station_name from BIZ_BUS_LINE_DISTANCE t1
join biz_bus_station t2 on t1.station_id=t2.station_id
order by t1.line_direct,t1.point_num) t
) A
group by A.line_no,A.line_direct
) loop
update biz_bus_station_direct_0711 bbs
set bbs.station_direct=t2.station_direct
where bbs.line_no=t2.line_no and bbs.line_direct=t2.line_direct;
commit;
end loop;
end;
存储过程：
create or replace procedure usp_bus_station_direct_update is
/*
--名称：公交站点方向同步
--功能：将各个公交站点的途径公交车辆相同方向置1，不同方向置-1
--作者：赛太岁
--编写时间：2016-07-12
*/
begin
--删除中间表数据
delete from biz_bus_station_direct;
--将公交站点等数据全量插入中间表
insert into biz_bus_station_direct
select ls.line_no line_no,
bb.line_name line_name,
t1.line_direct line_direct,
s.station_id station_id,
s.station_name station_name,
t.point_num point_num_now,
s.location_x x_now,
s.location_y y_now,
t1.point_num point_num_last,
t1.point_x x_last,
t1.point_y y_last,
s.location_x - t1.point_x x_value,
s.location_y - t1.point_y y_value,
0 direction,
0 rn,
‘‘ STATION_DIRECT,
sysdate create_time
from biz_bus_station s
join biz_bus_line_station ls
on ls.station_id = s.station_id
left join biz_bus_line bb on ls.line_no=bb.line_no
left join biz_bus_line_distance t
on t.station_id = s.station_id
and t.line_no = ls.line_no
left join biz_bus_line_distance t1
on t1.line_no = t.line_no
and t1.point_num + 1 = t.point_num
and t.line_direct = t1.line_direct;
--给行号赋值
for t2 in (select t1.line_no,t1.station_id,rank() over(partition by replace(replace(t1.station_name,‘-1‘,‘‘),‘-2‘,‘‘) order by t1.line_no,t1.station_id) rn from biz_bus_station_direct t1) loop
update biz_bus_station_direct t
set t.rn=t2.rn
where t.line_no=t2.line_no
and t.station_id=t2.station_id;
end loop;
--根据行号，逐行取值，赋方向值
/*cosθ=向量a.向量b/|向量a|×|向量b|
=(x1x2+y1y2)/[√(x12+y12)*√(x22+y22)]
夹角小于180度，则cosθ大于0，表示当前计算的车辆与RN=1的车辆方向一致
*/
for t2 in (select replace(replace(t1.station_name,‘-1‘,‘‘),‘-2‘,‘‘) station_name,t1.x_value,t1.y_value,t1.rn from biz_bus_station_direct t1 where t1.rn=1) loop
update biz_bus_station_direct t
set t.direction=case when t.x_value!=0 and t.y_value!=0 and t2.x_value!=0 and t2.y_value!=0 then (case when (t2.x_value*t.x_value+t2.y_value*t.y_value)/(SQRT(t2.x_value*t2.x_value+t2.y_value*t2.y_value)*SQRT(t.x_value*t.x_value+t.y_value*t.y_value))>0 then 1 else -1 end) else 1 end
where t.rn <=(select max(rn) from biz_bus_station_direct where replace(replace(station_name,‘-1‘,‘‘),‘-2‘,‘‘)=t2.station_name)
and replace(replace(t.station_name,‘-1‘,‘‘),‘-2‘,‘‘)=t2.station_name;
end loop;
--根据站点给各个线路赋值途径站点
for t2 in (
select A.line_no,A.line_direct,max(KEY) Station_DIRECT from (
select t.line_no,t.line_direct,
WMSYS.WM_CONCAT(t.station_name) OVER(PARTITION BY t.line_no,t.line_direct ORDER BY t.line_direct,t.point_num) KEY,
row_number() over(PARTITION BY t.line_no,t.line_direct ORDER BY t.line_direct,t.point_num) rs
from (
select t1.line_no,t1.line_direct,t1.point_num,t2.station_name from BIZ_BUS_LINE_DISTANCE t1
join biz_bus_station t2 on t1.station_id=t2.station_id
order by t1.line_direct,t1.point_num) t
) A
group by A.line_no,A.line_direct
) loop
update biz_bus_station_direct bbs
set bbs.station_direct=t2.station_direct
where bbs.line_no=t2.line_no and bbs.line_direct=t2.line_direct;
end loop;
commit;
--异常处理
exception
when others then
rollback;
end usp_bus_station_direct_update;
Oracle行转列+排序
标签：
本文系统来源：http://blog.csdn.net/u010758605/article/details/51886464
内容总结
以上是互联网集市为您收集整理的Oracle行转列+排序全部内容，希望文章能够帮你解决Oracle行转列+排序所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。
来源：【匿名】