当前使用版本(必填,否则不予处理)
V3.2.0
该问题是如何引起的？(确定最新版也有问题再提!!!)
SQLServer2005方言类，分页时 从第一个匹配的order by 开始截取sql，如果自定义sql含有子查询且子查询里含有order by
重现步骤(如果有就写完整)
自定义sql： SELECT customer.code,customer.name,customer.mobile_phone,customer.address, total.total_num,total.total_money,lastOne.times,lastOne.num,lastOne.paid_money,lastOne.create_date,lastOne.day_num FROM archive_customer customer LEFT JOIN (SELECT customer_id,SUM(num) total_num,SUM(paid_money) total_money FROM payment_record GROUP BY customer_id ) total ON customer.id=total.customer_id LEFT JOIN (SELECT TOP 1 customer_id,times,num,paid_money,create_date,datediff(day, create_date, GETDATE()) day_num FROM payment_record ORDER BY create_date DESC) lastOne ON customer.id=lastOne.customer_id
生成后的分页sql： WITH selectTemp AS (SELECT TOP 100 PERCENT ROW_NUMBER() OVER (ORDER BY create_date DESC) lastOne ON customer.id=lastOne.customer_id) as row_number, customer.code,customer.name,customer.mobile_phone,customer.address, total.total_num,total.total_money,lastOne.times,lastOne.num,lastOne.paid_money,lastOne.create_date,lastOne.day_num FROM archive_customer customer LEFT JOIN (SELECT customer_id,SUM(num) total_num,SUM(paid_money) total_money FROM payment_record GROUP BY customer_id ) total ON customer.id=total.customer_id LEFT JOIN (SELECT TOP 1 customer_id,times,num,paid_money,create_date,datediff(day, create_date, GETDATE()) day_num FROM payment_record ORDER BY create_date DESC) lastOne ON customer.id=lastOne.customer_id) SELECT * FROM selectTemp WHERE row_number BETWEEN 1 AND 20 ORDER BY row_number
报错信息
Comment From: miemieYaho
所以你的意见是?
Comment From: bingo0315
用lastIndexOf
------------------ 原始邮件 ------------------ 发件人: "miemieYaho"<notifications@github.com>; 发送时间: 2020年8月21日(星期五) 下午3:16 收件人: "baomidou/mybatis-plus"<mybatis-plus@noreply.github.com>; 抄送: "宋文宾"<3102281@qq.com>; "Author"<author@noreply.github.com>; 主题: Re: [baomidou/mybatis-plus] sqlserver2005方言分页时，如果自定sql的子查询有order by时会报错 (#2827)
所以你的意见是?
— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub, or unsubscribe.
Comment From: miemieYaho
比如?
Comment From: bingo0315
用lastIndexOf 也只是取巧的方式，没彻底解决问题。用lastIndexOf 则要求自定义sql 最后要需要加order by 才能避免出错
用lastIndexOf 生成后的分页sql： WITH selectTemp AS (SELECT TOP 100 PERCENT ROW_NUMBER() OVER ( ORDER BY customer.id DESC) as row_number, customer.code,customer.name,customer.mobile_phone,customer.address, total.total_num,total.total_money,lastOne.times,lastOne.num,lastOne.paid_money,lastOne.create_date,lastOne.day_num FROM archive_customer customer LEFT JOIN (SELECT customer_id,SUM(num) total_num,SUM(paid_money) total_money FROM payment_record GROUP BY customer_id ) total ON customer.id=total.customer_id LEFT JOIN (SELECT TOP 1 customer_id,times,num,paid_money,create_date,datediff(day, create_date, GETDATE()) day_num FROM payment_record ORDER BY create_date DESC) lastOne ON customer.id=lastOne.customer_id ORDER BY customer.id DESC ) SELECT * FROM selectTemp WHERE row_number BETWEEN 1 AND 20 ORDER BY row_number
------------------ 原始邮件 ------------------ 发件人: "baomidou/mybatis-plus" <notifications@github.com>; 发送时间: 2020年8月21日(星期五) 下午3:50 收件人: "baomidou/mybatis-plus"<mybatis-plus@noreply.github.com>; 抄送: "宋文宾"<3102281@qq.com>;"Author"<author@noreply.github.com>; 主题: Re: [baomidou/mybatis-plus] sqlserver2005方言分页时，如果自定sql的子查询有order by时会报错 (#2827)
比如?
— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub, or unsubscribe.
Comment From: miemieYaho
你这最后一个右括号对应的左括号在哪?
Comment From: Xushd529
同样的问题