手机网站弹出菜单,apache创建WordPress,手机版oa,做网站服务器系统比如#xff1a;有如下一张设备电子围栏位置坐标的表#xff08;tb_equ_point#xff09;。
equ_name:设备电子围栏名称
point_id:点位坐标id
point_x:点位x坐标
point_y:点位y坐标。 附数据#xff1a;
INSERT INTO tb_equ_point (EQU_NAME,…比如有如下一张设备电子围栏位置坐标的表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。
