场景：

希望使用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() AS "effectiveSlogan" ,

        sum() AS "grab_sus_order" ,

        sum() AS "cancel_order_num",

        sum() AS "cancel_passenger_num",

        sum() AS "cancel_driver_num",

        sum() AS "cancel_service_num" ,

        sum() AS "destination_reached_num" ,

        sum() AS "pay_num" ,

        sum() AS "order_cumulative_num",

        sum() AS "order_pay_cumulative_num"

        FROM

        "_crontab_reservation"

        WHERE

        CASE WHEN "pushOrderTime" != '' 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 = 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" ,  , ) = substr(#{register_city} ,  , )

        </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() AS "pushOrderNum" ,

        sum(to_number("effectiveSlogan")) AS "effectiveSlogan" ,

        sum() AS "grab_sus_order" ,

        sum() AS "cancel_order_num",

        sum() AS "cancel_passenger_num",

        sum() AS "cancel_driver_num",

        sum() AS "cancel_service_num" ,

        sum() AS "destination_reached_num" ,

        sum() AS "pay_num" ,

        sum() AS "order_cumulative_num",

        sum() AS "order_pay_cumulative_num"

        FROM

        "_crontab_reservation"

        WHERE

        CASE WHEN "_effective_glab_time" != '' 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 = 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" ,  , ) = substr(#{register_city} ,  , )

        </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() AS "pushOrderNum" ,

        sum() AS "effectiveSlogan" ,

        sum(to_number("grab_sus_order")) AS "grab_sus_order" ,

        sum() AS "cancel_order_num",

        sum() AS "cancel_passenger_num",

        sum() AS "cancel_driver_num",

        sum() AS "cancel_service_num" ,

        sum() AS "destination_reached_num" ,

        sum() AS "pay_num" ,

        sum() AS "order_cumulative_num",

        sum() AS "order_pay_cumulative_num"

        FROM

        "_crontab_reservation"

        WHERE

        CASE WHEN "grab_sus_time" != '' 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 = 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" ,  , ) = substr(#{register_city} ,  , )

        </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() AS "pushOrderNum" ,

        sum() AS "effectiveSlogan" ,

        sum() 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() AS "destination_reached_num" ,

        sum() AS "pay_num" ,

        sum() AS "order_cumulative_num",

        sum() AS "order_pay_cumulative_num"

        FROM

        "_crontab_reservation"

        WHERE

        CASE WHEN "cancel_time" != '' 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 = 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" ,  , ) = substr(#{register_city} ,  , )

        </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() AS "pushOrderNum" ,

        sum() AS "effectiveSlogan" ,

        sum() AS "grab_sus_order" ,

        sum() AS "cancel_order_num",

        sum() AS "cancel_passenger_num",

        sum() AS "cancel_driver_num",

        sum() AS "cancel_service_num" ,

        sum(to_number("destination_reached_num")) AS "destination_reached_num" ,

        sum() AS "pay_num" ,

        sum() AS "order_cumulative_num",

        sum() AS "order_pay_cumulative_num"

        FROM

        "_crontab_reservation"

        WHERE

        CASE WHEN "close_gps_time" != '' 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 = 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" ,  , ) = substr(#{register_city} ,  , )

        </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() AS "pushOrderNum" ,

        sum() AS "effectiveSlogan" ,

        sum() AS "grab_sus_order" ,

        sum() AS "cancel_order_num",

        sum() AS "cancel_passenger_num",

        sum() AS "cancel_driver_num",

        sum() AS "cancel_service_num" ,

        sum() 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" != '' 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 = 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" ,  , ) = substr(#{register_city} ,  , )

        </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() AS "effectiveSlogan" ,

        sum() AS "grab_sus_order" ,

        sum() AS "cancel_order_num",

        sum() AS "cancel_passenger_num",

        sum() AS "cancel_driver_num",

        sum() AS "cancel_service_num" ,

        sum() AS "destination_reached_num" ,

        sum() AS "pay_num" ,

        sum() AS "order_cumulative_num",

        sum() AS "order_pay_cumulative_num"

        FROM

        "_crontab_reservation"

        WHERE

        CASE WHEN "pushOrderTime" != '' 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 = 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" ,  , ) = substr(#{register_city} ,  , )

        </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() AS "pushOrderNum" ,

        sum(to_number("effectiveSlogan")) AS "effectiveSlogan" ,

        sum() AS "grab_sus_order" ,

        sum() AS "cancel_order_num",

        sum() AS "cancel_passenger_num",

        sum() AS "cancel_driver_num",

        sum() AS "cancel_service_num" ,

        sum() AS "destination_reached_num" ,

        sum() AS "pay_num" ,

        sum() AS "order_cumulative_num",

        sum() AS "order_pay_cumulative_num"

        FROM

        "_crontab_reservation"

        WHERE

        CASE WHEN "_effective_glab_time" != '' 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 = 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" ,  , ) = substr(#{register_city} ,  , )

        </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() AS "pushOrderNum" ,

        sum() AS "effectiveSlogan" ,

        sum(to_number("grab_sus_order")) AS "grab_sus_order" ,

        sum() AS "cancel_order_num",

        sum() AS "cancel_passenger_num",

        sum() AS "cancel_driver_num",

        sum() AS "cancel_service_num" ,

        sum() AS "destination_reached_num" ,

        sum() AS "pay_num" ,

        sum() AS "order_cumulative_num",

        sum() AS "order_pay_cumulative_num"

        FROM

        "_crontab_reservation"

        WHERE

        CASE WHEN "grab_sus_time" != '' 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 = 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" ,  , ) = substr(#{register_city} ,  , )

        </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() AS "pushOrderNum" ,

        sum() AS "effectiveSlogan" ,

        sum() 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() AS "destination_reached_num" ,

        sum() AS "pay_num" ,

        sum() AS "order_cumulative_num",

        sum() AS "order_pay_cumulative_num"

        FROM

        "_crontab_reservation"

        WHERE

        CASE WHEN "cancel_time" != '' 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 = 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" ,  , ) = substr(#{register_city} ,  , )

        </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() AS "pushOrderNum" ,

        sum() AS "effectiveSlogan" ,

        sum() AS "grab_sus_order" ,

        sum() AS "cancel_order_num",

        sum() AS "cancel_passenger_num",

        sum() AS "cancel_driver_num",

        sum() AS "cancel_service_num" ,

        sum(to_number("destination_reached_num")) AS "destination_reached_num" ,

        sum() AS "pay_num" ,

        sum() AS "order_cumulative_num",

        sum() AS "order_pay_cumulative_num"

        FROM

        "_crontab_reservation"

        WHERE

        CASE WHEN "close_gps_time" != '' 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 = 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" ,  , ) = substr(#{register_city} ,  , )

        </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() AS "pushOrderNum" ,

        sum() AS "effectiveSlogan" ,

        sum() AS "grab_sus_order" ,

        sum() AS "cancel_order_num",

        sum() AS "cancel_passenger_num",

        sum() AS "cancel_driver_num",

        sum() AS "cancel_service_num" ,

        sum() 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" != '' 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 = 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" ,  , ) = substr(#{register_city} ,  , )

        </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>

解决后的代码

最新文章

  1. backup
  2. Asynchronous fs.stat.isDirectory()
  3. CI框架如何在主目录application目录之外使用uploadify上传插件和bootstrap前端框架：
  4. Tomcat启动报错 Could not reserve enough space for object heap
  5. webapi输入验证过滤器ValidationActionFilter
  6. iOS之01-基本语法
  7. HDU 3966 Aragorn&#39;s Story
  8. [css] line-height
  9. Android（java）学习笔记196：Android中Menu的使用（静态和动态）
  10. JPA entity versioning (@Version and Optimistic Locking)
  11. n进制转为十进制
  12. oracle10.2 dblink impd 同库不同用户复制数据
  13. 项目中处理android 6.0权限管理问题
  14. UISwitch 开关控件
  15. Example013操作样式
  16. Api管家系列（一）：初探
  17. Wireshark解析MQTT
  18. leetcode-956. 最高的广告牌
  19. vue+vue-resource+vue-cookie随笔
  20. Remove Duplicates from Sorted Array II leetcode java

热门文章

  1. Scala学习二十——Actor
  2. c# http文件上传
  3. OpenCV安装和测试
  4. windows 无法找到unistd.h 的解决方法
  5. NodeJS express框架的使用
  6. 使用python+selenium获得b站今日播放的动漫
  7. BigDecimal与Long、int之间的相互转换
  8. R 读取xls/xlsx文件
  9. C++手动调用析构函数无效问题排查
  10. Java中接口是否可以继承多个接口？