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函数用于处理查询无匹配结果时返回空值的问题,无匹配时默认返回0ROUND函数第二个参数可按需调整保留的小数位数
内容的提问来源于stack exchange,提问作者Shahal
相关产品推荐
相关产品推荐

