SQL问题记录

小半 • 2023年1月15日下午9:20 • 技术随笔 • 阅读 171

exception 信息 Table ‘p’ from one of the SELECTs cannot be used in field list

环境： jpa 自己写了原生sql 包含union 和 order by 。
现象：自己在mysql行运行完全没问题，放到jpa的@query里就不行了，然后就报了 Table ‘p’ from one of the SELECTs cannot be used in field list 。
实例：sql – 命令行好使的语句
select s.* from table_name p,table_name s where p.id = s.pid and ((p.is_deleted = '0' and s.is_deleted = '0' and p.name like :name) or (s.is_deleted = '0' and s.name like :name and s.pid<>0)) union select p.* from table_name p left join table_name s on p.id = s.pid where p.pid=0 and s.id is null and p.is_deleted = '0' and p.name like :name union select p.* from table_name p left join table_name s on p.id = s.pid where p.pid=0 and p.is_deleted = '0' and p.name like :name GROUP BY s.pid having count(s.id) = sum(s.is_deleted) order by create_time desc。
原因：仔细观察啊观察啊发现 jpa 处理 order by 会加一个 id的类sql 被修改为了如下前边都一样忽略 order by create_time desc, p.id desc limit ? 直接加了一个 p.id desc 为啥会加那，没看源码不清楚，但是观察p是我第一个表的别名。所以直接改造sql将结果放到一个零时表里并将零时表起名为p 然后就兼容了 jpa自己加的p.id。
最终如下 select p.* from (与上边一样的union 逻辑) as p order by p.create_time desc OK兼容了jpa的骚操作。不报错了。

exception 信息 could not prepare statement; nested exception is org.hibernate.exception.GenericJDBCException: could not prepare statement

这个错太笼统了意思就是你sql错误看日志：select count(p.* from(select s.*) from 什么玩意啊，count把sql截取错了，好吧自己写了count语句好使了
环境： jpa 自己写了原生sql 包含union 和 order by 。
现象：jpa自己截取的count语句错误
实例：jpa自己创建的count 语句样例 select count(p.* from(select s.*) from table_name p,table_name s where p.id = s.pid and ((p.is_deleted = '0' and s.is_deleted = '0' and p.name like ?) or (s.is_deleted = '0' and s.name like ? and s.pid<>0)) union select p1.* from table_name p1 left join table_name s1 on p1.id = s1.pid where p1.pid=0 and s1.id is null and p1.is_deleted = '0' and p1.name like ? union select p2.* from table_name p2 left join table_name s2 on p2.id = s2.pid where p2.pid=0 and p2.is_deleted = '0' and p2.name like ? GROUP BY s2.pid having count(s2.id) = sum(s2.is_deleted) ) as p
解决方案 自己写了 countQuery =”XXX”