如何实现itinerario表同route_id的Coleta/Entrega交叉表查询

该需求完全可以实现，优先用标准SQL的条件聚合完成行列转换，无需依赖特定数据库的crosstab专有函数，写法兼容MySQL、PostgreSQL、SQL Server等绝大多数主流数据库。

核心逻辑 #

按route_id维度分组，通过条件判断分别提取tipo为Coleta、Entrega两类记录的属性值到独立列，同时对同组下的valor_cobrado字段求和。

以下代码默认同一route_id下Coleta、Entrega类型记录各最多1条，若存在多条同类型记录，可根据业务规则调整聚合取值逻辑。

参考SQL #

SELECT
    route_id,
    MAX(id) AS id,
    -- 提取Coleta类型对应字段
    MAX(CASE WHEN tipo = 'Coleta' THEN tipo END) AS `tipo Coleta`,
    MAX(CASE WHEN tipo = 'Coleta' THEN local END) AS `local Coleta`,
    -- 提取Entrega类型对应字段
    MAX(CASE WHEN tipo = 'Entrega' THEN tipo END) AS `tipo Entrega`,
    MAX(CASE WHEN tipo = 'Entrega' THEN local END) AS `local Entrega`,
    -- 汇总该路由下总收费金额
    SUM(valor_cobrado) AS `sum(valor_cobrado)`
FROM itinerario
GROUP BY route_id;

注意事项 #

• 若使用PostgreSQL且已安装tablefunc扩展，也可调用原生crosstab函数实现转换，但条件聚合写法无额外扩展依赖、跨库可移植性更强，优先推荐使用。
• 若同一route_id下存在多条同tipo的记录，上述写法默认取同组内字段最大值对应的记录属性，如需按id、创建时间等规则取特定记录，可先通过子查询筛选出符合要求的记录再做聚合。
• 若id字段为单条运输记录的主键、而非路由维度的公共字段，可参照tipo、local字段的拆分逻辑，分别提取Coleta、Entrega对应的id值到独立列即可。

内容的提问来源于stack exchange，提问作者Ademir Gomes