mysql动态列--统计报表信息对比

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'MAX(IF(tmp.summary = ''',
      tp.summary,
      ''', tmp.price, 0)) AS ''',
      tp.summary, ''''
    )
  ) INTO @sql
FROM  (
select dic1.data_name,cd.data_code,cd.price,
concat('修理厂：',ifnull(vd.vendor_name,'')) as summary
from  contract_detail2 cd 
inner join  contract ct on cd.contract_code = ct.contract_code
inner join  vendor_data vd on ct.vendor_code = vd.vendor_code
left join  tax_data td on ct.tax_code = td.tax_code
left join  workhour_assy_data dic1 on cd.data_code=dic1.data_code and dic1.brand_code=cd.brand_code 
left join  data_dic dic2 on cd.vehicle_type=dic2.data_code 
left join  data_dic dic3 on cd.brand_code=dic3.data_code where cd.data_type= 1) tp;
 
SET @sql = CONCAT('select tmp.data_name,tmp.data_code,', @sql, 
                        ' FROM (
select dic1.data_name,cd.data_code,cd.price,
concat(\'修理厂：\',ifnull(vd.vendor_name,\'\')) as summary
from  contract_detail2 cd 
inner join  contract ct on cd.contract_code = ct.contract_code
inner join  vendor_data vd on ct.vendor_code = vd.vendor_code
left join  tax_data td on ct.tax_code = td.tax_code
left join  workhour_assy_data dic1 on cd.data_code=dic1.data_code and dic1.brand_code=cd.brand_code 
left join  data_dic dic2 on cd.vehicle_type=dic2.data_code 
left join  data_dic dic3 on cd.brand_code=dic3.data_code where cd.data_type= 1 ) tmp group by tmp.data_code limit 0,10 ');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

 

得到的结果：不同修理厂的配件价格表格

转载于:https://www.cnblogs.com/jay763190097/p/10996292.html