mysql - 使用 declare 时出现语法错误

Question

当我使用“declare”时出现语法错误:

 delimiter $$
  begin

 declare @StartDay INT, @EndDay INT, @StartMonth INT, @EndMonth INT
 Select @StartDay = 01, @StartMonth = 10, @EndDay = 30, @EndMonth = 09

 Select year(a.created_at) as years , jd.JobDomain, count(a.Id) as nb_answer,   a.Job_id,   j.JobTitle 
 from JobAppliance a
 inner join Job j on a.Job_id =  j.PublicId
 inner join JobDestination d on j.Id=d.Job_id
 inner join Jjobdomain jd on  j.Id = jd.Job_id

 and 
 (
 @StartMonth = @EndMonth And 
 Month(a.created_at) = @StartMonth And 
 Day(a.created_at) >= @StartDay And 
 Day(a.created_at) <= @EndDay
 ) Or (
 @StartMonth != @EndMonth And (
 (
 Month(a.created_at) = @StartMonth And
 Day(a.created_at) >= @StartDay
 ) Or (
 Month(a.created_at) Between @StartMonth + 1 And @EndMonth - 1
 ) Or (
 Month(a.created_at) = @EndMonth And
 Day(a.created_at) <= @EndDay
 )
 )
)

group by year(a.created_at) , a.Job_id, j.JobTitle;

end$$
delimiter ;

我正在使用 Mysql 工作台

编辑:

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 
'@StartDay int, @EndDay int, @StartMonth int, 
     @EndMonth intSelect @StartDay = 0' at line 3

Answer 1

你错过了行分隔符

DELIMITER $$

DECLARE MyVal INT$$

SELECT
...
$$

DELIMITER ;

另一方面，为什么必须更改分隔符？

并且如文档中所述，Declare 只能在 BEGIN 和 END 之间使用。所以如果你没有在存储过程中使用这部分(例如一个简单的查询)你不能使用 DECLARE

所有以@开头的变量都是 session 变量，不需要声明。

所以这是给你的复制粘贴

SET @StartDay = 01, @StartMonth = 10, @EndDay = 30, @EndMonth = 09; -- look here the ;

Select year(a.created_at) as years , jd.JobDomain, count(a.Id) as nb_answer,   a.Job_id,   j.JobTitle 
from JobAppliance a
inner join Job j on a.Job_id =  j.PublicId
inner join JobDestination d on j.Id=d.Job_id
inner join Jjobdomain jd on  j.Id = jd.Job_id

and 
(
@StartMonth = @EndMonth And 
Month(a.created_at) = @StartMonth And 
Day(a.created_at) >= @StartDay And 
Day(a.created_at) <= @EndDay
) Or (
@StartMonth != @EndMonth And (
(
Month(a.created_at) = @StartMonth And
Day(a.created_at) >= @StartDay
) Or (
Month(a.created_at) Between @StartMonth + 1 And @EndMonth - 1
) Or (
Month(a.created_at) = @EndMonth And
Day(a.created_at) <= @EndDay
)
)
)

group by year(a.created_at) , a.Job_id, j.JobTitle;