Mysql多表联合查询，行转列

项目中，所有的字段是通过key和value的形式存放的，以至于导致查询某一个人的数据，需要进行判断，
一开始在网上找了很多，大部分都是 一样demo,
第一次尝试，结果不是我想要的

select id,project_id,
sum(if(`variable`='SITEID',variable_value,null)) as core_code,
sum(if(`variable`='CRFVER',variable_value,null)) as crf_version,create_time,update_time,create_by,update_by,is_deleted
from (SELECTcrfSubject.id,crfSubject.project_id,crfSubject.centre_id,subDetails.variable,subDetails.variable_name,subDetails.variable_value,subData.create_time,subData.update_time,subData.create_by,subData.update_by,crfSubject.is_deleted FROMedc_crf_subject AS crfSubjectLEFT JOIN edc_crf_subject_data AS subData ON crfSubject.id = subData.subject_idLEFT JOIN edc_crf_subject_data_details AS subDetails ON subData.id = subDetails.subject_data_id WHEREsubData.crf_name = 'SUBJECT' AND subDetails.is_deleted = '0' ) AS info 
GROUP BYid;

core_code是字符，但却搞成 了数字 ，问题就出在sum ,所以 如果不是计算类型的，最好不要用su

最后使用max

select id,max(	if(variable='SUBJID',variable_value,null)) as sub_code,max(	if(variable='SITEID',variable_value,null)) as core_code,
max(	if(variable='SITENAME',variable_value,null)) as core_name,
max(	if(variable='STATUS',variable_value,1)) as sub_status,
max(	if(variable='CRFVER',variable_value,null)) as crf_version,
max(	if(variable='CRFSTS',variable_value,1)) as crf_status,
max(	if(variable='COUNTRY',variable_value,null)) as county,
max(	if(variable='INVNAM',variable_value,null)) as invnam,
max(	if(variable='STUDYID',variable_value,null)) as study_id,
create_time,create_by,update_time,update_by,is_deleted
from (SELECTcrfSubject.id,crfSubject.project_id,crfSubject.centre_id,subDetails.variable,subDetails.variable_name,subDetails.variable_value,subData.create_time,subData.update_time,subData.create_by,subData.update_by,crfSubject.is_deleted FROMedc_crf_subject AS crfSubjectLEFT JOIN edc_crf_subject_data AS subData ON crfSubject.id = subData.subject_idLEFT JOIN edc_crf_subject_data_details AS subDetails ON subData.id = subDetails.subject_data_id WHEREsubData.crf_name = 'SUBJECT' AND subDetails.is_deleted = '0') as base_info group by id;

得到想要的结果

背景图网