mysql - 是否可以在 MySQL View 中使用变量或参数？

Question

我正在尝试创建如下 View :

CREATE VIEW v_MyView
AS

SET @par_count := 0;  -- XXX Working in SELECT, but not in a View !? XXX

SELECT
    q1.day,
    q1.count_per_day,
    (@par_count := @par_count  + q1.count_per_day) AS count_sum  -- sums up count_per_day 
FROM
    (SELECT
        DATE(registration_date_time_obj) AS day,
        COUNT(Date(registration_date_time_obj)) AS count_per_day
     FROM  tbl_registration_data
     GROUP BY day
     ORDER BY day
    ) AS q1
;

select 语句本身工作正常，只是在 MySQL 中创建 View 失败，因为我猜它不接受声明中的用户变量/参数。

有没有办法通过参数的变通方法仍然创建此 View ？

无论如何，我能够为 select 语句创建一个类似的过程，但这并不能真正解决问题，因为我不能在另一个 select 语句中调用该过程...

感谢您的建议和解决方案! (:

Answer 1

MySQL documentation很明显变量是不允许的:

• 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.

您可以使用相关子查询做您想做的事:

 SELECT DATE(registration_date_time_obj) AS day,
        COUNT(Date(registration_date_time_obj)) AS count_per_day,
        (SELECT COUNT(*)
         FROM tbl_registration_data rd2
         WHERE rd2.registration_date_time_obj <= date_add(date(rd.registration_date_time_obj), interval 1 day)
 FROM  tbl_registration_data rd
 GROUP BY day
 ORDER BY day;