sqlserver2008查询语句报错 无法解决 equal to 运算中 "Chinese_PRC_BIN" 和 "Chinese_PRC_CI_AS" 之间的排序规则冲突

SELECT top(10) * FROM [attendance].[dbo].[att_AttendanceDetail] a left join [attendance].[dbo].[base_StaffInfo] b on a.staffID collate Chinese_PRC_CI_AS =b.user_no where uploadtime>='2018-02-10 00:00:00' and staffID<>'0' and left(b.dept_no,4) in (select dept_no from [attendance].[dbo].[Coll_deptInfo])

sqlserver2008执行以上sql语句的时候报错“无法解决 equal to 运算中 "Chinese_PRC_BIN" 和 "Chinese_PRC_CI_AS" 之间的排序规则冲突”。

处理办法: 将表间关联字段增加collate Chinese_PRC_CI_AS的转义即可解决。修改后的语句如下：

Select a.att_id,a.staffID,a.readdate,a.ReadCardTime,a.uploadtime,a.deviceID,a.photoID,left(b.dept_no,4) dept_no_4,'0' status,b.dept_no,'2018-02-23 11:00:00' WriteTime FROM [attendance].[dbo].[att_AttendanceDetail] a left join [attendance].[dbo].[base_StaffInfo] b on a.staffID collate Chinese_PRC_CI_AS =b.user_no where a.uploadtime>='2018-02-10 00:00:00' and staffID  <>'0' and left(b.dept_no collate Chinese_PRC_CI_AS,4) in (select dept_no collate Chinese_PRC_CI_AS from [attendance].[dbo].[Coll_deptInfo])