Mysql多表联合查询，行转列

深藏阁楼爱情的钟 2024-03-23 14:57 101阅读 0赞

项目中，所有的字段是通过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  
![在这里插入图片描述][8b59492249154e18949cd17f4ded0b47.png]  
最后使用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;

得到想要的结果  
![在这里插入图片描述][b02e09f09f134d568dda7b1e8510e156.png]

[8b59492249154e18949cd17f4ded0b47.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/23/671745e1dd0d4a33a37d26df4d72d96b.png
[b02e09f09f134d568dda7b1e8510e156.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/23/b2c89a04e259423c93b1c261f4d984a5.png