记一次springboot+mybatis+phoenix在代码集成中的坑

时间:2024-02-29 14:08:41

场景:

希望使用phoenix做查询服务,给服务端提供接口

设计:

通过springboot做restful的接口发布,通过mybatis做phoenix的sql处理,因此是springboot+mybatis+phoenix的集成方案;

但是会遇到一个坑!

=========================================我是华丽的分割线=====================================================

先简单汇报下场景:

做网约车的预约单查询处理,如图:

 

这里面需要使用一个时间窗口查询6种时间:推送订单时间、有效抢单时间、抢单成功时间、取消订单时间、达到目的地时间、支付时间

这里可能遇到一个问题:跨天支付!

因此为了解决跨天支付,需要在处理好的基表基础上做6次查询 ,分别对应不同的查询时间,然后做union处理,最后在求和(注意:就是在最后在求和的地方出错!!!!!)

先看下有问题的代码:

<select id="searchContext" resultType="com.df.entity.SearchInfo">
        SELECT
        TB."driver_id",
        TB."driver_name"",
        TB."mobile",
        TB."alliance_name",
        TB."register_city",
        TB."driver_type",
        SUM(TB."pushOrderNum") AS "pushOrderNum" ,
        SUM(TB."effectiveSlogan") AS "effectiveSlogan",
        SUM(TB."grab_sus_order") AS "grab_sus_order",
        SUM(TB."cancel_order_num") AS "cancel_order_num",
        SUM(TB."cancel_passenger_num") AS "cancel_passenger_num",
        SUM(TB."cancel_driver_num") AS "cancel_driver_num",
        SUM(TB."cancel_service_num") AS "cancel_service_num",
        SUM(TB."destination_reached_num") AS "destination_reached_num",
        SUM(TB."pay_num") AS "pay_num",
        SUM(TB."order_cumulative_num") AS "order_cumulative_num",
        SUM(TB."order_pay_cumulative_num") AS "order_pay_cumulative_num"
        FROM
        (
        SELECT
        "driver_id" ,
        "driver_name" ,
        "mobile" ,
        "alliance_name" ,
        "register_city" ,
        "driver_type" ,
        sum(to_number("pushOrderNum")) AS "pushOrderNum" ,
        sum(0) AS "effectiveSlogan" ,
        sum(0) AS "grab_sus_order" ,
        sum(0) AS "cancel_order_num",
        sum(0) AS "cancel_passenger_num",
        sum(0) AS "cancel_driver_num",
        sum(0) AS "cancel_service_num" ,
        sum(0) AS "destination_reached_num" ,
        sum(0) AS "pay_num" ,
        sum(0) AS "order_cumulative_num",
        sum(0) AS "order_pay_cumulative_num"
        FROM
        "_crontab_reservation"
        WHERE
        CASE WHEN "pushOrderTime" != \'0\' THEN
        TO_DATE("pushOrderTime",\'yyyy-MM-dd\') >= TO_DATE(#{start_time},\'yyyy-MM-dd\')
        AND
        TO_DATE("pushOrderTime",\'yyyy-MM-dd\') &lt;= TO_DATE(#{end_time},\'yyyy-MM-dd\')
        ELSE 1=1 END
        <if test="driver_id != null and driver_id !=\'\'" >
            and "driver_id"= #{driver_id}
        </if>
        <if test="driver_name != null and driver_name !=\'\'" >
            and "driver_name" = #{driver_name}
        </if>
        <if test="mobile != null and mobile !=\'\'" >
            and "mobile" = #{mobile}
        </if>
        <if test="alliance_name != null and alliance_name !=\'\'" >
            and "driver_management_id" = #{alliance_name}
        </if>
        <if test="register_city != null and register_city !=\'\'" >
            and substr("register_city" , 0 , 4) = substr(#{register_city} , 0 , 4)
        </if>
        <if test="driver_type != null and driver_type !=\'\'" >
            and "driver_type" = #{driver_type}
        </if>
        GROUP BY
        "driver_id" ,
        "driver_name" ,
        "mobile" ,
        "alliance_name" ,
        "register_city" ,
        "driver_type"

        union all


        SELECT
        "driver_id" ,
        "driver_name" ,
        "mobile" ,
        "alliance_name" ,
        "register_city" ,
        "driver_type" ,
        sum(0) AS "pushOrderNum" ,
        sum(to_number("effectiveSlogan")) AS "effectiveSlogan" ,
        sum(0) AS "grab_sus_order" ,
        sum(0) AS "cancel_order_num",
        sum(0) AS "cancel_passenger_num",
        sum(0) AS "cancel_driver_num",
        sum(0) AS "cancel_service_num" ,
        sum(0) AS "destination_reached_num" ,
        sum(0) AS "pay_num" ,
        sum(0) AS "order_cumulative_num",
        sum(0) AS "order_pay_cumulative_num"
        FROM
        "_crontab_reservation"
        WHERE
        CASE WHEN "_effective_glab_time" != \'0\' THEN
        TO_DATE("_effective_glab_time",\'yyyy-MM-dd\') >= TO_DATE(#{start_time},\'yyyy-MM-dd\')
        AND
        TO_DATE("_effective_glab_time",\'yyyy-MM-dd\') &lt;= TO_DATE(#{end_time},\'yyyy-MM-dd\')
        ELSE 1=1 END
        <if test="driver_id != null and driver_id !=\'\'" >
            and "driver_id"= #{driver_id}
        </if>
        <if test="driver_name != null and driver_name !=\'\'" >
            and "driver_name" = #{driver_name}
        </if>
        <if test="mobile != null and mobile !=\'\'" >
            and "mobile" = #{mobile}
        </if>
        <if test="alliance_name != null and alliance_name !=\'\'" >
            and "driver_management_id" = #{alliance_name}
        </if>
        <if test="register_city != null and register_city !=\'\'" >
            and substr("register_city" , 0 , 4) = substr(#{register_city} , 0 , 4)
        </if>
        <if test="driver_type != null and driver_type !=\'\'" >
            and "driver_type" = #{driver_type}
        </if>
        GROUP BY
        "driver_id" ,
        "driver_name" ,
        "mobile" ,
        "alliance_name" ,
        "register_city" ,
        "driver_type"

        union all


        SELECT
        "driver_id" ,
        "driver_name" ,
        "mobile" ,
        "alliance_name" ,
        "register_city" ,
        "driver_type" ,
        sum(0) AS "pushOrderNum" ,
        sum(0) AS "effectiveSlogan" ,
        sum(to_number("grab_sus_order")) AS "grab_sus_order" ,
        sum(0) AS "cancel_order_num",
        sum(0) AS "cancel_passenger_num",
        sum(0) AS "cancel_driver_num",
        sum(0) AS "cancel_service_num" ,
        sum(0) AS "destination_reached_num" ,
        sum(0) AS "pay_num" ,
        sum(0) AS "order_cumulative_num",
        sum(0) AS "order_pay_cumulative_num"
        FROM
        "_crontab_reservation"
        WHERE
        CASE WHEN "grab_sus_time" != \'0\' THEN
        TO_DATE("grab_sus_time" , \'yyyy-MM-dd\') >= TO_DATE(#{start_time} , \'yyyy-MM-dd\')
        AND
        TO_DATE("grab_sus_time" , \'yyyy-MM-dd\') &lt;= TO_DATE(#{end_time} , \'yyyy-MM-dd\')
        ELSE 1=1 END
        <if test="driver_id != null and driver_id !=\'\'" >
            and "driver_id"= #{driver_id}
        </if>
        <if test="driver_name != null and driver_name !=\'\'" >
            and "driver_name" = #{driver_name}
        </if>
        <if test="mobile != null and mobile !=\'\'" >
            and "mobile" = #{mobile}
        </if>
        <if test="alliance_name != null and alliance_name !=\'\'" >
            and "driver_management_id" = #{alliance_name}
        </if>
        <if test="register_city != null and register_city !=\'\'" >
            and substr("register_city" , 0 , 4) = substr(#{register_city} , 0 , 4)
        </if>
        <if test="driver_type != null and driver_type !=\'\'" >
            and "driver_type" = #{driver_type}
        </if>
        GROUP BY
        "driver_id" ,
        "driver_name" ,
        "mobile" ,
        "alliance_name" ,
        "register_city" ,
        "driver_type"

        union all


        SELECT
        "driver_id" ,
        "driver_name" ,
        "mobile" ,
        "alliance_name" ,
        "register_city" ,
        "driver_type" ,
        sum(0) AS "pushOrderNum" ,
        sum(0) AS "effectiveSlogan" ,
        sum(0) AS "grab_sus_order" ,
        sum(to_number("cancel_order_num")) AS "cancel_order_num",
        sum(to_number("cancel_passenger_num")) AS "cancel_passenger_num",
        sum(to_number("cancel_driver_num")) AS "cancel_driver_num",
        sum(to_number("cancel_service_num")) AS "cancel_service_num" ,
        sum(0) AS "destination_reached_num" ,
        sum(0) AS "pay_num" ,
        sum(0) AS "order_cumulative_num",
        sum(0) AS "order_pay_cumulative_num"
        FROM
        "_crontab_reservation"
        WHERE
        CASE WHEN "cancel_time" != \'0\' THEN
        TO_DATE("cancel_time",\'yyyy-MM-dd\') >= TO_DATE(#{start_time},\'yyyy-MM-dd\')
        AND
        TO_DATE("cancel_time",\'yyyy-MM-dd\') &lt;= TO_DATE(#{end_time},\'yyyy-MM-dd\')
        ELSE 1=1 END
        <if test="driver_id != null and driver_id !=\'\'" >
            and "driver_id"= #{driver_id}
        </if>
        <if test="driver_name != null and driver_name !=\'\'" >
            and "driver_name" = #{driver_name}
        </if>
        <if test="mobile != null and mobile !=\'\'" >
            and "mobile" = #{mobile}
        </if>
        <if test="alliance_name != null and alliance_name !=\'\'" >
            and "driver_management_id" = #{alliance_name}
        </if>
        <if test="register_city != null and register_city !=\'\'" >
            and substr("register_city" , 0 , 4) = substr(#{register_city} , 0 , 4)
        </if>
        <if test="driver_type != null and driver_type !=\'\'" >
            and "driver_type" = #{driver_type}
        </if>
        GROUP BY
        "driver_id" ,
        "driver_name" ,
        "mobile" ,
        "alliance_name" ,
        "register_city" ,
        "driver_type"

        union all


        SELECT
        "driver_id" ,
        "driver_name" ,
        "mobile" ,
        "alliance_name" ,
        "register_city" ,
        "driver_type" ,
        sum(0) AS "pushOrderNum" ,
        sum(0) AS "effectiveSlogan" ,
        sum(0) AS "grab_sus_order" ,
        sum(0) AS "cancel_order_num",
        sum(0) AS "cancel_passenger_num",
        sum(0) AS "cancel_driver_num",
        sum(0) AS "cancel_service_num" ,
        sum(to_number("destination_reached_num")) AS "destination_reached_num" ,
        sum(0) AS "pay_num" ,
        sum(0) AS "order_cumulative_num",
        sum(0) AS "order_pay_cumulative_num"
        FROM
        "_crontab_reservation"
        WHERE
        CASE WHEN "close_gps_time" != \'0\' THEN
        TO_DATE("close_gps_time",\'yyyy-MM-dd\') >= TO_DATE(#{start_time},\'yyyy-MM-dd\')
        AND
        TO_DATE("close_gps_time",\'yyyy-MM-dd\') &lt;= TO_DATE(#{end_time},\'yyyy-MM-dd\')
        ELSE 1=1 END
        <if test="driver_id != null and driver_id !=\'\'" >
            and "driver_id"= #{driver_id}
        </if>
        <if test="driver_name != null and driver_name !=\'\'" >
            and "driver_name" = #{driver_name}
        </if>
        <if test="mobile != null and mobile !=\'\'" >
            and "mobile" = #{mobile}
        </if>
        <if test="alliance_name != null and alliance_name !=\'\'" >
            and "driver_management_id" = #{alliance_name}
        </if>
        <if test="register_city != null and register_city !=\'\'" >
            and substr("register_city" , 0 , 4) = substr(#{register_city} , 0 , 4)
        </if>
        <if test="driver_type != null and driver_type !=\'\'" >
            and "driver_type" = #{driver_type}
        </if>
        GROUP BY
        "driver_id" ,
        "driver_name" ,
        "mobile" ,
        "alliance_name" ,
        "register_city" ,
        "driver_type"

        union all

        SELECT
        "driver_id" ,
        "driver_name" ,
        "mobile" ,
        "alliance_name" ,
        "register_city" ,
        "driver_type" ,
        sum(0) AS "pushOrderNum" ,
        sum(0) AS "effectiveSlogan" ,
        sum(0) AS "grab_sus_order" ,
        sum(0) AS "cancel_order_num",
        sum(0) AS "cancel_passenger_num",
        sum(0) AS "cancel_driver_num",
        sum(0) AS "cancel_service_num" ,
        sum(0) AS "destination_reached_num" ,
        sum(to_number("pay_num")) AS "pay_num" ,
        sum(to_number("order_cumulative_num")) AS "order_cumulative_num",
        sum(to_number("order_pay_cumulative_num")) AS "order_pay_cumulative_num"
        FROM
        "_crontab_reservation"
        WHERE
        CASE WHEN "pay_time" != \'0\' THEN
        TO_DATE("pay_time",\'yyyy-MM-dd\') >= TO_DATE(#{start_time},\'yyyy-MM-dd\')
        AND
        TO_DATE("pay_time",\'yyyy-MM-dd\') &lt;= TO_DATE(#{end_time},\'yyyy-MM-dd\')
        ELSE 1=1 END
        <if test="driver_id != null and driver_id !=\'\'" >
            and "driver_id"= #{driver_id}
        </if>
        <if test="driver_name != null and driver_name !=\'\'" >
            and "driver_name" = #{driver_name}
        </if>
        <if test="mobile != null and mobile !=\'\'" >
            and "mobile" = #{mobile}
        </if>
        <if test="alliance_name != null and alliance_name !=\'\'" >
            and "driver_management_id" = #{alliance_name}
        </if>
        <if test="register_city != null and register_city !=\'\'" >
            and substr("register_city" , 0 , 4) = substr(#{register_city} , 0 , 4)
        </if>
        <if test="driver_type != null and driver_type !=\'\'" >
            and "driver_type" = #{driver_type}
        </if>
        GROUP BY
        "driver_id" ,
        "driver_name" ,
        "mobile" ,
        "alliance_name" ,
        "register_city" ,
        "driver_type"
        ) as TB
        GROUP BY
        TB."driver_id" ,
        TB."driver_name" ,
        TB."mobile" ,
        TB."alliance_name" ,
        TB."register_city" ,
        TB."driver_type"
    </select>
有问题的代码

这段代码大概就是:

select 
tb."driver_id" ... ,
sum(xxx1) , 
sum(xxx2) , 
.....
from
(
 A union B union C union D union E union F union G   
) as tb
group by
tb."driver_id" ...

这种操作的问题就在于:使用mybatis做union后,生成了一张表tb , 按照phoenix的官方说法是,phoenix严格区别大小写的,因此我们做完phoenix后,习惯性的在字段上加上双引号;

但是如果是使用mybatis做了集成后,union生成的表名称和字段名称,是不需要也不能在区分大小写的,因此在加双引号,就会出现字段找不到等一系列错误

 解决后的代码:

<select id="searchContext" resultType="com.df.entity.SearchInfo">
        SELECT
        "TB1".driver_id,
        "TB1".driver_name,
        "TB1".mobile,
        "TB1".alliance_name,
        "TB1".register_city,
        "TB1".driver_type,
        SUM("TB1".pushOrderNum) AS "pushOrderNum" ,
        SUM("TB1".effectiveSlogan) AS "effectiveSlogan",
        SUM("TB1".grab_sus_order) AS "grab_sus_order",
        SUM("TB1".cancel_order_num) AS "cancel_order_num",
        SUM("TB1".cancel_passenger_num) AS "cancel_passenger_num",
        SUM("TB1".cancel_driver_num) AS "cancel_driver_num",
        SUM("TB1".cancel_service_num) AS "cancel_service_num",
        SUM("TB1".destination_reached_num) AS "destination_reached_num",
        SUM("TB1".pay_num) AS "pay_num",
        SUM("TB1".order_cumulative_num) AS "order_cumulative_num",
        SUM("TB1".order_pay_cumulative_num) AS "order_pay_cumulative_num"
        from
        (SELECT
        "driver_id" ,
        "driver_name" ,
        "mobile" ,
        "alliance_name" ,
        "register_city" ,
        "driver_type" ,
        sum(to_number("pushOrderNum")) AS "pushOrderNum" ,
        sum(0) AS "effectiveSlogan" ,
        sum(0) AS "grab_sus_order" ,
        sum(0) AS "cancel_order_num",
        sum(0) AS "cancel_passenger_num",
        sum(0) AS "cancel_driver_num",
        sum(0) AS "cancel_service_num" ,
        sum(0) AS "destination_reached_num" ,
        sum(0) AS "pay_num" ,
        sum(0) AS "order_cumulative_num",
        sum(0) AS "order_pay_cumulative_num"
        FROM
        "_crontab_reservation"
        WHERE
        CASE WHEN "pushOrderTime" != \'0\' THEN
        TO_DATE("pushOrderTime",\'yyyy-MM-dd\') >= TO_DATE(#{start_time},\'yyyy-MM-dd\')
        AND
        TO_DATE("pushOrderTime",\'yyyy-MM-dd\') &lt;= TO_DATE(#{end_time},\'yyyy-MM-dd\')
        ELSE 1=1 END
        <if test="driver_id != null and driver_id !=\'\'">
            and "driver_id"= #{driver_id}
        </if>
        <if test="driver_name != null and driver_name !=\'\'">
            and "driver_name" = #{driver_name}
        </if>
        <if test="mobile != null and mobile !=\'\'">
            and "mobile" = #{mobile}
        </if>
        <if test="alliance_name != null and alliance_name !=\'\'">
            and "driver_management_id" = #{alliance_name}
        </if>
        <if test="register_city != null and register_city !=\'\'">
            and substr("register_city" , 0 , 4) = substr(#{register_city} , 0 , 4)
        </if>
        <if test="driver_type != null and driver_type !=\'\'">
            and "driver_type" = #{driver_type}
        </if>
        GROUP BY
        "driver_id" ,
        "driver_name" ,
        "mobile" ,
        "alliance_name" ,
        "register_city" ,
        "driver_type"

        union all


        SELECT
        "driver_id" ,
        "driver_name" ,
        "mobile" ,
        "alliance_name" ,
        "register_city" ,
        "driver_type" ,
        sum(0) AS "pushOrderNum" ,
        sum(to_number("effectiveSlogan")) AS "effectiveSlogan" ,
        sum(0) AS "grab_sus_order" ,
        sum(0) AS "cancel_order_num",
        sum(0) AS "cancel_passenger_num",
        sum(0) AS "cancel_driver_num",
        sum(0) AS "cancel_service_num" ,
        sum(0) AS "destination_reached_num" ,
        sum(0) AS "pay_num" ,
        sum(0) AS "order_cumulative_num",
        sum(0) AS "order_pay_cumulative_num"
        FROM
        "_crontab_reservation"
        WHERE
        CASE WHEN "_effective_glab_time" != \'0\' THEN
        TO_DATE("_effective_glab_time",\'yyyy-MM-dd\') >= TO_DATE(#{start_time},\'yyyy-MM-dd\')
        AND
        TO_DATE("_effective_glab_time",\'yyyy-MM-dd\') &lt;= TO_DATE(#{end_time},\'yyyy-MM-dd\')
        ELSE 1=1 END
        <if test="driver_id != null and driver_id !=\'\'">
            and "driver_id"= #{driver_id}
        </if>
        <if test="driver_name != null and driver_name !=\'\'">
            and "driver_name" = #{driver_name}
        </if>
        <if test="mobile != null and mobile !=\'\'">
            and "mobile" = #{mobile}
        </if>
        <if test="alliance_name != null and alliance_name !=\'\'">
            and "driver_management_id" = #{alliance_name}
        </if>
        <if test="register_city != null and register_city !=\'\'">
            and substr("register_city" , 0 , 4) = substr(#{register_city} , 0 , 4)
        </if>
        <if test="driver_type != null and driver_type !=\'\'">
            and "driver_type" = #{driver_type}
        </if>
        GROUP BY
        "driver_id" ,
        "driver_name" ,
        "mobile" ,
        "alliance_name" ,
        "register_city" ,
        "driver_type"

        union all


        SELECT
        "driver_id" ,
        "driver_name" ,
        "mobile" ,
        "alliance_name" ,
        "register_city" ,
        "driver_type" ,
        sum(0) AS "pushOrderNum" ,
        sum(0) AS "effectiveSlogan" ,
        sum(to_number("grab_sus_order")) AS "grab_sus_order" ,
        sum(0) AS "cancel_order_num",
        sum(0) AS "cancel_passenger_num",
        sum(0) AS "cancel_driver_num",
        sum(0) AS "cancel_service_num" ,
        sum(0) AS "destination_reached_num" ,
        sum(0) AS "pay_num" ,
        sum(0) AS "order_cumulative_num",
        sum(0) AS "order_pay_cumulative_num"
        FROM
        "_crontab_reservation"
        WHERE
        CASE WHEN "grab_sus_time" != \'0\' THEN
        TO_DATE("grab_sus_time" , \'yyyy-MM-dd\') >= TO_DATE(#{start_time} , \'yyyy-MM-dd\')
        AND
        TO_DATE("grab_sus_time" , \'yyyy-MM-dd\') &lt;= TO_DATE(#{end_time} , \'yyyy-MM-dd\')
        ELSE 1=1 END
        <if test="driver_id != null and driver_id !=\'\'">
            and "driver_id"= #{driver_id}
        </if>
        <if test="driver_name != null and driver_name !=\'\'">
            and "driver_name" = #{driver_name}
        </if>
        <if test="mobile != null and mobile !=\'\'">
            and "mobile" = #{mobile}
        </if>
        <if test="alliance_name != null and alliance_name !=\'\'">
            and "driver_management_id" = #{alliance_name}
        </if>
        <if test="register_city != null and register_city !=\'\'">
            and substr("register_city" , 0 , 4) = substr(#{register_city} , 0 , 4)
        </if>
        <if test="driver_type != null and driver_type !=\'\'">
            and "driver_type" = #{driver_type}
        </if>
        GROUP BY
        "driver_id" ,
        "driver_name" ,
        "mobile" ,
        "alliance_name" ,
        "register_city" ,
        "driver_type"

        union all


        SELECT
        "driver_id" ,
        "driver_name" ,
        "mobile" ,
        "alliance_name" ,
        "register_city" ,
        "driver_type" ,
        sum(0) AS "pushOrderNum" ,
        sum(0) AS "effectiveSlogan" ,
        sum(0) AS "grab_sus_order" ,
        sum(to_number("cancel_order_num")) AS "cancel_order_num",
        sum(to_number("cancel_passenger_num")) AS "cancel_passenger_num",
        sum(to_number("cancel_driver_num")) AS "cancel_driver_num",
        sum(to_number("cancel_service_num")) AS "cancel_service_num" ,
        sum(0) AS "destination_reached_num" ,
        sum(0) AS "pay_num" ,
        sum(0) AS "order_cumulative_num",
        sum(0) AS "order_pay_cumulative_num"
        FROM
        "_crontab_reservation"
        WHERE
        CASE WHEN "cancel_time" != \'0\' THEN
        TO_DATE("cancel_time",\'yyyy-MM-dd\') >= TO_DATE(#{start_time},\'yyyy-MM-dd\')
        AND
        TO_DATE("cancel_time",\'yyyy-MM-dd\') &lt;= TO_DATE(#{end_time},\'yyyy-MM-dd\')
        ELSE 1=1 END
        <if test="driver_id != null and driver_id !=\'\'">
            and "driver_id"= #{driver_id}
        </if>
        <if test="driver_name != null and driver_name !=\'\'">
            and "driver_name" = #{driver_name}
        </if>
        <if test="mobile != null and mobile !=\'\'">
            and "mobile" = #{mobile}
        </if>
        <if test="alliance_name != null and alliance_name !=\'\'">
            and "driver_management_id" = #{alliance_name}
        </if>
        <if test="register_city != null and register_city !=\'\'">
            and substr("register_city" , 0 , 4) = substr(#{register_city} , 0 , 4)
        </if>
        <if test="driver_type != null and driver_type !=\'\'">
            and "driver_type" = #{driver_type}
        </if>
        GROUP BY
        "driver_id" ,
        "driver_name" ,
        "mobile" ,
        "alliance_name" ,
        "register_city" ,
        "driver_type"

        union all


        SELECT
        "driver_id" ,
        "driver_name" ,
        "mobile" ,
        "alliance_name" ,
        "register_city" ,
        "driver_type" ,
        sum(0) AS "pushOrderNum" ,
        sum(0) AS "effectiveSlogan" ,
        sum(0) AS "grab_sus_order" ,
        sum(0) AS "cancel_order_num",
        sum(0) AS "cancel_passenger_num",
        sum(0) AS "cancel_driver_num",
        sum(0) AS "cancel_service_num" ,
        sum(to_number("destination_reached_num")) AS "destination_reached_num" ,
        sum(0) AS "pay_num" ,
        sum(0) AS "order_cumulative_num",
        sum(0) AS "order_pay_cumulative_num"
        FROM
        "_crontab_reservation"
        WHERE
        CASE WHEN "close_gps_time" != \'0\' THEN
        TO_DATE("close_gps_time",\'yyyy-MM-dd\') >= TO_DATE(#{start_time},\'yyyy-MM-dd\')
        AND
        TO_DATE("close_gps_time",\'yyyy-MM-dd\') &lt;= TO_DATE(#{end_time},\'yyyy-MM-dd\')
        ELSE 1=1 END
        <if test="driver_id != null and driver_id !=\'\'">
            and "driver_id"= #{driver_id}
        </if>
        <if test="driver_name != null and driver_name !=\'\'">
            and "driver_name" = #{driver_name}
        </if>
        <if test="mobile != null and mobile !=\'\'">
            and "mobile" = #{mobile}
        </if>
        <if test="alliance_name != null and alliance_name !=\'\'">
            and "driver_management_id" = #{alliance_name}
        </if>
        <if test="register_city != null and register_city !=\'\'">
            and substr("register_city" , 0 , 4) = substr(#{register_city} , 0 , 4)
        </if>
        <if test="driver_type != null and driver_type !=\'\'">
            and "driver_type" = #{driver_type}
        </if>
        GROUP BY
        "driver_id" ,
        "driver_name" ,
        "mobile" ,
        "alliance_name" ,
        "register_city" ,
        "driver_type"

        union all

        SELECT
        "driver_id" ,
        "driver_name" ,
        "mobile" ,
        "alliance_name" ,
        "register_city" ,
        "driver_type" ,
        sum(0) AS "pushOrderNum" ,
        sum(0) AS "effectiveSlogan" ,
        sum(0) AS "grab_sus_order" ,
        sum(0) AS "cancel_order_num",
        sum(0) AS "cancel_passenger_num",
        sum(0) AS "cancel_driver_num",
        sum(0) AS "cancel_service_num" ,
        sum(0) AS "destination_reached_num" ,
        sum(to_number("pay_num")) AS "pay_num" ,
        sum(to_number("order_cumulative_num")) AS "order_cumulative_num",
        sum(to_number("order_pay_cumulative_num")) AS "order_pay_cumulative_num"
        FROM
        "_crontab_reservation"
        WHERE
        CASE WHEN "pay_time" != \'0\' THEN
        TO_DATE("pay_time",\'yyyy-MM-dd\') >= TO_DATE(#{start_time},\'yyyy-MM-dd\')
        AND
        TO_DATE("pay_time",\'yyyy-MM-dd\') &lt;= TO_DATE(#{end_time},\'yyyy-MM-dd\')
        ELSE 1=1 END
        <if test="driver_id != null and driver_id !=\'\'">
            and "driver_id"= #{driver_id}
        </if>
        <if test="driver_name != null and driver_name !=\'\'">
            and "driver_name" = #{driver_name}
        </if>
        <if test="mobile != null and mobile !=\'\'">
            and "mobile" = #{mobile}
        </if>
        <if test="alliance_name != null and alliance_name !=\'\'">
            and "driver_management_id" = #{alliance_name}
        </if>
        <if test="register_city != null and register_city !=\'\'">
            and substr("register_city" , 0 , 4) = substr(#{register_city} , 0 , 4)
        </if>
        <if test="driver_type != null and driver_type !=\'\'">
            and "driver_type" = #{driver_type}
        </if>
        GROUP BY
        "driver_id" ,
        "driver_name" ,
        "mobile" ,
        "alliance_name" ,
        "register_city" ,
        "driver_type"
        ) AS "TB1"
        GROUP BY
        "TB1".driver_id ,
        "TB1".driver_name ,
        "TB1".mobile ,
        "TB1".alliance_name ,
        "TB1".register_city ,
        "TB1".driver_type
    </select>
解决后的代码