连接查询 Error:Column 'XXX' in order clause is ambiguous

错误为:

Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Column 'XXX' in order clause is ambiguous↵

### The error may exist in class path resource [mapper/ClassesSignUpMapper.xml]↵### The error may involve defaultParameterMap↵###

The error occurred while setting parameters

↵### SQL: SELECT * FROM XXX as s1 right join (SELECT * FROM SSS where 1=1 AND name like CONCAT("%",?,"%") )s2 on s1.student_id = s2.id where cls_id = ? order by status asc↵###

Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Column 'XXX' in order clause is ambiguous↵; ]; Column 'XXX' in order clause is ambiguous; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Column 'status' in order clause is ambiguous"

 

原因为连接查询的时候,误把两个表中的都有字段当成单张表的单有字段,没有在字段前加上想要作为排序条件的表名,固报错.

解决:在两张表的都有字段前加上想要作为排序条件的表名(设置为别名后加别名即可)为区分.

 

建议:链表查询的时候,可以把两个表中的字段 as 为 别名 看着更清晰