mysql2pgsql.perl_mysql2pgsql.perl

{"moduleinfo":{"card_count":[{"count_phone":1,"count":1}],"search_count":[{"count_phone":5,"count":5}]},"card":[{"des":"PostgreSQL被业界誉为“最先进的开源数据库”,面向企业复杂SQL处理的OLTP在线事务处理场景,支持NoSQL数据类型(JSON/XML/hstore)、支持GIS地理信息处理。","link1":"https://www.aliyun.com/product/rds/postgresql","link":"https://www.aliyun.com/product/rds/postgresql","icon":"https://img.alicdn.com/tfs/TB1TEFrwamWBuNjy1XaXXXCbXXa-200-200.png","btn2":"了解价格","tip":"轻松处理空间信息,强大NoSQL兼容,支持全文搜索,支持OSS云存储扩展,支持数据仓库,尽在PostgreSQL! 产品动态发布立即查看","btn1":"立即开通","link2":"https://www.aliyun.com/price/product#/rds/detail?hash=postgresql","title":"云数据库RDS PostgreSQL版"}],"search":[{"txt":"PostgreSQL产品文档","link":"https://help.aliyun.com/document_detail/26092.html"},{"txt":"ECS上的自建数据库迁移指导","link":"https://help.aliyun.com/document_detail/26627.html"},{"txt":"快速入门PostgreSQL","link":"https://help.aliyun.com/document_detail/26151.html"},{"txt":"PostgreSQL的进阶之路","link":"https://yq.aliyun.com/topic/118"},{"txt":"产品动态","link":"https://www.aliyun.com/product/new?source_type=out_sousuo_feature_0716"}],"countinfo":{"search":{"length_pc":0,"length":0},"card":{"length_pc":0,"length":0}},"simplifiedDisplay":"newEdition","newCard":[{"link":"https://img.alicdn.com/tfs/TB1KM.qIeH2gK0jSZJnXXaT1FXa-1740-328.png","icon":"","contentLink":"https://www.aliyun.com/product/rds/postgresql","title":"云数据库RDS PostgreSQL版","des":"PostgreSQL被业界誉为“最先进的开源数据库”,面向企业复杂SQL处理的OLTP在线事务处理场景,支持NoSQL数据类型(JSON/XML/hstore)、支持GIS地理信息处理。","btn1":"产品详情","link1":"https://www.aliyun.com/product/rds/postgresql","btn2":"管理控制台","link2":"https://rdsnext.console.aliyun.com/?spm=5176.7920951.1393245.4.21614ce1AbbK2V#/rdsList/cn-hangzhou/basic/","btn3":"在线咨询","link3":"https://www.aliyun.com/core/online-consult?from=yAAzuk58kF","infoGroup":[{"infoName":"最新活动","infoContent":{"firstContentName":"PostgreSQL低至9.9元","firstContentLink":"https://www.aliyun.com/database/dbfirstbuy#J_2351226990","lastContentName":"ECS+MySQL 0.7元/日","lastContentLink":"https://www.aliyun.com/1111/enterprise"}},{"infoName":"快速入门","infoContent":{"firstContentLink":"https://help.aliyun.com/document_detail/26151.html","firstContentName":"使用流程","lastContentLink":"https://help.aliyun.com/document_detail/96781.html","lastContentName":"数据迁移"}},{"infoName":"最佳实践","infoContent":{"firstContentLink":"https://help.aliyun.com/document_detail/96772.html","firstContentName":"数据库备份","lastContentLink":"https://help.aliyun.com/document_detail/147837.html","lastContentName":"丰富插件类型"}},{"infoName":"产品推荐","infoContent":{"firstContentName":"云原生数据仓库PG版","firstContentLink":"https://www.aliyun.com/product/gpdb","lastContentName":"数据传输DTS","lastContentLink":"https://www.aliyun.com/product/dts"}}],"ifIcon":"img","iconImg":"https://img.alicdn.com/tfs/TB1ASH107L0gK0jSZFtXXXQCXXa-200-200.png"}]}

{"$env":{"JSON":{}},"$page":{"env":"production"},"$context":{"moduleinfo":{"card_count":[{"count_phone":1,"count":1}],"search_count":[{"count_phone":5,"count":5}]},"card":[{"des":"PostgreSQL被业界誉为“最先进的开源数据库”,面向企业复杂SQL处理的OLTP在线事务处理场景,支持NoSQL数据类型(JSON/XML/hstore)、支持GIS地理信息处理。","link1":"https://www.aliyun.com/product/rds/postgresql","link":"https://www.aliyun.com/product/rds/postgresql","icon":"https://img.alicdn.com/tfs/TB1TEFrwamWBuNjy1XaXXXCbXXa-200-200.png","btn2":"了解价格","tip":"轻松处理空间信息,强大NoSQL兼容,支持全文搜索,支持OSS云存储扩展,支持数据仓库,尽在PostgreSQL! 产品动态发布立即查看","btn1":"立即开通","link2":"https://www.aliyun.com/price/product#/rds/detail?hash=postgresql","title":"云数据库RDS PostgreSQL版"}],"search":[{"txt":"PostgreSQL产品文档","link":"https://help.aliyun.com/document_detail/26092.html"},{"txt":"ECS上的自建数据库迁移指导","link":"https://help.aliyun.com/document_detail/26627.html"},{"txt":"快速入门PostgreSQL","link":"https://help.aliyun.com/document_detail/26151.html"},{"txt":"PostgreSQL的进阶之路","link":"https://yq.aliyun.com/topic/118"},{"txt":"产品动态","link":"https://www.aliyun.com/product/new?source_type=out_sousuo_feature_0716"}],"countinfo":{"search":{"length_pc":0,"length":0},"card":{"length_pc":0,"length":0}},"simplifiedDisplay":"newEdition","newCard":[{"link":"https://img.alicdn.com/tfs/TB1KM.qIeH2gK0jSZJnXXaT1FXa-1740-328.png","icon":"","contentLink":"https://www.aliyun.com/product/rds/postgresql","title":"云数据库RDS PostgreSQL版","des":"PostgreSQL被业界誉为“最先进的开源数据库”,面向企业复杂SQL处理的OLTP在线事务处理场景,支持NoSQL数据类型(JSON/XML/hstore)、支持GIS地理信息处理。","btn1":"产品详情","link1":"https://www.aliyun.com/product/rds/postgresql","btn2":"管理控制台","link2":"https://rdsnext.console.aliyun.com/?spm=5176.7920951.1393245.4.21614ce1AbbK2V#/rdsList/cn-hangzhou/basic/","btn3":"在线咨询","link3":"https://www.aliyun.com/core/online-consult?from=yAAzuk58kF","infoGroup":[{"infoName":"最新活动","infoContent":{"firstContentName":"PostgreSQL低至9.9元","firstContentLink":"https://www.aliyun.com/database/dbfirstbuy#J_2351226990","lastContentName":"ECS+MySQL 0.7元/日","lastContentLink":"https://www.aliyun.com/1111/enterprise"}},{"infoName":"快速入门","infoContent":{"firstContentLink":"https://help.aliyun.com/document_detail/26151.html","firstContentName":"使用流程","lastContentLink":"https://help.aliyun.com/document_detail/96781.html","lastContentName":"数据迁移"}},{"infoName":"最佳实践","infoContent":{"firstContentLink":"https://help.aliyun.com/document_detail/96772.html","firstContentName":"数据库备份","lastContentLink":"https://help.aliyun.com/document_detail/147837.html","lastContentName":"丰富插件类型"}},{"infoName":"产品推荐","infoContent":{"firstContentName":"云原生数据仓库PG版","firstContentLink":"https://www.aliyun.com/product/gpdb","lastContentName":"数据传输DTS","lastContentLink":"https://www.aliyun.com/product/dts"}}],"ifIcon":"img","iconImg":"https://img.alicdn.com/tfs/TB1ASH107L0gK0jSZFtXXXQCXXa-200-200.png"}]}}

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
这个 SQL 语句有一些可优化的地方,可以考虑以下几点: 1. 尽可能使用 JOIN 代替子查询。例如,第一个子查询中的 `pub_ports` 表可以通过 JOIN 进行连接。 2. 避免使用 `array_agg` 和 `unnest` 函数。这些函数需要进行大量的数组转换操作,非常耗费资源。如果可以,可以考虑使用其他方式将数组数据按逗号拼接。 3. 在使用 GROUP BY 时,只选择必要的列。不需要 GROUP BY 的列可以在外部查询中进行选择。 4. 对于多个表的连接,可以考虑使用合适的索引来提高查询性能。 下面是优化后的 SQL 语句: ``` SELECT pot.pot_type_name, ttpw.ttpw_addr AS twlPortName, ttpw.ttpw_addr_code AS twlPortCode, tmrGroup.tmr_material_name, array_to_string(ids, ',') AS ids, tms_whe.twl_leave_stock FROM ( SELECT ttpw_tmr_id, array_agg(DISTINCT tmr.tmr_id) AS ids, ttpw_addr, ttpw_addr_code, pot_type_name, tmrGroup.tmr_material_name FROM tms_transport_plan_work ttpw JOIN tms_material_ref tmr ON tmr.tmr_id = ttpw.ttpw_tmr_id JOIN tms_material_ref tmrGroup ON tmrGroup.tmr_id = tmr.parent_tmr_id JOIN pub_ports pot ON pot.pot_regcd = ttpw.ttpw_addr_code AND pot.pot_cnname = ttpw.ttpw_addr WHERE ttpw_plan_amount > 0 GROUP BY ttpw_tmr_id, ttpw_addr, ttpw_addr_code, pot_type_name, tmrGroup.tmr_material_name ) AS info LEFT JOIN ( SELECT twl_material_id, twl_port_code, SUM(twl_leave_stock) AS twl_leave_stock FROM tms_whe_control WHERE pot_type_name = '合作港口' GROUP BY twl_material_id, twl_port_code ) AS tms_whe ON tms_whe.twl_material_id = ANY(ids) AND tms_whe.twl_port_code = twlPortCode ORDER BY twlPortName ``` 这个 SQL 语句使用了 JOIN 来代替子查询,并避免了使用 `array_agg` 和 `unnest` 函数。同时,只选择必要的列进行 GROUP BY,使用合适的索引来优化查询性能。

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值