你遇到的核心问题是混淆了MariaDB的会话变量和ADO.NET的参数占位符：

• 在Navicat里，@r1、@r2是MariaDB的本地会话变量，用来实现行号递增，语法完全合法；
• 但在C#的ADO.NET中，你把@r1、@r2当成了需要传入的SQL参数，通过AddWithValue赋值后，ADO.NET会自动把SQL里的@r1替换成你传入的0，原本的@r1:=@r1+1就变成了0 := 0 +1，这直接触发了SQL语法错误。

解决步骤 #

1. 移除错误的参数添加代码 #

直接删掉这两行代码，因为@r1和@r2是SQL内部使用的会话变量，根本不需要从C#传入：

// 删掉这两行无效代码
// cmd.Parameters.AddWithValue("@r1", 0); 
// cmd.Parameters.AddWithValue("@r2", 0); 

2. 修正SQL语句中的笔误 #

你的查询末尾有个timeshift.time_ou，明显是timeshift.time_out的笔误——虽然这不是当前报错的原因，但会导致后续查询结果异常，建议一并修正。

3. 修正后的完整SQL语句 #

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_out  -- 修正了笔误
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

额外优化建议 #

如果你的MariaDB版本是10.2及以上，推荐使用窗口函数ROW_NUMBER()替代会话变量实现行号，语法更简洁且不易出错，示例如下：

-- 用ROW_NUMBER()替换会话变量的写法
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_out
from 
    (select *, ROW_NUMBER() OVER (PARTITION BY User_ID ORDER BY Verify_Date) as rnk1 from attendance_table) as t1 
join 
    (select *, ROW_NUMBER() OVER (PARTITION BY User_ID ORDER BY Verify_Date) as rnk2 from attendance_table) 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 
-- 后续的JOIN和WHERE子句保持不变
...

内容的提问来源于stack exchange，提问作者Cyrus