postgre——case、union、小计总计（GROUP BY ROLLUP）写法

//将几张不同表结构的数据全部合并在一起，使用了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_divTotalfrom ((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_inTax,(case when shd.real_rece_count is not null then shd.real_rece_count else 0 end) as temp_inCount,0 as temp_outCountFROM t_bi_receivedetail AS shd LEFT JOIN t_bi_receive AS sh ON sh.receipt_id = shd.receipt_numLEFT JOIN t_bi_commbaseinfo on shd.commodity_id=t_bi_commbaseinfo.commodity_idWHERE sh.check_date BETWEEN '2015/7/1' AND '2016/12/10' AND receipt_status = '2')UNION(SELECT thd.return_com_receipt_num AS temp_bill,thd.commodity_id as temp_comm,th.check_date AS temp_date,0 AS temp_inPrice,(CASE WHEN thd.return_com_price IS NOT NULL THEN thd.return_com_price ELSE 0 END) 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,0 AS temp_inTax,0 as temp_inCount,(case when thd.return_com_count is not null then return_com_count else 0 end) as temp_outCount  FROM t_bi_returncommdetail AS thd LEFT JOIN t_bi_returncomm AS th ON thd.return_com_receipt_num = th.return_com_receipt_numLEFT JOIN t_bi_commbaseinfo on thd.commodity_id=t_bi_commbaseinfo.commodity_idWHERE th.check_date BETWEEN '2015/6/1' AND '2016/12/10' AND th.status = '2')UNION(SELECT lyd.entertain_recei_num AS temp_bill,lyd.commodity_id as temp_comm,ly.check_date AS temp_date,0 as temp_inPrice,(case when t_bi_commbaseinfo.retail_price is not null then t_bi_commbaseinfo.retail_price else 0 end) AS temp_retailPrice,0 as temp_outPrice,0 as temp_inTax,0 as temp_inCount,0 as temp_outCountFROM t_bi_entaindetail AS lyd LEFT JOIN t_bi_entainment AS ly ON lyd.entertain_recei_num = ly.entertain_recei_numLEFT JOIN t_bi_commbaseinfo on lyd.commodity_id=t_bi_commbaseinfo.commodity_idWHERE ly.check_date BETWEEN '2015/6/1' AND '2016/12/10' AND ly.status IN ('2', '3'))UNION(SELECT bsd.break_rece_num AS temp_bill,bsd.commodity_id as temp_comm,bs.check_date AS temp_date,0 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,0 as temp_inTax,0 as temp_inCount,0 as temp_outCountFROM t_bi_breakdetail AS bsd LEFT JOIN t_bi_break AS bs ON bsd.break_rece_num = bs.break_rece_numLEFT JOIN t_bi_commbaseinfo on bsd.commodity_id=t_bi_commbaseinfo.commodity_idWHERE bs.check_date BETWEEN '2015/6/1' AND '2016/12/10' AND bs.status = '2')UNION(SELECT fcd.back_reciept_num AS temp_bill,fcd.commodity_id as temp_comm,fc.check_date AS temp_date,0 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,0 as temp_inTax,0 as temp_inCount,0 as temp_outCountFROM t_bi_returnwaredetail AS fcd LEFT JOIN t_bi_returnwarehose AS fc ON fcd.back_reciept_num = fc.back_reciept_numLEFT JOIN t_bi_commbaseinfo on fcd.commodity_id=t_bi_commbaseinfo.commodity_idWHERE fc.check_date BETWEEN '2015/6/1' AND '2016/12/10' AND fc.status IN ('2', '3', '4'))UNION(SELECT zdd.deliever_rece_num AS temp_bill,zdd.commodity_id as temp_comm,zd.check_date AS temp_date,0 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,0 as temp_inTax,0 as temp_inCount,0 as temp_outCountFROM t_bi_deliverdetail AS zdd LEFT JOIN t_bi_deliver AS zd ON zdd.deliever_rece_num = zd.deliever_rece_numLEFT JOIN t_bi_commbaseinfo on zdd.commodity_id=t_bi_commbaseinfo.commodity_idWHERE zd.check_date BETWEEN '2015/6/1' AND '2016/12/10' AND zd.status IN ('5', '3', '4'))UNION(SELECT tgd.group_reciept AS temp_bill,tgd.commodity_id as temp_comm,tg.check_date AS temp_date,0 as temp_inPrice,(case when tgd.groupbuy_price is not null then tgd.groupbuy_price else 0 end) 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,0 as temp_inTax,0 as temp_inCount,(case when tgd.groupbuy_count is not null then tgd.groupbuy_count else 0 end) as temp_outCountFROM t_bi_hqgroupdetail AS tgd LEFT JOIN t_bi_hqgroup AS tg ON tgd.group_reciept = tg.group_recieptLEFT JOIN t_bi_commbaseinfo on tgd.commodity_id=t_bi_commbaseinfo.commodity_idWHERE tg.check_date BETWEEN '2015/6/1' AND '2016/12/10' AND tg.status IN ('2', '3', '4'))UNION(SELECT ttgd.group_return_reciept AS temp_bill,ttgd.commodity_id as temp_comm,ttg.check_date AS temp_date,(case when ttgd.groupbuy_price is not null then ttgd.groupbuy_price else 0 end) as temp_inPrice,(case when t_bi_commbaseinfo.retail_price is not null then t_bi_commbaseinfo.retail_price else 0 end) AS temp_retailPrice,0 as temp_outPrice,0 as temp_inTax,(case when ttgd.groupbuy_count is not null then ttgd.groupbuy_count else 0 end) as temp_inCount,0 as temp_outCountFROM t_bi_returnhqgroupdetail AS ttgd LEFT JOIN t_bi_returnhqgroup AS ttg ON ttg.group_return_reciept = ttgd.group_return_recieptLEFT JOIN t_bi_commbaseinfo on ttgd.commodity_id=t_bi_commbaseinfo.commodity_idWHERE ttg.check_date BETWEEN '2015/6/1' AND '2016/12/10' AND ttg.status IN ('2', '3', '4')) ) as t_allLEFT JOIN t_bi_commbaseinfo on t_bi_commbaseinfo.commodity_id=t_all.temp_commLEFT JOIN t_bi_commtype on t_bi_commtype.comm_type_id=t_bi_commbaseinfo.comm_type_idGROUP BY ROLLUP((t_all.temp_comm,commodity_name,guige,t_bi_commbaseinfo.comm_type_id,comm_type_name,t_all.temp_bill,t_all.temp_date, temp_inPrice,temp_outPrice))order by temp_comm