随笔分类 - sql
摘要：-- sum() over(partition by ... order by ...)SELECT len/sum(len)over(partition by road_id) param from rs; -- min() over(partition by ... order by ...)S
阅读全文
摘要：SELECT len/sum(len)over(partition by road_id) param from road_jcpd_section
阅读全文
摘要：SELECT rl.road_code,string_agg(distinct rs.tech_level_label, ',') from road_lst rlleft join road_section rs on rl.id = rs.road_idwhere rs.tech_level_l
阅读全文
摘要：SELECT st_x(cast(point(100, 200) as geometry)) SELECT st_y(cast(point(100, 200) as geometry)) 搜索 复制
阅读全文
摘要：-- 截取指定字符最后出现的前半部分select reverse(substr(reverse('/C107341723B_145404/01/0_20220802_145505552.jpg'),position('/' in reverse('/C107341723B_145404/01/0_2
阅读全文
摘要：1、建表语句 DROP TABLE IF EXISTS "public"."json_param";CREATE TABLE "public"."json_param" ( "id" int4 NOT NULL, "json" text COLLATE "pg_catalog"."default")
阅读全文
摘要：UPDATE road_jcpd_record a set road_lst_id = b.id from road_lst b where b.road_code = a.road_code and a.road_code = '' 搜索 复制
阅读全文
摘要：CREATE OR REPLACE FUNCTION "public"."generate_road_list_id_to_jcpddas"("road_code_param" varchar) RETURNS "pg_catalog"."int4" AS $BODY$declare returnv
阅读全文
摘要：SELECT u.gender , count(*) FILTER (WHERE u.age > 20 and u.name like '%张') AS playedFROM users uGROUP BY u.gender; 搜索 复制
阅读全文
摘要：SELECT * from ( SELECT ROW_NUMBER() OVER(PARTITION by wj ORDER BY px desc)idx,px,wj from table ) ord where ord.idx = 1 wj ：关联外键 px ：排序字段 ROW_NUMBER：该函
阅读全文
摘要：GREATEST获取最大值SELECT GREATEST(3,5,1,8,33,99,34,55,67,43);######################################################LEAST获取最小值SELECT LEAST(3,5,1,8,33,99,34,
阅读全文
摘要：一、sql语句 DROP TABLE IF EXISTS "public"."student";CREATE TABLE "public"."student" ( "id" int4 NOT NULL, "name" varchar(255) COLLATE "pg_catalog"."defaul
阅读全文
摘要：SELECT company.id,company.zz,snap.xydm,snap."companyName", p.xm fr from ( SELECT a.id,string_agg( case when d.zzlx = 1 then '路基路面' when d.zzlx = 2 the
阅读全文
摘要：pgsql数据库语法 SELECT a.id,a.name as "companyName",a.xydm,a.fddbr,string_agg(case when d.zzlx = 1 then '路基路面'when d.zzlx = 2 then '桥梁'when d.zzlx = 3 then
阅读全文
摘要：SELECT * from bridge where concat(road_short_name,name,code) like '%xx镇%' 搜索 复制
阅读全文
