本来有个要求,就是查询出同一组F_process_key
下,版本F_deployment_version
最高的数据,我的sql如下:
SELECT
wd.*
FROM
table_wd wd
WHERE
wd.F_id = (
SELECT
F_id
FROM
table_wd
WHERE F_process_key = wd.F_process_key
ORDER BY F_deployment_version DESC LIMIT 1
)
ORDER BY wd.F_id DESC;
在navicat上运行也没问题,然后就放到了mapper的xml中,并加上动态sql的条件判断
结果却出乎意料
日志打印:
格式化后的sql如下:
SELECT
wd.*
FROM
t_ticket_workflow_deployment wd
WHERE
1 = 1
AND wd.F_id = (
SELECT
F_id,
F_deployment_version AS sharding_gen_1
FROM
t_ticket_workflow_deployment
WHERE
F_process_key = wd.F_process_key
AND F_deployment_status = 2
ORDER BY
F_deployment_version DESC
LIMIT 1
)
ORDER BY
wd.F_id DESC
可以看到莫名其妙多了一个F_deployment_version AS sharding_gen_1
,并直接导致了子查询返回两个结果,报错。
初步判断是sharding-jdbc
搞的鬼。但是网上搜索了没找到有用信息
最后妥协解决方法: 子查询改成自己和自己连接查询
SELECT
a.*
FROM
t_ticket_workflow_deployment a,
(
SELECT
F_process_key,
MAX(F_deployment_version) v
FROM
t_ticket_workflow_deployment
GROUP BY
F_process_key
) b
WHERE
b.F_process_key = a.F_process_key
AND b.v = a.F_deployment_version
AND a.F_deployment_status = 2
GROUP BY
a.F_process_key
ORDER BY
a.F_id DESC
有知道如何解决或者问题原因的欢迎指导