mysql动态字段行转列

动态行转列

table_schema
id          name   
s_001     是否吃饭了
s_002     你的汽车品牌
	
	
table_schema_value
id   user_id  schema_id schema_value
001   u_01      s_001     是
002   u_01      s_002     兰博基尼
003   u_001     s_001     否

转换后
user_id  s_001    s_002
001       是     兰博基尼
002       否

如下创建临时表

WITH temp_schema AS (
	SELECT
		ifnull( user_id, '' ) AS user_id,
		max(
		IF
		( schema_id = '001', schema_value, '' )) AS `001`,
		max(
		IF
		( schema_id = '002', schema_value, '' )) AS `002`,
	
	FROM
		table_schema_value
	WHERE
	GROUP BY
		user_id 
)

mybatis XML方式

 <!--  行转列-->
            WITH temp_schema  AS (
            SELECT
            ifnull(user_id, '' ) AS user_id,
            <foreach collection="schemaIdList" index="index" item="item">
                max(IF( schema_id = #{item}, schema_value, '' )) AS `${item}`
                <if test="schemaIdList !=null and index != (schemaIdList.size()-1)">
                    ,
                </if>
            </foreach>
            FROM
            table_schema_value
            GROUP BY user_id