统计 - MYSQL版本问题导致SQL语法错误!unknow column ’SQLSTR’
问题描述
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 THENSET sType = 'years'; ELSEIF pType=2 THENSET sType = 'months'; ELSEIF pType=3 THENSET sType = 'weeks'; ELSEIF pType=5 THENSET 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
问题解答
回答1:可以了,用SQLSTR后面into 一个变量,然后用这个变量来order就可以了,汗!
相关文章:
1. angular.js - Angular1使用bootstrap轮播条carousel不能自动轮播2. android - 新建项目卡在Gradle:Resolvedependencies’:app:_debugCompile’3. atom编译器 如何格式化前端代码文件?4. javascript - 关于Js中 this的一道题5. javascript - 求一套有关于IP地址的JS正则!6. 移动端页面文字垂直居中兼容性问题7. javascript - 关于在angularjs的select中遇到的问题!!8. Android Studio 中程序添加不上Xutils 3.0 jar包9. css3 - 我的css用less写的,eclipse的jsp文件要修改css很麻烦,怎么解决?10. python - 基于scrapy-redis的分布式爬虫运行的时候不能正常运行 遇到的问题如下截图所示