问题描述(oracle):

sql报错之后打印到控制台,发现在已经有了ORDER BY的情况下,还自动多生成一个
ORDER BY CREATETIME DESC

同时日志里的报错提示是这样子的(完全看不出问题所在):

Caused by: java.sql.SQLSyntaxErrorException: ORA-00907: 缺失右括号

问题原因:

当sql语句中存在组合函数,比如count(*)时,mybatisplus会
不识别sql语句最后的order by

解决办法:

将原有sql语句,除了ORDER BY的语句都用小括号包括起来嵌套一层,
具体情况参考下面例子:

原sql

SELECT
		tl.id,
		tl.name,
		tl.CREATE_TIME,
		tk.name knowledgeId,
		tl.TYPE,
		td.name doc_typeName,
		tl.defi,
		tl.upload_status uploadStatus,
		tl.use_status useStatus,
		tl.create_time createTime,
		tl.qingxiurl,
		tl.gaoqing,
		tl.chaoqing,
		tl.error_info errorInfo,
		org.name org_name,
		tl.doc_type_detail docTypeDetail,
		tl.is_uploadword isUploadword,
		count( tl.id ) haveRelated 
	FROM
		t_lesson tl
		LEFT JOIN t_knowledge tk ON tk.id = tl.knowledge_id
		LEFT JOIN t_dictionary td ON td.id = tl.doc_type
		LEFT JOIN XL_ORG org ON org.id = tl.org
		LEFT JOIN t_learn_lesson tll ON tl.id = tll.lesson_id 
	WHERE
		1 = 1 
	GROUP BY
		tl.id,
		tl.name,
		tk.name,
		tl.TYPE,
		td.name,
		tl.defi,
		tl.upload_status,
		tl.use_status,
		tl.create_time,
		tl.qingxiurl,
		tl.gaoqing,
		tl.chaoqing,
		tl.error_info,
		org.name,
		tl.doc_type_detail,
		tl.is_uploadword 
ORDER BY
	tl.create_time DESC,
	tl.id ASC

修改后:

SELECT
	a.id,
	a.name,
	a.knowledgeId,
	a.TYPE,
	a.defi,
	a.uploadStatus,
	a.useStatus,
	a.createTime,
	a.qingxiurl,
	a.gaoqing,
	a.chaoqing,
	a.errorInfo,
	a.docTypeDetail,
	a.isUploadword,
	a.haveRelated 
FROM
	(
	SELECT
		tl.id,
		tl.name,
		tl.CREATE_TIME,
		tk.name knowledgeId,
		tl.TYPE,
		td.name doc_typeName,
		tl.defi,
		tl.upload_status uploadStatus,
		tl.use_status useStatus,
		tl.create_time createTime,
		tl.qingxiurl,
		tl.gaoqing,
		tl.chaoqing,
		tl.error_info errorInfo,
		org.name org_name,
		tl.doc_type_detail docTypeDetail,
		tl.is_uploadword isUploadword,
		count( tl.id ) haveRelated 
	FROM
		t_lesson tl
		LEFT JOIN t_knowledge tk ON tk.id = tl.knowledge_id
		LEFT JOIN t_dictionary td ON td.id = tl.doc_type
		LEFT JOIN XL_ORG org ON org.id = tl.org
		LEFT JOIN t_learn_lesson tll ON tl.id = tll.lesson_id 
	WHERE
		1 = 1 
	GROUP BY
		tl.id,
		tl.name,
		tk.name,
		tl.TYPE,
		td.name,
		tl.defi,
		tl.upload_status,
		tl.use_status,
		tl.create_time,
		tl.qingxiurl,
		tl.gaoqing,
		tl.chaoqing,
		tl.error_info,
		org.name,
		tl.doc_type_detail,
		tl.is_uploadword 
	) a 
ORDER BY
	a.create_time DESC,
	a.id ASC
取消