mysql json 行转列_Mysql 行转列 + json

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

);

分享到：

2017-05-11 20:08

浏览 2597

分类:数据库

评论