Error querying database. Cause: java.sql.SQLSyntaxErrorException: Unknown column ‘u.user_id‘ in ‘whe

项目场景：

记录一下，如下报错

### Error querying database. Cause: java.sql.SQLSyntaxErrorException: Unknown column 'u.user_id' in 'where clause' ### The error may exist in file [D:\javaProject\exam\RuoYi-Vue-master\ruoyi-exam\target\classes\mapper\exam\MessageUserMapper.xml] ### The error may involve com.ruoyi.exam.mapper.MessageUserMapper.selectMessageUserList-Inline ### The error occurred while setting parameters ### SQL: SELECT count(0) FROM t_message_user tmu LEFT JOIN sys_user su ON tmu.receive_user_id = su.user_id LEFT JOIN t_message tm ON tmu.message_id = tm.message_id WHERE (u.user_id = 101) ### Cause: java.sql.SQLSyntaxErrorException: Unknown column 'u.user_id' in 'where clause' ; bad SQL grammar []; nested exception is java.sql.SQLSyntaxErrorException: Unknown column 'u.user_id' in 'where clause'

---

原因分析：

提示：这里填写问题的分析：

在确保sql语法正确的情况下

    <select id="selectMessageUserList" parameterType="MessageUser" resultMap="MessageUserResult">        select tmu.message_user_id, tmu.message_id, tmu.receive_user_id, tmu.receive_user_name,                tmu.receive_nike_name, tmu.readed, tmu.read_time, tmu.create_by, tmu.create_time,                tmu.update_by, tmu.update_time, tmu.remark, tm.title,tm.content,su.user_name, su.nick_name        from t_message_user tmu        left join sys_user su on tmu.receive_user_id=su.user_id        left join t_message tm on tmu.message_id = tm.message_id        <where>            <if test="readed != null "> and readed = #{readed}if>            <if test="readTime != null "> and read_time >= #{readTime}if>                        ${params.dataScope}        where>    select>

其中left join sys_user su on tmu.receive_user_id=su.user_id

这里的 sys_user su 这个别名su
要与userAlias = "su"的su相对应，名不能乱起

    @Override    @DataScope(deptAlias = "d", userAlias = "su")    public List<MessageUser> selectMessageUserList(MessageUser messageUser)    {        return messageUserMapper.selectMessageUserList(messageUser);    }

其中datascrop.java

@Target(ElementType.METHOD)@Retention(RetentionPolicy.RUNTIME)@Documentedpublic @interface DataScope{        public String deptAlias() default "";        public String userAlias() default "";        public String permission() default "";}

来源地址：https://blog.csdn.net/weixin_43798721/article/details/128455642