select a.bu as 经营部, a.pdate as 时间, a.item as 项目, a.rec as 人员 , SUM(CASE b.product_name WHEN '产品1' THEN b.num END) [产品1] , SUM(CASE b.product_name WHEN '产品2' THEN b.num END) [产品2] , SUM(CASE b.product_name WHEN '产品3' THEN b.num END) [产品3] from used_info a left join use_dtl b on a.used_id=b.used_id group by a.bu ,a.pdate ,a.item ,a.rec union all select '合計', '', '', '', SUM(CASE b.product_name WHEN '产品1' THEN b.num END) [产品1] , SUM(CASE b.product_name WHEN '产品2' THEN b.num END) [产品2] , SUM(CASE b.product_name WHEN '产品3' THEN b.num END) [产品3] from use_dtl
a.bu as 经营部, a.pdate as 时间, a.item as 项目, a.rec as 人员 ,
SUM(CASE b.product_name WHEN '产品1' THEN b.num END) [产品1] ,
SUM(CASE b.product_name WHEN '产品2' THEN b.num END) [产品2] ,
SUM(CASE b.product_name WHEN '产品3' THEN b.num END) [产品3]
from used_info a
left join use_dtl b on a.used_id=b.used_id
group by a.bu ,a.pdate ,a.item ,a.rec
union all
select
'合計', '', '', '',
SUM(CASE b.product_name WHEN '产品1' THEN b.num END) [产品1] ,
SUM(CASE b.product_name WHEN '产品2' THEN b.num END) [产品2] ,
SUM(CASE b.product_name WHEN '产品3' THEN b.num END) [产品3]
from use_dtl