1:合并字符串
select CONCAT(car.brand_code,'(',b.name,')') as '品牌',
CONCAT(car.series_code,'(',s.name,')') as '车系',
CONCAT(car.model_code,'(',m.auto_type,')') as '车型',
CONCAT( brand.`name`,' ',series.`name`,' ', model.`auto_type`) 'chexing',
if(voince.company_name != '' ,voince.company_name,attr.invoice_title) 'company'
from t_mst_car_brand as b on car.brand_code= b.`code`
left join t_mst_car_series as s on car.series_code= s.`code`
left join t_mst_car_model as m on car.model_code =m.code
2:行数据按照类型变为列显示(比如,车辆的保险按照保险类型以行的形式保存数据,需要按照车辆ID的维度显示保险数据)
SELECT authen_id ,
MAX(CASE image_type WHEN '400' THEN '已认证' ELSE '未认证' END ) 商业险发票认证,
MAX(CASE image_type WHEN '410' THEN '已认证' ELSE '未认证' END) 交强险发票认证,
MAX(CASE image_type WHEN '420' THEN '已认证' ELSE '未认证' END ) 购车发票认证
FROM t_car_invoice_authen_img GROUP BY authen_id
select CONCAT(car.brand_code,'(',b.name,')') as '品牌',
CONCAT(car.series_code,'(',s.name,')') as '车系',
CONCAT(car.model_code,'(',m.auto_type,')') as '车型',
CONCAT( brand.`name`,' ',series.`name`,' ', model.`auto_type`) 'chexing',
if(voince.company_name != '' ,voince.company_name,attr.invoice_title) 'company'
from t_mst_car_brand as b on car.brand_code= b.`code`
left join t_mst_car_series as s on car.series_code= s.`code`
left join t_mst_car_model as m on car.model_code =m.code
2:行数据按照类型变为列显示(比如,车辆的保险按照保险类型以行的形式保存数据,需要按照车辆ID的维度显示保险数据)
SELECT authen_id ,
MAX(CASE image_type WHEN '400' THEN '已认证' ELSE '未认证' END ) 商业险发票认证,
MAX(CASE image_type WHEN '410' THEN '已认证' ELSE '未认证' END) 交强险发票认证,
MAX(CASE image_type WHEN '420' THEN '已认证' ELSE '未认证' END ) 购车发票认证
FROM t_car_invoice_authen_img GROUP BY authen_id