Mysql多表联合查询，行转列-蒲公英云

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 (
    SELECT
        crfSubject.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 
    FROM
        edc_crf_subject AS crfSubject
        LEFT JOIN edc_crf_subject_data AS subData ON crfSubject.id = subData.subject_id
        LEFT JOIN edc_crf_subject_data_details AS subDetails ON subData.id = subDetails.subject_data_id 
    WHERE
        subData.crf_name = 'SUBJECT' 
        AND subDetails.is_deleted = '0' 
    ) AS info 
GROUP BY
    id;

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 (
    SELECT
        crfSubject.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 
    FROM
        edc_crf_subject AS crfSubject
        LEFT JOIN edc_crf_subject_data AS subData ON crfSubject.id = subData.subject_id
        LEFT JOIN edc_crf_subject_data_details AS subDetails ON subData.id = subDetails.subject_data_id 
    WHERE
        subData.crf_name = 'SUBJECT' 
        AND subDetails.is_deleted = '0') as base_info group by id;