mysql - 无法在 MySQL 上创建 View

Question

可以在 MySQL 上创建这个 View 吗？问题在于变量。我找不到解决方法。

CREATE VIEW vw_ranking AS 

SELECT rank.ativid_id, rank.user_id, b.nome, rank.quant

FROM
(SELECT user_id, ativid_id, quant, 

    @ativ_rank := IF(@current_ativ = ativid_id, @ativ_rank + 1, 1) AS ativ_rank,
    @current_ativ := ativid_id 

FROM (SELECT ativid_id, user_id, COUNT(user_id) as quant FROM tb_registro_ativ

    GROUP BY ativid_id, user_id) atividade
    ORDER BY ativid_id, quant DESC

) rank INNER JOIN tb_usuarios b ON rank.user_id = b.user_id

WHERE ativ_rank <= 10;

Answer 1

您不能在创建 View 时定义变量。

来源:https://dev.mysql.com/doc/refman/5.5/en/create-view.html

A view definition is subject to the following restrictions:

The SELECT statement cannot contain a subquery in the FROM clause.

The SELECT statement cannot refer to system variables or user-defined variables.

Within a stored program, the SELECT statement cannot refer to program parameters or local variables.

The SELECT statement cannot refer to prepared statement parameters.

Any table or view referred to in the definition must exist. After the view has been created, it is possible to drop a table or view that the definition refers to. In this case, use of the view results in an error. To check a view definition for problems of this kind, use the CHECK TABLE statement.

我建议您改用存储过程。