You need to enable JavaScript to run this app. PostgreSQL合并两个查询实现结果相除输出的方法咨询
优惠活动
大模型
产品
解决方案
定价
更多

PostgreSQL合并两个查询实现结果相除输出的方法咨询

PostgreSQL合并查询实现结果相除展示方案

1. 直接输出A/B格式拼接字符串

直接运行以下查询即可得到你需要的446565/14格式输出:

WITH schedule_count AS (
    SELECT count(*) AS cnt 
    FROM trip_schedule_close ts
    WHERE EXTRACT(MONTH FROM ts.schedule_date) = EXTRACT(MONTH FROM current_date)
      AND EXTRACT(YEAR FROM ts.schedule_date) = EXTRACT(YEAR FROM current_date)  
      AND ts.route_id='152'
),
sales_sum AS (
    SELECT sum(ts.product_qty) AS product_quantity 
    FROM trip_delivery_sales_lines ts
    WHERE EXTRACT(MONTH FROM ts.order_date) = EXTRACT(MONTH FROM current_date)
      AND EXTRACT(YEAR FROM ts.order_date) = EXTRACT(YEAR FROM current_date) 
      AND ts.route_id='152' 
      AND ts.product_id='432'  
      AND ts.type='sales'    
)
SELECT CONCAT(product_quantity, '/', cnt) AS result
FROM schedule_count, sales_sum;

2. 同时输出拼接字符串和数值计算结果

如果需要得到除法计算的数值结果,可以使用以下写法:

WITH schedule_count AS (
    SELECT COALESCE(count(*), 0) AS cnt 
    FROM trip_schedule_close ts
    WHERE EXTRACT(MONTH FROM ts.schedule_date) = EXTRACT(MONTH FROM current_date)
      AND EXTRACT(YEAR FROM ts.schedule_date) = EXTRACT(YEAR FROM current_date)  
      AND ts.route_id='152'
),
sales_sum AS (
    SELECT COALESCE(sum(ts.product_qty), 0) AS product_quantity 
    FROM trip_delivery_sales_lines ts
    WHERE EXTRACT(MONTH FROM ts.order_date) = EXTRACT(MONTH FROM current_date)
      AND EXTRACT(YEAR FROM ts.order_date) = EXTRACT(YEAR FROM current_date) 
      AND ts.route_id='152' 
      AND ts.product_id='432'  
      AND ts.type='sales'    
)
SELECT 
    CONCAT(product_quantity, '/', cnt) AS result_str,
    ROUND(product_quantity::numeric / cnt, 2) AS result_value
FROM schedule_count, sales_sum;

说明:

  • ::numeric类型转换用于避免PostgreSQL整数除法自动截断小数的问题,不需要小数可删除该转换
  • COALESCE函数用于处理查询无匹配结果时返回空值的问题,无匹配时默认返回0
  • ROUND函数第二个参数可按需调整保留的小数位数

内容的提问来源于stack exchange,提问作者Shahal

相关产品推荐
方舟 Agent Plan

超全模态模型 × Harness 升级,最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列、Doubao-Seedream-5.0-pro、Kimi-K3 (部分), 限时 9.9 元起

最近更新时间:2026.10.16 05:15:02