本文介绍了如何使用PostgreSQL的CASE语句、UNION操作符以及GROUP BY ROLLUP函数来合并不同表结构的数据并实现小计和总计。通过示例展示了涉及多个字段的复杂查询,包括收据、退货、娱乐等业务场景的数据整合和统计分析。
摘要由CSDN通过智能技术生成
//将几张不同表结构的数据全部合并在一起,使用了case、union,同时实现小计总计
//小计总计使用的 GROUP BY ROLLUP函数,GROUP BY ROLLUP使用双括号将字段括起来,是将这些字段分为一组,只会产生一个总计
select case when t_all.temp_comm is null then '总计' else t_all.temp_comm end as temp_comm,
commodity_name,guige,t_bi_commbaseinfo.comm_type_id,comm_type_name,t_all.temp_bill,t_all.temp_date,
sum(temp_inCount) as temp_inCount,temp_inPrice as temp_inPrice,
sum(temp_inCount*temp_inPrice) as temp_inTotal,
sum(temp_inCount*temp_inPrice*(1-temp_inTax)) as temp_inTatolTax,
sum(temp_inCount*temp_inTax*temp_inPrice) as temp_inTaxTatal,
sum(temp_outCount) as temp_outCount,temp_outPrice as temp_outPrice,
sum(temp_outCount*temp_outPrice) as temp_outTotal,
sum(temp_retailPrice*temp_inCount) as temp_inSaleTotal,
sum((temp_retailPrice-temp_inPrice)*temp_inCount) as temp_outDivInTotal,
sum(temp_inCount-temp_outCount) as temp_outDivIn,
sum(temp_inCount*temp_inPrice) as temp_divTotal
from ((SELECT receipt_num AS temp_bill,shd.commodity_id as temp_comm,sh.check_date AS temp_date,
(CASE WHEN shd.order_real_inprice IS NOT NULL THEN shd.order_real_inprice ELSE 0 END) AS temp_inPrice,0 as temp_outPrice,
(case when t_bi_commbaseinfo.retail_price is not null then t_bi_commbaseinfo.retail_price else 0 end) AS temp_retailPrice,
(CASE WHEN shd.in_tax IS NOT NULL THEN shd.in_tax ELSE 0 END) AS temp_