c# - mysql 在 c# 上选择错误，但在 navicat 中的查询生成器上运行

Question

每次在 C# 上运行此查询时，我都会收到此错误。

“您的 SQL 语法有错误；请检查与您的 MariaDB 服务器版本相对应的手册，了解使用 close ':= 0 + 1 as rnk1 from attends_table, (select 0 := 0) r1 order 的正确语法通过 User_ID，第 1 行的 V'”

当我在查询生成器上运行它时，不会出现问题，一切正常。我已经添加了这一行

cmd.Parameters.AddWithValue("@r1", 0);
cmd.Parameters.AddWithValue("@r2", 0); 

有人可以帮我解决这个问题吗？这是选择命令。

select 
t1.User_ID,concat(employee_profile.fname,' ',employee_profile.mname,' ',employee_profile.lname) as fullname,
CASE WHEN t1.Verify_State = '0'  THEN t1.Verify_Date END AS time_in,
CASE WHEN t2.Verify_State = '1' THEN t2.Verify_Date END AS time_out,round(TIMESTAMPDIFF(minute,t1.Verify_Date,t2.Verify_Date)/60,2) as Total_hours_worked,timeshift.time_in,
timeshift.time_ou
from 
(select * , @r1:=@r1+1 as rnk1 from attendance_table , (select @r1:=0) r1  order by User_ID, Verify_Date) as  t1 join 
(select * , @r2:=@r2+1 as rnk2 from attendance_table , (select @r2:=0) r2 order by User_ID, Verify_Date) as t2 
on t1.User_ID=t2.User_ID 
and t1.rnk1+1=t2.rnk2 
and t1.Verify_State=0 
and t2.Verify_State=1 
left Join 
employee_profile ON employee_profile.emp_id_no = t1.User_ID 
left JOIN employee_timeshift ON employee_timeshift.emp_id_no = t1.User_ID 
left JOIN timeshift on timeshift.id = employee_timeshift.timeshift_id
left JOIN timeshift_day on timeshift_day.timeshift_id = timeshift.id
where t1.Work_date BETWEEN  '2018-04-09' AND '2018-04-14' and t1.Work_time <> t2.Work_time and timeshift_day.day_id = if(DATE_FORMAT(t1.Verify_Date,'%H:%i:%s') BETWEEN '02:00:00' and '06:00:00',(DAYOFWEEK(t1.Verify_Date) -1)-1,DAYOFWEEK(t1.Verify_Date)-1)
order by  employee_profile.lname asc, t1.Verify_Date,t2.Verify_Date

Answer 1

与

cmd.Parameters.AddWithValue("@r1", 0);

您将查询中的 @r1 替换为 0，从而创建对文字的赋值。例如

@r1:=@r1+1

变成了

0:=0+1

(将0分配给0)。这不是有效的表达式，因为值只能分配给变量。

@r2 的模拟。

我猜当您直接运行查询时，您不会进行替换，因此它可以在那里工作。

由于您没有发布您真正想要做的事情，因此我无法给您任何进一步的建议。