MySQL - 无法创建带有 SET 变量的视图
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19035154/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL - Cannot Create View with SET variable inside
提问by John Guan
I am trying to create a view with SET @rank = 0;inside but it's giving me errors. Been trying different things but it's not working. Can anyone please point me to the right direction?
我正在尝试使用SET @rank = 0;内部创建一个视图，但它给了我错误。一直在尝试不同的东西，但它不起作用。任何人都可以指出我正确的方向吗？
CREATE VIEW S1_Bottom_Performer_AHT as (
SET @rank=0
SELECT @rank := @rank+1 AS '#',
ei.SM,
ei.TM,
es.Month_Date,
ei.emp_id,
ei.DNAME,
ei.STATUS,
ei.SHIFT,
ei.SKILL,
ei.HIRE_DATE,
ifnull(TIMESTAMPDIFF(MONTH, ei.HIRE_DATE, now()), '-') AS Tenure,
ifnull(es.Call_Handled, '-') AS Call_Handled,
ifnull(es.AHT, '-') AS AHT
FROM mtl_extended_info ei
LEFT OUTER JOIN
( SELECT es.Employee_ID,
es.Month_Date,
sum(es.Calls_Handled_Ct) AS Call_Handled,
round((sum(es.I_Talk_Time_Sec) + sum(es.Hold_Time_Sec) + sum(es.I_Work_Time_Sec) + sum(es.I_AUX_Out_Time_Sec)) / sum(es.Calls_Handled_Ct)) AS AHT
FROM cdl_agent_call_voume_gen es
WHERE es.Month_Date = '2013-09-01'
GROUP BY es.Employee_ID,
es.Month_Date ) es ON es.Employee_ID = ei.emp_id
WHERE es.Month_Date = '2013-09-01'
AND ei.Visible = 1
AND ei.SKILL != 'RSD'
GROUP BY ei.emp_id
ORDER BY es.AHT DESC LIMIT 80);
Error message:
错误信息：
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SET @rank=0
SELECT @rank := @rank+1 AS '#',
ei.SM,
' at line 2
回答by Rahul Tripathi
I think you cannot do that.
我认为你不能那样做。
As from the MYSQL guidelines:
根据 MYSQL指南：
A view definition is subject to the following restrictions:
[ deletia ]
The SELECTstatement cannot refer to system or user variables.
视图定义受以下限制：
[删除]
该SELECT语句不能引用系统或用户变量。
回答by Vulcronos
Views are select statements, nothing more. Views can not be multiple statements. If you can't get this view down to a single statement, try the suggestion hereto use a function or procedure.
视图是选择语句，仅此而已。视图不能是多个语句。如果您无法将此视图归结为单个语句，请尝试此处的建议以使用函数或过程。
Try thissuggestion for MYSQL using a join instead of a set.
尝试这种使用连接，而不是一组建议为MySQL。
JOIN (SELECT @rank:= 0) r;
Here is a untested example, I am not sure if you can order by '#' at the end, but if you can it would sort everything correctly by rank:
这是一个未经测试的示例，我不确定您是否可以在最后按“#”排序，但如果可以，它会按排名正确排序所有内容：
CREATE VIEW S1_Bottom_Performer_AHT as (
SELECT @rank := @rank+1 AS '#', *
FROM
(SELECT ei.SM,
ei.TM,
es.Month_Date,
ei.emp_id,
ei.DNAME,
ei.STATUS,
ei.SHIFT,
ei.SKILL,
ei.HIRE_DATE,
ifnull(TIMESTAMPDIFF(MONTH, ei.HIRE_DATE, now()), '-') AS Tenure,
ifnull(es.Call_Handled, '-') AS Call_Handled,
ifnull(es.AHT, '-') AS AHT
FROM mtl_extended_info ei
LEFT OUTER JOIN
( SELECT es.Employee_ID,
es.Month_Date,
sum(es.Calls_Handled_Ct) AS Call_Handled,
round((sum(es.I_Talk_Time_Sec) + sum(es.Hold_Time_Sec) + sum(es.I_Work_Time_Sec) + sum(es.I_AUX_Out_Time_Sec)) / sum(es.Calls_Handled_Ct)) AS AHT
FROM cdl_agent_call_voume_gen es
WHERE es.Month_Date = '2013-09-01'
GROUP BY es.Employee_ID,
es.Month_Date ) es ON es.Employee_ID = ei.emp_id
WHERE es.Month_Date = '2013-09-01'
AND ei.Visible = 1
AND ei.SKILL != 'RSD'
GROUP BY ei.emp_id
ORDER BY es.AHT DESC LIMIT 80)
) AS RESULTS
JOIN (SELECT @rank:= 0) r;
ORDER BY '#'
