MyBatis-Plus 分页查询统计信息不兼容子查询

当前使用版本(必填,否则不予处理)

3.3.2

该问题是如何引起的？(确定最新版也有问题再提!!!)

分页不能完整执行预设语句

重现步骤(如果有就写完整)

    select 
    IF(guideScenicSpotId IS NULL,voiceScenicSpotId,guideScenicSpotId) AS  scenicSpotId,
    IF(guideStatisticalDate IS NULL,voiceStatisticalDate,guideStatisticalDate) AS  statisticalDate,
           t.*
    from (
             SELECT g.scenicSpotId      AS guideScenicSpotId

。。。。。。）t where statisticalDate >=#{startTime}

执行count语句为 SELECT COUNT(1) FROM (SELECT g.scenicSpotId AS guideScenicSpotId, g.orderType AS guideOrderType

报错信息

直接查询的子查询的语句进行了分页统计，未能完整执行语句。 java.sql.SQLSyntaxErrorException: Unknown column 'statisticalDate' in 'where clause'

Comment From: miemieYaho

原始sql发完整看看

Comment From: spvycf

    select
    IF(guideScenicSpotId IS NULL,voiceScenicSpotId,guideScenicSpotId) AS  scenicSpotId,
    IF(guideStatisticalDate IS NULL,voiceStatisticalDate,guideStatisticalDate) AS  statisticalDate,
           t.*
    from (
             SELECT g.scenicSpotId      AS guideScenicSpotId,
                    g.orderType         AS guideOrderType,
                    g.orderNum          AS guideOrderNum,
                    g.totalGetAmount    AS guideTotalGetAmount,
                    g.refundNum         AS guideRefundNum,
                    g.totalRefundAmount AS guideTotalRefundAmount,
                    g.statisticalDate   AS guideStatisticalDate,

                    v.scenicSpotId      AS voiceScenicSpotId,
                    v.orderType         AS voiceOrderType,
                    v.orderNum          AS voiceOrderNum,
                    v.totalGetAmount    AS voiceTotalGetAmount,
                    v.refundNum         AS voiceRefundNum,
                    v.totalRefundAmount AS voiceTotalRefundAmount,
                    v.statisticalDate   AS voiceStatisticalDate
             FROM `v_guideOrder_day` g
                      LEFT JOIN `v_voiceOrder_day` v
                                ON g.`scenicSpotId` = v.`scenicSpotId` AND
                                   g.`statisticalDate` = v.`statisticalDate`
             UNION ALL
             SELECT g.scenicSpotId      AS guideScenicSpotId,
                    g.orderType         AS guideOrderType,
                    g.orderNum          AS guideOrderNum,
                    g.totalGetAmount    AS guideTotalGetAmount,
                    g.refundNum         AS guideRefundNum,
                    g.totalRefundAmount AS guideTotalRefundAmount,
                    g.statisticalDate   AS guideStatisticalDate,

                    v.scenicSpotId      AS voiceScenicSpotId,
                    v.orderType         AS voiceOrderType,
                    v.orderNum          AS voiceOrderNum,
                    v.totalGetAmount    AS voiceTotalGetAmount,
                    v.refundNum         AS voiceRefundNum,
                    v.totalRefundAmount AS voiceTotalRefundAmount,
                    v.statisticalDate   AS voiceStatisticalDate
             FROM `v_guideOrder_day` g
                      RIGHT JOIN `v_voiceOrder_day` v
                                 ON g.`scenicSpotId` = v.`scenicSpotId` AND
                                    g.`statisticalDate` = v.`statisticalDate`
         ) t
    where 1=1
    <if test="startTime!=null ">
        and  statisticalDate &gt;=#{startTime}
    </if>
    <if test="endTime!=null ">
        and  statisticalDate &lt;=#{endTime}
    </if>

Comment From: spvycf

Error querying database. Cause: com.baomidou.mybatisplus.core.exceptions.MybatisPlusException: Error: Method queryTotal execution error of sql :

SELECT COUNT(1) FROM (SELECT g.scenicSpotId AS guideScenicSpotId, g.orderType AS guideOrderType, g.orderNum AS guideOrderNum, g.totalGetAmount AS guideTotalGetAmount, g.refundNum AS guideRefundNum, g.totalRefundAmount AS guideTotalRefundAmount, g.statisticalDate AS guideStatisticalDate, v.scenicSpotId AS voiceScenicSpotId, v.orderType AS voiceOrderType, v.orderNum AS voiceOrderNum, v.totalGetAmount AS voiceTotalGetAmount, v.refundNum AS voiceRefundNum, v.totalRefundAmount AS voiceTotalRefundAmount, v.statisticalDate AS voiceStatisticalDate FROM v_guideOrder_day g LEFT JOIN v_voiceOrder_day v ON g.scenicSpotId = v.scenicSpotId AND g.statisticalDate = v.statisticalDate UNION ALL SELECT g.scenicSpotId AS guideScenicSpotId, g.orderType AS guideOrderType, g.orderNum AS guideOrderNum, g.totalGetAmount AS guideTotalGetAmount, g.refundNum AS guideRefundNum, g.totalRefundAmount AS guideTotalRefundAmount, g.statisticalDate AS guideStatisticalDate, v.scenicSpotId AS voiceScenicSpotId, v.orderType AS voiceOrderType, v.orderNum AS voiceOrderNum, v.totalGetAmount AS voiceTotalGetAmount, v.refundNum AS voiceRefundNum, v.totalRefundAmount AS voiceTotalRefundAmount, v.statisticalDate AS voiceStatisticalDate FROM v_guideOrder_day g RIGHT JOIN v_voiceOrder_day v ON g.scenicSpotId = v.scenicSpotId AND g.statisticalDate = v.statisticalDate) t WHERE 1 = 1 AND statisticalDate >= ? AND statisticalDate <= ? AND scenicSpotId IN (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)

The error may exist in file [E:\talkman\talkman-service-order\target\classes\mapper\GuideorderDayMapper.xml]

The error may involve defaultParameterMap

The error occurred while setting parameters

Cause: com.baomidou.mybatisplus.core.exceptions.MybatisPlusException: Error: Method queryTotal execution error of sql :

Comment From: miemieYaho

你这种只能自己写count了,不信你可以试试pagehelper能不能给出你想要的count

Comment From: the-Rings

如何在分页时，使用自己写的count语句

Comment From: miemieYaho

最新正式版下自己count一下然后set进page