//将几张不同表结构的数据全部合并在一起，使用了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_inTax,
(case when shd.real_rece_count is not null then shd.real_rece_count else 0 end) as temp_inCount,0 as temp_outCount
FROM t_bi_receivedetail AS shd LEFT JOIN t_bi_receive AS sh ON sh.receipt_id = shd.receipt_num
LEFT JOIN t_bi_commbaseinfo on shd.commodity_id=t_bi_commbaseinfo.commodity_id
WHERE 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_num
LEFT JOIN t_bi_commbaseinfo on thd.commodity_id=t_bi_commbaseinfo.commodity_id
WHERE 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_outCount
FROM t_bi_entaindetail AS lyd LEFT JOIN t_bi_entainment AS ly ON lyd.entertain_recei_num = ly.entertain_recei_num
LEFT JOIN t_bi_commbaseinfo on lyd.commodity_id=t_bi_commbaseinfo.commodity_id
WHERE 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_outCount
FROM t_bi_breakdetail AS bsd LEFT JOIN t_bi_break AS bs ON bsd.break_rece_num = bs.break_rece_num
LEFT JOIN t_bi_commbaseinfo on bsd.commodity_id=t_bi_commbaseinfo.commodity_id
WHERE 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_outCount
FROM t_bi_returnwaredetail AS fcd LEFT JOIN t_bi_returnwarehose AS fc ON fcd.back_reciept_num = fc.back_reciept_num
LEFT JOIN t_bi_commbaseinfo on fcd.commodity_id=t_bi_commbaseinfo.commodity_id
WHERE 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_outCount
FROM t_bi_deliverdetail AS zdd LEFT JOIN t_bi_deliver AS zd ON zdd.deliever_rece_num = zd.deliever_rece_num
LEFT JOIN t_bi_commbaseinfo on zdd.commodity_id=t_bi_commbaseinfo.commodity_id
WHERE 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_outCount
FROM t_bi_hqgroupdetail AS tgd LEFT JOIN t_bi_hqgroup AS tg ON tgd.group_reciept = tg.group_reciept
LEFT JOIN t_bi_commbaseinfo on tgd.commodity_id=t_bi_commbaseinfo.commodity_id
WHERE 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_outCount
FROM t_bi_returnhqgroupdetail AS ttgd LEFT JOIN t_bi_returnhqgroup AS ttg ON ttg.group_return_reciept = ttgd.group_return_reciept
LEFT JOIN t_bi_commbaseinfo on ttgd.commodity_id=t_bi_commbaseinfo.commodity_id
WHERE ttg.check_date BETWEEN '2015/6/1' AND '2016/12/10' AND ttg.status IN ('2', '3', '4'))
) as t_all
LEFT JOIN t_bi_commbaseinfo on t_bi_commbaseinfo.commodity_id=t_all.temp_comm
LEFT JOIN t_bi_commtype on t_bi_commtype.comm_type_id=t_bi_commbaseinfo.comm_type_id
GROUP 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
版权声明：本文为wenhuaguisu原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。