在mysql视图中,想要创建视图的同时,在sql查询语句中将原数据按照某一字段排序。
以我所遇到的情况为例,我想要查询视图中的数据,并将其按照市值(total_mv)降序排序。
以下是sql语句:
select @rownum:= @rownum+1 AS 市值排名,name,ts_code,total_mv
from stock_all,(SELECT @rownum:=0) b
order by total_mv DESC
如过只是查询,那么这样没什么问题,但如果是在创建视图的同时带这个查询,那么在mysql中就会报以下错误:
create or replace view stock_all1
as
select @rownum:= @rownum+1 AS 市值排名,name,ts_code,total_mv
from stock_all,(SELECT @rownum:=0) b
order by total_mv DESC
这是因为在mysql中,不允许创建视图的语句里有变量(带@)
我们可以通过自定义函数的方式,让返回值每次自加1,来取代变量
以数据库工具navicat为例,点击函数--》新建函数--》输入函数名,点击完成
然后输入以下代码,改代码有两个功能置零和自增1,这样写的原因可以参考这个博主的文章:mysql 视图 行号_MySQL踩坑记之视图添加行号_怡一的博客-CSDN博客
CREATE DEFINER=py_stock@% FUNCTION rank_total_mv(RESET BIT) RETURNS int
NO SQL
BEGIN
IF RESET THEN
SET @rownum:= 0;
ELSE
SET @rownum:= IFNULL(@rownum,0)+1;
END IF;
RETURN @rownum;
END
点击保存会发现,提示以下错误:
原因是自定义函数需要相应权限,我们可以进入mysql的root用户下输入以下指令打开创建函数开关:
SET GLOBAL log_bin_trust_function_creators = 1;
如果是线上数据库,可以在下图所示地方开启:
在sql框中输入上面的指令,点至执行即可。
此时再回到navicat可以发现,函数已经可以创建:
然后我们将创建视图语句中的变量换为自定义的函数即可:
create or replace view stock_all1
as
select rank_total_mv(0) AS 市值排名,name,ts_code,total_mv
from stock_all,(SELECT rank_total_mv(1)) b
order by total_mv DESC
下面为视图结果显示: