1. 代码

SELECT    a.id,    a.activity_name,    (        CASE        WHEN a.activity_end_time > now() THEN            '参与中'        ELSE            (                CASE                WHEN (                    a.activity_doubt <> '*'                    AND a.activity_doubt < c.doubt                )                OR (                    a.activity_praise <> '*'                    AND a.activity_praise > c.praise * 100                ) THEN                    '未达标'                ELSE                    '已达标'                END            )        END    ) AS state,    c.driver_id,    c.driver_phone,    c.driver_name,    c.countFROM    (        SELECT            id,            activity_name,            activity_end_time,            SUBSTRING_INDEX(                driver_award_condition,                "-",                1            ) AS activity_doubt,            SUBSTRING_INDEX(                driver_award_condition,                "-",                - 1            ) AS activity_praise        FROM            car_biz_numprize_base AS b        WHERE            1 = 1        AND activity_name LIKE concat(concat('%', '数据'), '%')        AND id = 1        AND NOT (            (                activity_start_time > '2017-10-27 17:16:00'            )            OR (                activity_end_time < '2017-10-27 17:10:00'            )        )    ) AS aINNER JOIN (    SELECT        i.driver_id,        i.driver_phone,        i.driver_name,        i.numprize_base_id,        count(order_no) AS count,        sum(order_doubt) AS doubt,        sum(order_praise) / count(order_no) AS praise    FROM        car_biz_numprize_order_item i    WHERE        1 = 1    AND driver_id = 1000063    GROUP BY        i.driver_id,        i.numprize_base_id) AS c ON a.id = c.numprize_base_idHAVING    state = '未达标' order by a.created_time desc, c.driver_id ascLIMIT 0, 10

2. 需求的来源

 2.1 符合活动条件的订单 达标情况查询 如下页面所示 2-1

2.2  活动相关的表

活动表2-2

符合条件订单表2-3

2.1图中 活动参与状态的查询条件 是在这两个表中不存在的字段

   需从活动表2-2中 获取 driver_award_condition列 并分割条件 然后对 2-1表进行运算 然后再帅选

 2.4 如果在mybatis中 拼sql 小于 大于号 放在 <![ CDATA [>]]>中

 SELECT SUBSTRING_INDEX(REVERSE(SUBSTRING_INDEX("aa,bb,cc,dd", ",", 1)), ",", 1);    #aa  

SELECT SUBSTRING_INDEX(REVERSE(SUBSTRING_INDEX("aa,bb,cc,dd", ",", 2)), ",", 1);    #bb        

SELECT SUBSTRING_INDEX(REVERSE(SUBSTRING_INDEX("aa,bb,cc,dd", ",", 3)), ",", 1);    #cc

SELECT SUBSTRING_INDEX(REVERSE(SUBSTRING_INDEX("aa,bb,cc,dd", ",", 4)), ",", 1);    #dd

3.总结

复杂的sql 是一步一步写出来的