比如:有如下一张设备电子围栏位置坐标的表(tb_equ_point)。
equ_name:设备电子围栏名称
point_id:点位坐标id
point_x:点位x坐标
point_y:点位y坐标。
附数据:
INSERT INTO "tb_equ_point" ("EQU_NAME", "POINT_ID", "POINT_X", "POINT_Y") VALUES ('轻烃二列回收装置', '1707192429914726402', '84.2097884178748', '41.45319253343837');
INSERT INTO "tb_equ_point" ("EQU_NAME", "POINT_ID", "POINT_X", "POINT_Y") VALUES ('轻烃二列回收装置', '1707192429918920706', '84.20991864872965', '41.45267977449036');
INSERT INTO "tb_equ_point" ("EQU_NAME", "POINT_ID", "POINT_X", "POINT_Y") VALUES ('轻烃二列回收装置', '1707192429927309314', '84.21122698760112', '41.45286915256579');
INSERT INTO "tb_equ_point" ("EQU_NAME", "POINT_ID", "POINT_X", "POINT_Y") VALUES ('轻烃二列回收装置', '1707192429931503618', '84.21116556273135', '41.453349887040574');
INSERT INTO "tb_equ_point" ("EQU_NAME", "POINT_ID", "POINT_X", "POINT_Y") VALUES ('乙烷天然气增压站', '1707192429960863746', '84.20783836821487', '41.45526140938881');
INSERT INTO "tb_equ_point" ("EQU_NAME", "POINT_ID", "POINT_X", "POINT_Y") VALUES ('乙烷天然气增压站', '1707192429969252354', '84.20791184166993', '41.45484777039682');
INSERT INTO "tb_equ_point" ("EQU_NAME", "POINT_ID", "POINT_X", "POINT_Y") VALUES ('乙烷天然气增压站', '1707192429973446657', '84.20917631597212', '41.45499000780251');
INSERT INTO "tb_equ_point" ("EQU_NAME", "POINT_ID", "POINT_X", "POINT_Y") VALUES ('乙烷天然气增压站', '1707192429977640961', '84.20908976877685', '41.45538944455889');
INSERT INTO "tb_equ_point" ("EQU_NAME", "POINT_ID", "POINT_X", "POINT_Y") VALUES ('液化气罐区', '1707192430002806786', '84.20723072875117', '41.45346426655951');
INSERT INTO "tb_equ_point" ("EQU_NAME", "POINT_ID", "POINT_X", "POINT_Y") VALUES ('液化气罐区', '1707192430007001090', '84.20740838206585', '41.45246187545852');
INSERT INTO "tb_equ_point" ("EQU_NAME", "POINT_ID", "POINT_X", "POINT_Y") VALUES ('液化气罐区', '1707192430015389698', '84.20841913655941', '41.45256453477293');
INSERT INTO "tb_equ_point" ("EQU_NAME", "POINT_ID", "POINT_X", "POINT_Y") VALUES ('液化气罐区', '1707192430019584001', '84.20822649186182', '41.45357496335363');
INSERT INTO "tb_equ_point" ("EQU_NAME", "POINT_ID", "POINT_X", "POINT_Y") VALUES ('轻烃罐区', '1707192430044749826', '84.20872921576651', '41.45318604610804');
INSERT INTO "tb_equ_point" ("EQU_NAME", "POINT_ID", "POINT_X", "POINT_Y") VALUES ('轻烃罐区', '1707192430048944130', '84.20884409341907', '41.45257962408824');
INSERT INTO "tb_equ_point" ("EQU_NAME", "POINT_ID", "POINT_X", "POINT_Y") VALUES ('轻烃罐区', '1707192430220910594', '84.2092407921853', '41.45263007114594');
INSERT INTO "tb_equ_point" ("EQU_NAME", "POINT_ID", "POINT_X", "POINT_Y") VALUES ('轻烃罐区', '1707192430225104898', '84.20911756856574', '41.453243652526155');
INSERT INTO "tb_equ_point" ("EQU_NAME", "POINT_ID", "POINT_X", "POINT_Y") VALUES ('丙烷罐区', '1707192430250270722', '84.20896730497799', '41.451822676291805');
INSERT INTO "tb_equ_point" ("EQU_NAME", "POINT_ID", "POINT_X", "POINT_Y") VALUES ('丙烷罐区', '1707192430258659329', '84.2090131474932', '41.451590037400294');
INSERT INTO "tb_equ_point" ("EQU_NAME", "POINT_ID", "POINT_X", "POINT_Y") VALUES ('丙烷罐区', '1707192430262853633', '84.2095576735967', '41.45164557396578');
INSERT INTO "tb_equ_point" ("EQU_NAME", "POINT_ID", "POINT_X", "POINT_Y") VALUES ('丙烷罐区', '1707192430267047938', '84.20950934658842', '41.451900631780624');
INSERT INTO "tb_equ_point" ("EQU_NAME", "POINT_ID", "POINT_X", "POINT_Y") VALUES ('装车站危废库房区域', '1707192430292213762', '84.20630670236255', '41.45587361982766');
INSERT INTO "tb_equ_point" ("EQU_NAME", "POINT_ID", "POINT_X", "POINT_Y") VALUES ('装车站危废库房区域', '1707192430296408066', '84.20666273263066', '41.45394183801273');
INSERT INTO "tb_equ_point" ("EQU_NAME", "POINT_ID", "POINT_X", "POINT_Y") VALUES ('装车站危废库房区域', '1707192430304796674', '84.20785692954897', '41.45406762412216');
INSERT INTO "tb_equ_point" ("EQU_NAME", "POINT_ID", "POINT_X", "POINT_Y") VALUES ('装车站危废库房区域', '1707192430308990978', '84.20749154021959', '41.45595024021673');
现在想把数据按名称合并,一组x坐标和y坐标放在一起,用 “,” 隔开,x,y的顺序要对应。
效果如下图:
sql如下:
SELECTequ_name,LISTAGG ( POINT_X, ',' ) WITHIN GROUP ( ORDER BY point_id ) AS POINT_X,LISTAGG ( POINT_Y, ',' ) WITHIN GROUP ( ORDER BY point_id ) AS POINT_Y
FROMtb_equ_point
GROUP BYequ_name
解析:
Oracle的LISTAGG函数将多个结果合并到一起,示例如下:
SELECT name, LISTAGG(x, ',') WITHIN GROUP (ORDER BY x) AS x_list, LISTAGG(y, ',') WITHIN GROUP (ORDER BY y) AS y_list
FROM table_name
GROUP BY name
其中,table_name是你的表名,x_list和y_list分别是按name分组后合并的x和y结果。如果需要在结果中添加分隔符,可以在LISTAGG函数的第一个参数中指定。例如,如果想在结果中使用“|”作为分隔符,可以将函数改为:
LISTAGG(x, '|') WITHIN GROUP (ORDER BY x) AS x_list
同样的,也可以使用其他聚合函数,如MAX、MIN、AVG等。
注:合并的字段,x,y要顺序对应的话,要有一个统一的标识point_id。