Mysql 行转列 + json

麻辣小布叮

于 2017-05-11 20:08:01 发布

阅读量1.2k

点赞数

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

本文链接：https://blog.youkuaiyun.com/luobing_csdn/article/details/88341566

版权

MySQL 专栏收录该内容

21 篇文章

订阅专栏

本文介绍了一种使用 SQL 语句动态构建 JSON 数据的方法。通过拼接 SELECT 语句及利用临时表来实现对多类型收入数据的汇总，并最终形成 JSON 结构。此方法适用于需要将数据库中的数据转换为 JSON 格式进行传输或存储的场景。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >


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 );'
        );