WITH allSorts AS (
SELECT s.*
FROM Sort s
where s.isDel = 0
and s.kid = #{sortKid}
UNION ALL
SELECT s.*
FROM Sort s
INNER JOIN allSorts a ON a.parentId = convert(varchar (36), s.kid)
WHERE s.isdel = 0
)
select c.*
from ContractInfo c
inner join SectionInfo s on c.sectionIds = convert(varchar (36), s.kid)
inner join (select top 1 * from allSorts where allSorts.isdel = 0 ORDER BY iid asc) a
on a.parentId = convert(varchar (36), s.kid)
where c.isdel = 0
and s.isdel = 0
and c.contractType = 4
你的mysql 什么版本?,用这个sql查询一下:
select version();
mysql8.0也支持这个了with as,你的mysql是什么版本
应该不是不支持with as 吧 看下第9行 with 后面表名allSorts 在as后面括号里也用到了 是这个不支持吧
select c.* allSorts
from ContractInfo c
inner join SectionInfo s
on c.sectionIds = cast(s.kid as varchar)
inner join (select *
from (SELECT s.*
FROM Sort s
where s.isDel = 0
and s.kid = #{sortKid}
UNION ALL
SELECT s.*
FROM Sort s
INNER JOIN allSorts a ON a.parentId = cast(s.kid as varchar)
WHERE s.isdel = 0
) allSorts
where allSorts.isdel = 0
ORDER BY iid asc limit 1 ) a
on a.parentId = cast(s.kid as varchar )
where c.isdel = 0
and s.isdel = 0
and c.contractType = 4
版本太低了,在mysql5.7及以下的版本中没有“with as”语句,在mysql8.0以上的版本中有“with as”语句;
mysql5.7不支持该语句,但是可以考虑通过创建临时表的方式来实现同样的效果,在mysql8.0之后就支持利用该语句进行子查询。
如有帮助,还请采纳!
你这里面涉及到递归查询父结点,用mysql5直接写sql不好实现。
可以在sql中引入变量的方式来实现。你可以参考这篇https://www.cnblogs.com/liuxiaoji/p/15219091.html
版本太低了,在mysql5.7及以下的版本中没有“with as”语句,在mysql8.0以上的版本中有“with as”语句,可以考虑升级一下数据库
看一下是不是版本不支持语法,找到当前版本相应兼容的语法代替即可
你的mysql 版本应该不支持with as