使用WITH ROLLUP解决报表中增加小计，总计

之前公司做报表要在后面加小计和总计，都是用代码解决的，没想到mysql有自带的，真的太菜逼了，有需要的快来看看

 

 

SELECT
CASE WHEN (CONCAT(t2.stock_name_) = 1) THEN 'ALL'
ELSE IFNULL(t2.stock_name_, 'UNKNOWN')
END AS st_name,
CASE WHEN (CONCAT(t2.goods_name_) = 1) THEN 'ALL'
ELSE IFNULL(t2.goods_name_, 'UNKNOWN')
END AS cl_clsna_,
t2.stock_name_,
t2.goods_clsna_,
t2.goods_name_,
t2.goods_no_,
t2.goods_unit_cost_,
IFNULL(SUM(t3.qty),0.00) AS current_inventory_quantity_,
IFNULL(SUM(t3.cost),0.00) AS current_purchase_cost_,
IFNULL(SUM(t2.qty),0.00) AS current_warehousing_quantity_,
IFNULL(SUM(t2.cost),0.00) AS current_warehousing_amt_,
IFNULL(SUM(t1.qty),0.00) AS current_period_,
IFNULL(SUM(t1.cost),0.00) AS current_amt_
FROM
real_time_view t3
LEFT JOIN collect_view t1 ON t1.stock_no_ = t3.stock_no_ AND t1.goods_clsna_ = t3.goods_clsna_ AND t1.`goods_no_`=t3.`goods_no_`
RIGHT JOIN warehousing_view t2 ON t2.stock_no_ = t3.stock_no_ AND t2.goods_clsna_ = t3.goods_clsna_ AND t2.`goods_no_`=t3.`goods_no_`
WHERE t2.date_time >= '2022-02-01' AND t2.date_time <= '2022-07-14'
GROUP BY st_name,cl_clsna_ WITH ROLLUP;

 

 

就是在GROUP BY后面加WITH ROLLUP