Mysql 行转列 + json

最新推荐文章于 2024-09-13 23:09:20 发布

麻辣小布叮

最新推荐文章于 2024-09-13 23:09:20 发布

阅读量1.2k

点赞数

分类专栏： MySQL 文章标签： json 行转列

本文链接：https://blog.csdn.net/luobing_csdn/article/details/88341566

版权

MySQL 专栏收录该内容

21 篇文章 0 订阅

订阅专栏


SET @EE = '';
        SET @str_tmp = '';

        SET @Revenue_JSON = '';
        SET @Revenue_JSON_tmp = '';

        SELECT @EE := CONCAT(
			@EE,
			'SUM(IF(fsRevenueTypeName=\'',
			fsRevenueTypeName,
			'\'',
			',fdSubTotal,0)) AS ''revenue_',
			fsRevenueTypeName,
			''','
		       ) AS aa ,
	        @Revenue_JSON := CONCAT (
			@Revenue_JSON,','',"'
			,fsRevenueTypeName,'":'','
			,'revenue_',fsRevenueTypeName
			)
			AS bb
		INTO @str_tmp,@Revenue_JSON_tmp
        FROM
            (
                SELECT DISTINCT
                    fsRevenueTypeName
                FROM
                    TempRevenueType 
                WHERE fsRevenueTypeName IS NOT NULL
            ) A
        ORDER BY
            LENGTH(aa) DESC
        LIMIT 1;

        -- 拼接 json 
        SELECT CONCAT('''{',SUBSTRING(@Revenue_JSON_tmp,4),','',"汇总":'',','revenue_汇总',',''}''') INTO @Revenue_JSON_tmp;

        SET @QQ = CONCAT(
            ' CREATE TEMPORARY TABLE TempRevenueType1 ( SELECT fsSellNo3,CONCAT(',@Revenue_JSON_tmp,') as revenue_info',
            ' FROM ( SELECT fsSellNo as fsSellNo3,',@str_tmp,
            ' SUM(fdSubTotal) AS revenue_汇总 FROM TempRevenueType Group By  fsSellNo) a );'
        );