|
1，
with t as (select poi.mid poi_id,link.link_id link_id from poi,nav_link_graphy link where poi.kind not like '%BF00%' and poi.kind not like '%BB86%' and poi.kind not like '%2F0105%' and poi.kind not like '%8401%' and poi.kind not like '%8085%' and
st_dwithin(poi.geometry::geography,link.geom,3.0)) select 'poi' tn,t.poi_id fid,string_agg(t.link_id::varchar,'|') r__agg__fid,'nav_link' r__agg__tn from t group by t.poi_id;
注：string_agg聚合函数，可以放在select之后，且语句中有groupby分组，对于多行多个要素之间存在关系,需要一起报出时
又如：
select n1.name_id,string_agg(n2.name_id,'|') r_agg_fid,'nav_name' as r_agg_tn from nav_namen1,nav_name n2 where n1.name_py=n2.name_py and
n1.name_ch<>n2.name_chand n1.ctid<n2.ctid group by n1.name_id
2，
SELECT 'NAV_LINK' as tn,nav_link.link_id as fid,'sp_class取值错误' err_msg from nav_link WHERE sp_class is not null and sp_class <>'' and substring(sp_class,1,1) not similar to '[012345678]'
UNION ALL
SELECT 'NAV_LINK' as tn,'f_speed取值错误' err_msg from nav_link WHERE f_speed is not null and f_speed !='' and f_speed not similar to '(s|[0-9]*),(s|0|1|2|3|4|5),(s|0|1)'
UNION ALL
SELECT 'NAV_LINK' as tn,'t_speed取值错误' err_msg from nav_link WHERE t_speed is not null and t_speed !='' and t_speed not similar to '(s|[0-9]*),(s|0|1)'
3，
WITH t1 as(SELECT s_nid as nid from nav_link UNION ALL SELECT e_nid as nid FROM nav_link),
t2 as (SELECT nid from t1 GROUP BY nid HAVING "count"(1)=1)
SELECT 'NAV_LINK' as tn,nav_link.link_id as fid from nav_link WHERE nav_link.s_nid in (SELECT nid from t2) AND
nav_link.e_nid in (SELECT nid from t2)
4，
SELECT 'NAV_BRANCH' as tn,branch_id as fid from NAV_BRANCH WHERE not EXISTS(
SELECT 1 from nav_link WHERE nav_link.link_id = nav_branch.in_linkid)
5，
SELECT link_id from nav_link WHERE string_to_array(nav_link.form,';')@>array['50']
注：@>array['50']表示包含
6，
SELECT 'NAV_LINK' as tn,link_id as fid FROM NAV_LINK
WHERE string_to_array(form,';')&&array['0','1','2','10','11','12','13','14','15','16','17','18','20','21','22','23','24','30','31','32','33','34','35','36','37','38','39','50','51','52','54','60','80']=false
注：&&表示有交集.
例如：@>array['33','50'] ---这个是同时包含两个才返回true
string_to_array(form,';')&&array['33','50'] --&&是包含其中一个就满足
注意他们的反面：@>array['33','50']=false，这表示不包含其中一个就返回false，而&&array['33','50']=false，这表示同时不包含两个
7，
SELECT 'NAV_REALIMAGE' as tn,REAL_ID as fid,'image_type取值错误' err_msg from NAV_REALIMAGE where image_type::VARCHAR not SIMILAR TO '[01]'
8，
SELECT 'NAV_REALIMAGE' as tn,REAL_ID as fid from NAV_REALIMAGE where arr_code is not null and arr_code!='' and real_code is not null and real_code !=''
and ("substring"(arr_code from 2 for "char_length"(arr_code)-1) != "substring"(real_code from 2 for "char_length"(real_code))-1)
注：substring(field,start,length) ，field为字段，start为开始点（从1开始），length为长度
9，
selectst_astext(st_pointN(geom,5)) as a_location from nav_link
注：请转为wkt字符串,比如某个link的第5个形状点处存在折角较小,比较尖,一般在此处会创建一个点几何
10，
"该连接点驶出的道路至少有条LinkID在NAV_SLOPE表存在，否则报错。"
with a as(SELECT ns.node_id nid,"string_agg"(sln.link_id::VARCHAR,'|') lidfrom nav_slope ns,shd_link_node sln WHERE ns.node_id = sln.node_id and ns.link_id <>sln.link_id GROUP BY ns.node_id )
,b as(SELECT a.nid nid,regexp_split_to_table(a.lid,'|')rid from a )
SELECT 'nav_slope' tn,a.nid fid,a.lid as m__1__link_id from b,a,nav_slope nsp WHERE a.nid=b.nid and b.rid=nsp.link_id GROUP BY a.nid,a.lid HAVING "count"(1)=0
"string_agg"先聚合，再使用regexp_split_to_table将聚合在一起使用‘|’分隔的一条记录，拆分成多条记录存于子表中，也可以使用regexp_split_to_array函数拆分成数组，array_length求得数组长度
11，
select * from a where a.nid>100 or a.lid<10000 ； select * from a,b where a.nid>b.nid unionselect * from a,b where a.lid< b.lid
or单表内使用时效率还好，多表连接时使用union替换
12，
select f1,f2,...,分析函数（例如row_num(),count(1)）over(partition by f3 oder by f4) from table;
与group by 区别：1，select f1,后面字段不限制 2，count计数结果在一组内的每一条记录均有一个值，而group by会合成一条记录
例句：with a as (
select DETAIL_ID,group_id,link_id,count(1)over(partition by p.DETAIL_ID,p.group_id order by SEQ_NUM) m_seq,
row_number()over(partition by p.DETAIL_ID,p.group_id order by SEQ_NUM) seq from NAV_RESTRICTION_PASS p ),
link as (
select t1.DETAIL_ID,t1.link_id l1,t2.link_id l2 from a t1,a t2 where t1. DETAIL_ID=t2.DETAIL_ID and t1.group_id=t2.group_id and t1.seq+1=t2.seq
union all
select a.detail_id,a.link_id l1,d.out_linkid l2 from NAV_RESTRICTION_DETAIL d,a where d.detail_id=a.detail_id and a.seq=a.m_seq
union all
select a.detail_id,r.in_linkid l1,a.link_id l2 from NAV_RESTRICTION r,NAV_RESTRICTION_DETAIL d,a where r.RESTRIC_ID=d.RESTRIC_ID and d.detail_id=a.detail_id and a.seq=1)
select l.detail_id,l.l1,l.l2 from link l where not exists (select 1 from shd_link_go_link s where l.l1=s.in_link_id and l.l2=s.out_link_id)
13,
如果一条弧段上制作了坡度信息，则该点连接的其他弧段中至少有一条link上也应该存在坡度信息，否则报log
with b as(SELECT sln2.node_id,sln2.link_id,ns.slope_id fid from nav_slope ns,shd_link_node sln,shd_link_node sln2
WHERE ns.link_id=sln.link_id and ns.node_id= sln.node_id and sln2.node_id=sln.node_id and sln2.link_id<>sln.link_id),
c as(SELECT b.fid,string_agg(DISTINCT b.link_id,'|') link_id,string_agg(DISTINCT b.node_id,'|') node_id FROM b GROUP BY b.fid)
(
SELECT 'nav_slope' tn,c.fid fid from c
EXCEPT
SELECT 'nav_slope' tn,c.fid fid FROM c WHERE EXISTs(SELECT 1 from nav_slope WHERE string_to_array(c.link_id,'|')@>string_to_array(nav_slope.link_id,' '))
)
注意：至少的表达，先求出包含坡度信息的表，然后求差集即错误的
14,查找NAV_LINK_LIMIT中TYPE=4的道路两端连接的弧段，如果一个端点连接的弧段只能进入当前节点或只能退出当前节点，则该点连接道路的TYPE！=4则报错
with sg as ( select ln.link_id,node_id from shd_link_node ln,NAV_LINK_LIMIT ll where ln.link_id=ll.link_id and ll.type=4 ),
tp as (
select
case when dir=2 and s_e=1 then 0
when dir=2 and s_e=0 then 1
when dir=3 and s_e=1 then 1
when dir=3 and s_e=0 then 0
when dir=1 then 2
end mark,ln.link_id,ln.node_id,count(1)over(partition by ln.node_id) gc
from shd_link_node ln where exists (select 1 from sg where ln.node_id=sg.node_id and ln.link_id<>sg.link_id))
,fx as ( select link_id,node_id,mark,count(1)over(partition by node_id,mark) mc,gc from tp )
select 'nav_link' tn,link_id fid from fx where mc=gc and mark in (0,1)
15,
获取两个NODE的坐标，如果两个点(图廓点除外)的距离小于3则报错。程序检查时不检查的情况有： 1、排除当立交点之间都是Node点的情况。 4、排除图幅接边的图廓点
with t as (select z1.link_id lid1,z1.start_end se1,z2.link_id lid2,z2.start_end se2 from NAV_ZLEVEL_LINK z1,NAV_ZLEVEL_LINK z2
where z1.start_end>0 and z2.start_end>0 and z1.table_name='NAV_LINK' and z2.table_name=z1.table_name and z1.zlevel_id=z2.zlevel_id),
n as (select case t.se1 when 1 then l1.s_nid else l1.e_nid end nd1,
case t.se2 when 1 then l2.s_nid else l2.e_nid end nd2 from nav_link l1,t,nav_link l2
where t.lid1=l1.link_id and t.lid2=l2.link_id),
sn as (select case when nd1>nd2 then nd1 else nd2 end id1,case when nd1>nd2 then nd2 else nd1 end id2 from n )
SELECT 'nav_node' tn,a.node_id as fid,'nav_node' as r__1__tn,b.node_id as r__1__fid from SHD_NODE_GRAPHY a,SHD_NODE_GRAPHY b
WHERE st_dwithin(a.geom,b.geom,3) and a.node_id>b.node_id and a.adjoin_nid<>b.node_id
and not exists(select 1 from sn where a.node_id=sn.id1 and b.node_id=sn.id2);
16，
pg存储过程输出（调试）语句,RAISE NOTICE
CREATE FUNCTION somefunc() RETURNS integer AS $$
DECLARE
quantity integer := 30;
BEGIN
RAISE NOTICE 'Quantity here is %',quantity; --在这里的数量是30
quantity := 50;
--
-- 创建一个子块
-- http://www.qinglvfenzu.com DECLARE quantity integer := 80; BEGIN RAISE NOTICE 'Quantity here is %',quantity; --在这里的数量是80 END; RAISE NOTICE 'Quantity here is %',quantity; --在这里的数量是50 RETURN quantity; END; $$ LANGUAGE plpgsql; #执行该函数以进一步观察其执行的结果。 postgres=# select somefunc(); NOTICE: Quantity here is 30 NOTICE: Quantity here is 80 NOTICE: Quantity here is 50 somefunc ---------- 50 (1 row) （编辑：李大同）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
