统计 - MYSQL版本问题导致SQL语法错误！unknow column 'SQLSTR'

Question

SQLSTR是别名，在MySQL5.7以上版本能通过，在5.0上提示unknow column ‘SQLSTR’错误，我不知道应该怎么修改（因为客户关系不能通过mysql版本）,请各位大神指点！

BEGIN
    DECLARE sTYPE VARCHAR(20);
    DROP TEMPORARY TABLE IF EXISTS temp_public_data;
    CREATE TEMPORARY TABLE temp_public_data SELECT 
    DATE_FORMAT(FROM_UNIXTIME(n.inputtime),'%Y-%m-%d') as days,
    DATE_FORMAT(FROM_UNIXTIME(n.inputtime),'%Y-%m') as months,
    DATE_FORMAT(FROM_UNIXTIME(n.inputtime),'%Y') as years,
    DATE_FORMAT(FROM_UNIXTIME(n.inputtime),'%u') as weeks,
    count(n.id) as pubnum,n.username from v9_news as n 
    WHERE DATE_FORMAT(FROM_UNIXTIME(n.inputtime),'%Y-%m-%d') BETWEEN pDayBegin AND pDayEnd
    GROUP BY days,n.username;

    IF pType=1 THEN
        SET sType = "years";
    ELSEIF pType=2 THEN
        SET sType = "months";
    ELSEIF pType=3 THEN
        SET sType = "weeks";
    ELSEIF pType=5 THEN
        SET sType = "days";
    END IF;
    

    SET @EE = "";
    SELECT @EE:= CONCAT(@EE,',SUM(IF(username=\'',username,'\',pubnum,0)) AS \'',username,'\'') AS **SQLSTR** FROM (SELECT DISTINCT username FROM temp_public_data) A ORDER BY **LENGTH(SQLSTR)** DESC LIMIT 0,1;

    SET @SQL = CONCAT('SELECT IFNULL(',sType,',\'TOTAL\') AS MONTH');

    SET @SQL = CONCAT(@SQL,@EE);
    SET @SQL = CONCAT(@SQL,',SUM(pubnum) as TOTAl ');
    SET @SQL = CONCAT(@SQL,' FROM temp_public_data');
    SET @SQL = CONCAT(@SQL,' GROUP BY ',sType,' WITH ROLLUP;');
    
    #SELECT @SQL;

    PREPARE stmt FROM @SQL;    
    EXECUTE stmt;




        

    DROP TEMPORARY TABLE IF EXISTS temp_public_data;
END

Answer 1

That’s it, just use SQLSTR and put it into a variable, and then use this variable to order, sweat!