mysql case when & concat & SUBSTRING_INDEX & not & having 使用的小case

標籤：jpg   color   存在   case   car   com   stat   blog   col   

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 是一步一步寫出來的

mysql case when & concat & SUBSTRING_INDEX & not & having 使用的小case